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:

1. Backup a database
2. Copy the database to our testing SQL Server
3. Mount the database
4. Run a Database Consistency Check (DBCC from here on)
5. Detach the database
6. Keep the backup copy until next time
7. 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'

# Add Required Powershell Snapins to work with SQL
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

# Switch to root directory
cd c:
cd \

# Declare Constant Variables
$date = (get-date -Format M/d/yyyy)
$newline = "`r`n"
$dividerline = "==============================================================================="

# Variables dealing with the testing database server
$TestingDBServer = "SQLTEST"
$TestingBackupFilePath = "D:\Databases\Backups"
$TestingBackupFileUNC = "d$\Databases\Backups"
$TestingServerSQLDataFolder = "D:\Databases\Backups"
$TestingServerSQLDataFolderUNC = "d$\Databases\Backups"
$TimestampFolder = (get-date -uformat "%H%M-%m%d%Y")
$ArchiveStoragePath = "\\$TestingDBServer\$TestingBackupFileUNC\$Timestampfolder"
new-item -Path $ArchiveStoragePath -ItemType directory | out-null
$Pattern = "..errors and repaired ........"
$InsertString = "Found"




# ADDING A NEW DATABASE - START COPY ON THIS LINE

#***************************************
#*   *
#* DATABASE 1   *
#*   *
#***************************************

#***********************
#*  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

# Automatically Built Variables
$SourceBackupFullPathAndName = ($SourceBackupFilePath + "\" + $BackupFileName)
$TestingBackupFullPathAndName = ($TestingBackupFilePath + "\" + $BackupFileName)

# Backup Database
Invoke-SqlCmd -Query "BACKUP DATABASE [$DatabaseName] TO  DISK = N'$SourceBackupFullPathAndName' WITH NOFORMAT, INIT,  `
NAME = N'$BackupName', SKIP, NOREWIND, NOUNLOAD,  STATS = 10" -ServerInstance $SourceDBServer -QueryTimeout 65535

# MOVE (not copy!) the backup file to the Testing Server
move-item "\\SQLPROD\$SourceBackupFileUNC\$BackupFileName" -Destination "\\$TestingDBServer\$TestingBackupFileUNC"

# Restore Database onto the Testing Server
Invoke-SqlCmd -Query "RESTORE DATABASE [$DatabaseName] FROM  DISK = N'$TestingBackupFullPathAndName' WITH  FILE = 1,  NOUNLOAD,  STATS = 10" `
-ServerInstance "$TestingDBServer" -QueryTimeout 65535

# DBCC Check
Invoke-SqlCmd -Query "DBCC CHECKDB ($DatabaseName) WITH ALL_ERRORMSGS" -ServerInstance "$TestingDBServer" -QueryTimeout 65535

# Get DBCC Results from SQL Log and format them
[system.reflection.assembly]::LoadWithPartialName('Microsoft.SQLServer.Smo') | out-null
$srv1 = new-object ('Microsoft.SQLServer.Management.Smo.Server') $TestingDBServer
$Result = ($srv1.readerrorlog(0) | where {$_.text -like "*DBCC*"} | select text -last 1)
$Result -match $Pattern | out-null
$DBCCOutputText = $matches[0]
$DBCCOutputText = ($InsertString,$DBCCOutputText -join " ")



# Delete Database
$DeleteDatabaseQuery = "EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'$DatabaseName'
GO
USE [master]
GO
DROP DATABASE [$DatabaseName]
GO"
Invoke-SqlCmd -Query $DeleteDatabaseQuery -ServerInstance "$TestingDBServer" -QueryTimeout 65535

# 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


# This section creates the report for this database
$EndTimeDatabase1 = (get-date)
$ReportForDatabase1 = (
"Date: "+$Date+$newline+`
"Database Name: "+$DatabaseName+$newline+`
"DBCC Output: "+$newline+$DBCCOutputText+$newline+`
"Start Time: "+$StartTimeDatabase1+$newline+`
"End Time: "+$EndTimeDatabase1+$newline+`
$dividerline
)

#***************************************
#*   *
#* END DATABASE 1   *
#*   *
#***************************************

# ADDING A NEW DATABASE - START COPY ON THIS LINE

# ADDING A NEW DATABASE - END COPY HERE

#********************************************************************
#* *
#* 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

#This section counts instances of the $SearchFor text, and if it equals 1 (the number of databases checked) then the body of the email will begin with "DBCC Check Successful". This allows for an Outlook filtering rule.
$Content = $MasterReport
$SearchFor = 'Found 0 errors and repaired 0 errors'
$MatchedInstances = ($Content | Select-String $SearchFor -AllMatches | Select -Expand Matches | Select -Expand Value)
If ($MatchedInstances.count -eq 1){
$MasterReport = ("DBCC Check Successful"+$MasterReport)
} #End If


# For each folder in the Archive area on the testing server, get the creationtime and delete anything older than 24 hours
$Folders = (Get-childitem "\\$TestingDBServer\$TestingBackupFileUNC")
Foreach ($folder in $folders){
$FolderAge = (((Get-Date) - $folder.creationtime).totalhours)
If ($FolderAge -gt 24)
{remove-item $Folder.FullName -recurse}
}

# Section for emailing of the report
Send-Mailmessage -from administrator@contoso.com -to me@contoso.com,OtherAdmin@contoso.com -subject "DB1 Database Consistency Check for $date" -smtpserver SMTPServer.contoso.com -body $MasterReport -bodyasHTML