Technical Article

Automatically Restoring from Production


Came across a great script from Ken Kaufman on copying backups using xp_cmdshell.  Thought I'd contribute a script I wrote that builds on that to restore the latest production backup to a testing or development server. 

In this case I routinely create all my production backups to a network share and then use that source to restore the database in on my beta server.  this way I ensure all the data as well as the schema are exact mirrors.  Last step I do is to update permissions between the two copies.

If anyone has any comments or suggestions please let me know.  Scripting it this way proved to be a blessing since it was just about impossible to keep the changes to schema objects in sync on my development server, and manually determining which backup to restore was always a manual process.

-- Ryan Brochez
-- Dec-16-2002

-- step 1: dynamically determine the latest 
-- backup that was taken

-- generate a directory listing into a text file
-- sorts the folder listing by descending date order
EXEC master..xp_cmdshell 'dir "\\remoteServerName\data\backups\databaseName\*.bak" /b /O-D > d:\temp\dir.txt', no_output
-- create a temporary table to store the directory listing
CREATE TABLE #tmpDirList (strFileName varchar(100))

-- upload the directory listing to the temporary table
BULK INSERT #tmpDirList FROM 'd:\temp\dir.txt' WITH (DATAFILETYPE = 'char', ROWTERMINATOR = '\n')

-- create a variable to store the latest backup filename to
DECLARE @strFileSource varchar(100)
SET @strFileSource = '\\remoteServerName\data\backups\databaseName\' + (SELECT TOP 1 strFilename FROM #tmpDirList)

-- get rid to the temporary table and the file created
DROP TABLE #tmpDirList
EXEC master..xp_cmdshell 'del d:\temp\dir.txt', no_output

-- first time through, run this to check the filenames
-- set up for the database. 


-- having identified the lates copy to restore, 
-- now go ahead and restore it
-- I specify MOVE because for me the file locations differ
FROM DISK = @strFileSource
MOVE 'DatabaseName_Data' TO 'D:\Data\DatabaseName\DatabaseName_Data.mdf',
MOVE 'DatabaseName_Log' TO 'D:\Data\DatabaseName\DatabaseName_Log.ldf'

-- last step, make sure you update permissions
-- however required (if at all)

use DatabaseName

-- resync local sql accounts
exec sp_change_users_login Auto_Fix, 'localSQLAcct'

-- add developers to the user base
exec sp_grantdbaccess 'Domain\DBDevelopers', 'DBDevelopers''

-- give developers whatever access level needed
exec sp_addrolemember 'db_owner', 'DBDevelopers''