SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
joshd 1807
joshd 1807
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 378
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
George M Parker
George M Parker
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 1472
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.
joshd 1807
joshd 1807
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 378
George....just what I was looking for! My terminology was off...no wonder I couldn't find anything

Thanks!
George M Parker
George M Parker
SSC Eights!
SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)SSC Eights! (840 reputation)

Group: General Forum Members
Points: 840 Visits: 1472
Glad it meets your needs. We've been running something similar here for 4 or 5 years and it's worked great for us!
muth_51
muth_51
SSC Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 Visits: 2905
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.
joshd 1807
joshd 1807
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 378
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86877 Visits: 45263
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


joshd 1807
joshd 1807
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 378
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)


RTaylor2208
RTaylor2208
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 Visits: 1191
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
joshd 1807
joshd 1807
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 378
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search