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.
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.