I was looking for a simple way to schedule a job to perform a nightly backup of the production system and restore it to a dev system. I know there are many ways of achieving my goal and I am sure my solution will be frowned upon by some (if not all J) but it works for me so I thought I would share my experience with others.
For my scenario the requirement was quite straightforward. Backup a single 5GB database then restore to an existing dev system (over writing the existing dev system). This article relates to SQL Server 2005 Standard 64bit (version 9.00.3353) running on Windows Server 2003 R2 Standard x64 (SP2).
I started as I do with most things by over complicating the solution! Initially I planned on creating a job with 3 steps. The first step executed a .vb script. The script traverses the directory provided for the ‘strSrcPath’ parameter, deleting all files that are older than the value provided for the ’intMaxDate’ parameter.
My first a job included 3 steps and is scheduled to run nightly:
- Delete previous backups
- Backup Production System
- Restore to Development System
Step 1 (Cleanup .vb script)
Here was my first attempt:
Option Explicit Const strSrcPath = "C:\Restore" ' subdirectory to clean Const intMaxDate = 0 ' change days to keep here Dim objFSO Set objFSO = CreateObject("Scripting.FileSystemObject") TraverseFolder strSrcPath, intMaxDate Sub TraverseFolder(strSrcPath, intMaxDate) Dim objCurrentFolder Set objCurrentFolder = objFSO.GetFolder(strSrcPath) On Error Resume Next Dim objFile, objFolder For Each objFile In objCurrentFolder.Files If DateDiff("d", CDate(objFile.DateLastModified), Now) > intMaxDate Then objFSO.DeleteFile objFile End If Next For Each objFolder In objCurrentFolder.subFolders TraverseFolder objFolder, intMaxDate Next End Sub
When I ran the job SQL returned error 14277.
The command script does not destroy all the objects that it creates. Revise the command script. (Microsoft SQL Server, Error: 14277)
The error was generated because the objects that were created by the script were not removed after execution. To resolve I simply appended the following 2 lines to the code:
Set objFSO=Nothing Set objCurrentFolder=Nothing
The working .vb script is as follows:
Option Explicit Const strSrcPath = "C:\Restore" ' subdirectory to clean Const intMaxDate = 0 ' change days to keep here Dim objFSO Set objFSO = CreateObject("Scripting.FileSystemObject") TraverseFolder strSrcPath, intMaxDate Sub TraverseFolder(strSrcPath, intMaxDate) Dim objCurrentFolder Set objCurrentFolder = objFSO.GetFolder(strSrcPath) On Error Resume Next Dim objFile, objFolder For Each objFile In objCurrentFolder.Files If DateDiff("d", CDate(objFile.DateLastModified), Now) > intMaxDate Then objFSO.DeleteFile objFile End If Next For Each objFolder In objCurrentFolder.subFolders TraverseFolder objFolder, intMaxDate Next Set objFSO=Nothing Set objCurrentFolder=Nothing End Sub
Step 2 (Backup Production System)
The backup is invoked by a job which is scheduled to run daily at 0500. The backup is performed on the same server that hosts the SQL Database.
BACKUP DATABASE MyDatabase TO DISK = 'C:\Restore\Dev.bak'
Step 3 (Restore to Development System)
The restore operation again is on the same server that hosts the database and overwrites the existing dev system.
RESTORE DATABASE MyDatabase FROM DISK = 'C:\Restore\Dev.bak' WITH REPLACE
This got me thinking. I only had the delete .vb script as my backup was appending to the exiting file.
I made a simple change to the backup script, simply adding the WITH INIT option.
BACKUP DATABASE MyDatabase TO DISK = 'C:\Restore\Dev.bak' WITH INIT
Here is an explanation of the INIT option taken from Microsoft TechNet:
If the volume contains a valid media header, performs the following checks:
Verifies the media password.2
If MEDIANAME was specified, verifies that the given media name matches the media header's media name.
Verifies that there are no unexpired backup sets already on the media.
If there are, terminates the backup.
If these checks pass, overwrites any backup sets on the media, preserving only the media header.
If the volume does not contain a valid media header, generates one with using specified MEDIANAME, MEDIAPASSWORD, and MEDIADESCRIPTION, if any.
I no longer needed step 1 as the INIT option overwrites the existing backup set.
OK, I now have a very straight forward backup and restore job. This got me thinking again. What if somebody is connected to the Dev system when I am trying to restore it?
To identify a script that would disconnect any active processes against the dev system I searched online using Google and found exactly what I was looking for (creator anonymous on blog response so no credit can be given). Blog url: http://blog.tech-cats.com/2008/01/kill-all-database-connections-to-sql.html
Having originally started with a job that contained 3 steps and then removing the Cleanup .vb script to have only 2 steps (backup and restore) I reverted back to 3 steps with the addition t-sql script ‘kill all database connections’.
1. Backup Production System
2. Kill Connections to Dev System
3. Restore to Development System
The kill script is as follows:
-- Create the sql to kill the active database connections declare @execSql varchar(1000), @databaseName varchar(100) -- Set the database name for which to kill the connections set @databaseName = 'MyDatabase' set @execSql = '' select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ' from master.dbo.sysprocesses where db_name(dbid) = @databaseName and DBID <> 0 and spid <> @@spid exec (@execSql) GO
The job ran fine for a few days and then failed at step 2. Reviewing the job history returned Sql Message ID 6107
Executed as user: Domain\User. Only user processes can be killed. [SQLSTATE 42000] (Error 6107). The step failed.
After a little research I discovered that you cannot kill system processes. Any SPID less than 50 is generally a system process. To resolve I modified the ‘Kill’ script to include additional filters.
AND status <> 'background' AND status IN ('runnable','sleeping')
The ‘Kill’ script now looks as follows:
-- Create the sql to kill the active database connections DECLARE @execSql varchar(1000) DECLARE @databaseName varchar(100) -- Set the database name for which to kill the connections SET @databaseName = 'DevSystem' SET @execSql = ''SELECT @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' ' from master.dbo.sysprocesses WHERE db_name(dbid) = @databaseName AND status <> 'background' AND status IN ('runnable','sleeping') AND DBID <> 0 AND spid <> @@spid EXEC (@execSql)
To conclude I have a scheduled job that performs 3 tasks that allow me to automate back-up and restore of my production system to my dev system.
- Back-up the production system
- Kills connections prior to restore
- Restores the backup to the dev system
So that concludes my Backup and Restore task. This article was driven by a recent vacation. Prior to my vacation I was asked to restore the production system to the dev system at month end. However I was going to be thousands of miles away when they wanted it done. As a novice DBA I am constantly looking for ways to improve how I manage my growing SQL environment and accommodate end user requests. I hope other novice DBA’s like I will find this article of use.