Best practice to setup a test server to restore backups from production automatically?

  • I was finally given leeway in my budget to get a proper test environment and have two instances of Server 2008 R2 and SQL Server 2008 instances. Now I want to have data automatically restore to the test environment every night. What would be the best practice to do this?

    I've searched high and low and can't find a concrete way to do this, since everyone has their own way of doing it.

    We have backups that run every night and the file names reflect the date it was backed up (database_13_8_23.bak, database_13_8_24.back, database_13_8_25.back...) Of all of the examples to do an automatic restore, all I see is a restore if the backup name doesn't change (ex: RESTORE DATABASE [Test] FROM DISK = N'D:\database.bak) and not if it has a date range.

    What I'd like to do is restore the most current databases each night to the test environment. If this is a trivial task that has been repeated, accept my apologies. Couldn't find anything that related to my situation

  • Please check out the following, it should come close to meeting your needs: http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

    You may need to modify it to accomodate your file naming standards but it should get the job done.

  • George....just what I was looking for! My terminology was off...no wonder I couldn't find anything

    Thanks!

  • Glad it meets your needs. We've been running something similar here for 4 or 5 years and it's worked great for us!

  • joshd 1807 (9/19/2013)


    George....just what I was looking for! My terminology was off...no wonder I couldn't find anything

    Thanks!

    That script will not work if you have multiple files for a database.

  • Just a follow up, this is working great!

    I used the script provided in the solution that George provided and modified so it would actually restore the database instead of giving me an output.

    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'

    EXEC (@cmd)

    Now...instead of restoring from a local drive, I want to set up restores from a unc path (IE: our production backup location)

    I have this setup as a hidden share (\\hostname\d$) and the database backup folders are located in that area. But for some reason SQL Server cannot 'find' any backups even though they are there.

    I've mapped a drive on our test environment to make it appear as a drive letter but still cannot get SQL Server to find the backups. I'm logged into SQL Server as a domain administrator.

    Any ideas?

  • Does the account that SQL Server runs under have permissions to that share?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 100% full access. I can access it from the server with the Windows Authentication account I am using

    EDIT: I forgot to show the error message that I get when using the \\hostname\d$ share

    The database is already fully recovered.

    As soon as I switch it to local storage, it runs without any issues

    Here's the full script

    USE Master;

    GO

    SET NOCOUNT ON

    -- 1 - Variable declaration

    DECLARE @dbName sysname

    DECLARE @backupPath NVARCHAR(500)

    DECLARE @cmd NVARCHAR(500)

    DECLARE @fileList TABLE (backupFile NVARCHAR(255))

    DECLARE @lastFullBackup NVARCHAR(500)

    DECLARE @lastDiffBackup NVARCHAR(500)

    DECLARE @backupFile NVARCHAR(500)

    -- 2 - Initialize variables

    SET @dbName = 'database'

    SET @backupPath = '\\hostname\d$\SQLBACKUP\database\'

    -- 3 - get list of files

    SET @cmd = 'DIR /b ' + @backupPath

    INSERT INTO @fileList(backupFile)

    EXEC master.sys.xp_cmdshell @cmd

    -- 4 - Find latest full backup

    SELECT @lastFullBackup = MAX(backupFile)

    FROM @fileList

    WHERE backupFile LIKE '%.BAK'

    AND backupFile LIKE @dbName + '%'

    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''

    + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'

    EXEC (@cmd)

    -- 4 - Find latest diff backup

    SELECT @lastDiffBackup = MAX(backupFile)

    FROM @fileList

    WHERE backupFile LIKE '%.DIF'

    AND backupFile LIKE @dbName + '%'

    AND backupFile > @lastFullBackup

    -- check to make sure there is a diff backup

    IF @lastDiffBackup IS NOT NULL

    BEGIN

    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''

    + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'

    EXEC (@cmd)

    SET @lastFullBackup = @lastDiffBackup

    END

    -- 5 - check for log backups

    DECLARE backupFiles CURSOR FOR

    SELECT backupFile

    FROM @fileList

    WHERE backupFile LIKE '%.TRN'

    AND backupFile LIKE @dbName + '%'

    AND backupFile > @lastFullBackup

    OPEN backupFiles

    -- Loop through all the files for the database

    FETCH NEXT FROM backupFiles INTO @backupFile

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''

    + @backupPath + @backupFile + ''' WITH NORECOVERY'

    EXEC (@cmd)

    FETCH NEXT FROM backupFiles INTO @backupFile

    END

    CLOSE backupFiles

    DEALLOCATE backupFiles

    -- 6 - put database in a useable state

    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'

    EXEC (@cmd)

  • I couldnt help but notice you are using an administrative share:

    \\hostname\d$\SQLBACKUP\database

    Accessing D$ on the host can only be done by accounts that are a member of the local administrators group on the host server.

    It is better suited to access the other host via a regular share such as

    \\hostname\SQLBAckup\database

    MCITP SQL 2005, MCSA SQL 2012

  • Thanks for the suggestion, but even after adding a share as \\hostname\SQLBACKUP\database, I'm still getting this error:

    Msg 3153, Level 16, State 2, Line 1

    The database is already fully recovered.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    As soon as I change the script to point to C:\SQL_Backup\Database\ it immediately restores.

    The databases are fully functional and aren't restoring any logs, so the error message is confusing.

  • I suspect the problems trying to use the dir comman on a unc path. I can't test this as I'm not on a computer but I am sure that doesn't work with a unc path as its invalid at the command line.

    Try running the command from a prompt to the unc path and see if it returns a list of files.

    If it doesn't, you will need to create a temporary mapped drive using the NET USE command as part of your command shell command.

    MCITP SQL 2005, MCSA SQL 2012

  • joshd 1807 (10/23/2013)


    100% full access. I can access it from the server with the Windows Authentication account I am using

    I didn't ask about the account you're using. I asked if the SQL Server service account (the account that SQL Server is running under) has access. The account you're using is mostly irrelevant.

    It's perfectly possible to restore from a UNC path, SQL just has to have permissions to access that path.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • joshd 1807 (10/23/2013)


    The databases are fully functional and aren't restoring any logs, so the error message is confusing.

    That message is from the RESTORE DATABASE ... WITH RECOVERY line. Since the restore that would have left the DB RESTORING failed, the attempt to bring it online would fail because the DB's in the wrong state.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply