Automatically Restoring from Production

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

--RESTORE FILELISTONLY FROM DISK = @strFileSource 


--------------------
-- having identified the lates copy to restore, 
-- now go ahead and restore it
-- I specify MOVE because for me the file locations differ
RESTORE DATABASE DatabaseName
FROM DISK = @strFileSource
WITH RECOVERY,
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''