Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Best practice to setup a test server to restore backups from production automatically? Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 1:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:34 AM
Points: 59, Visits: 298
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
Post #1496109
Posted Wednesday, September 18, 2013 2:01 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 653, Visits: 1,428
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.
Post #1496119
Posted Thursday, September 19, 2013 9:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:34 AM
Points: 59, Visits: 298
George....just what I was looking for! My terminology was off...no wonder I couldn't find anything

Thanks!
Post #1496466
Posted Thursday, September 19, 2013 11:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 653, Visits: 1,428
Glad it meets your needs. We've been running something similar here for 4 or 5 years and it's worked great for us!
Post #1496500
Posted Thursday, September 19, 2013 11:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 351, Visits: 1,656
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.
Post #1496518
Posted Tuesday, October 22, 2013 3:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:34 AM
Points: 59, Visits: 298
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?

Post #1507401
Posted Tuesday, October 22, 2013 4:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Does the account that SQL Server runs under have permissions to that share?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1507421
Posted Wednesday, October 23, 2013 6:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:34 AM
Points: 59, Visits: 298
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)

Post #1507597
Posted Wednesday, October 23, 2013 9:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:24 AM
Points: 286, Visits: 539
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
Post #1507711
Posted Wednesday, October 23, 2013 10:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:34 AM
Points: 59, Visits: 298
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.
Post #1507720
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse