Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Monday, July 1, 2013

My Best Script So Far - SQL Backup, Remote Mount, and DBCC

NOTE: After some actual time with SQL, I understand that this is not the most efficient way to do this - you can accomplish most of what I've scripted with Maintenance plans. HOWEVER, I am posting this script anyway, because I'm proud of it, it works, and people might get helpful examples of certain routines out of it.

I've alluded to this script in a couple of posts. It's the script I'm most proud of. It was very difficult to do with my somewhat limited knowledge of SQL and Powershell at the time, it taught me a lot about both, and it runs multiple times per day on production systems with no issues.

The issue this script solves is as follows: I am an "Accidental DBA". Meaning, I'm the admin who knows the most about SQL (which isn't much), and we don't have the budget for an actual DBA, so I'm it. I backup my SQL servers every night using Veeam Enterprise AND Backup Exec 2010. Veeam is great and all, but Backup Exec can restore my database right back into my SQL server with little fuss; the process is much easier. The databases I inherited are using simple recovery mode, so I have no point-in-time recovery capabilities. I could switch them to use a Full Recovery model, but I don't know what all the ramifications of that change may be on the apps themselves (supported?), disk space, backup configuration changes, and then backup disk space. It occured to me that if we lost a database at 3pm, I would have to restore from the previous evening, negating all work done throughout the day. No good. I needed a mid-day backup to mitigate this loss.

Like I said, this is probably not the best way to do this, but at the time (with no SQL training, or time to partake in it) it was the best I could come up with.

An outline of the process:

Backup a database

Copy the database to our testing SQL Server

Mount the database

Run a Database Consistency Check (DBCC from here on)

Detach the database

Keep the backup copy until next time

Email me the DBCC report

A couple of notes.

Powershell SQL stuff (and this script) doesn't work on SQL 2005.

You need to have the SQL 2008 R2 Studio Management Console (2008 might work, didn't try) installed on the system you run this script from.

The players:

SQLTEST - This is the testing SQL Server

SQLPROD - This is the production SQL Server

The script, in its entirety, will be posted without edits (save anonymizing my server names) between the two horizontal lines below. I've documented the script very well, so just read the comments withing the script for explanations.

Ugh, I'm not going to bother with the formatting either. This script is a beast, and apparently Blogger doesn't like pasting from Notepad++. Here's a link to the script, so you can download it from my Google Drive (no login required).

Here it is:

# HOW TO ADD ANOTHER DATABASE
# 1. Select all code between the two lines that start with # ADDING A NEW DATABASE, and copy it to your clipboard
# 2. Paste the code you copied at the end of the file, between the last # END DATABASE X and # ADD ANY NEW DATABASES ABOVE THIS LINE - THIS SECTION IS THE END
# 3. Renumber the Database Heading (within the * box), also edit the following variables (search for them within your section) to reflect the database number:
#$StartTimeDatabase# (Two Instances)
#$EndTimeDatabase# (Two Instances)
#$ReportForDatabase#
#$srv# (Two Instances)
# 4. Log in to the production database server that houses the database we want to schedule backups and consistency checks for
# 5. Ensure that the location C:\Backups exists (If you use a different drive letter, please edit the appropriate variables)
# 6. Open SQL Server Management Studio, logging in as someone with administrtive rights to all databases
# 7. Right Click the chosen database, choose "Tasks", then "Backup"
# 8. Modify Backup Set section "Name" to match the Database Name
# 9. Add the Destination to c:\backups\<DatabaseName.bak>, then remove the other location
# 10. Click Drop-down arrow next to "Script" at the top and choose "Script Action to new Query Window"
# 11. Close the backup windows by pressing the 'X' in the upper right hand corner (we will not actually run the backup job)
# 13. Referring to the key below on REFERENCE LINE:,
# analyze the SQL Query syntax of the backup job to assign the appropriate values to EDITING SECTION within your database's code section
# that you pasted in earlier at the end of the script. You need four values: $DatabaseName, $SourceBackupFilePath, $BackupFileName, $BackupName
# REFERENCE LINE: BACKUP DATABASE [DB1] TO DISK = N'C:\Backups\DB1.bak' WITH NOFORMAT, NOINIT, NAME = N'DB1', SKIP, NOREWIND, NOUNLOAD, STATS = 10
# [$DatabaseName] '$SourceBackupFilePath\$BackupFileName' N'$BackupName'

#***********************
#* EDITING SECTION *
#***********************
# Declare DB Specific Variables
$StartTimeDatabase1 = (get-date) #Change the number to correspond to the Database # in the * Box above
$SourceDBServer = "SQLPROD\MSSQLSERVER" #The Hostname\Instance of the production database server
$DatabaseName = "DB1" #Refer to the key
$SourceBackupFilePath = "C:\Backups" #Refer to the key
$SourceBackupFileUNC = "c$\Backups" #This is the same as $SourceBackupFilePath, but replace the colon with a dollar sign
$BackupFileName = "DB1.bak" #Refer to the key
$BackupName = "DB1" #This is the same as your database name, but refer to the key

# Move the BAK files that you used for the restores to a timestamped folder within the backup area
# These folders will be deleted after their creation times are over 24 hours
move-item "\\$TestingDBServer\$TestingBackupFileUNC\*.bak" -destination $ArchiveStoragePath

#********************************************************************
#**
#* ADD ANY NEW DATABASES ABOVE THIS LINE - THIS SECTION IS THE END*
#**
#********************************************************************

# CHANGES NEED TO BE MADE TO THIS REPORTING SECTION FOR SUBSEQUENT DATABASES
# Build the master report
$MasterReport = ("Beginning"+$newline+$dividerline+$newline+$ReportForDatabase1+$newline) #When adding a new database, append +$ReportForDatabase#+$newline just inside the end parenthesis