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:

1. Delete previous backups
2. Backup Production System
3. 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.

Error

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

Message

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.