September 18, 2013 at 1:46 pm
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
September 18, 2013 at 2:01 pm
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.
September 19, 2013 at 9:38 am
George....just what I was looking for! My terminology was off...no wonder I couldn't find anything
Thanks!
September 19, 2013 at 11:12 am
Glad it meets your needs. We've been running something similar here for 4 or 5 years and it's worked great for us!
September 19, 2013 at 11:36 am
joshd 1807 (9/19/2013)
George....just what I was looking for! My terminology was off...no wonder I couldn't find anythingThanks!
That script will not work if you have multiple files for a database.
October 22, 2013 at 3:48 pm
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?
October 22, 2013 at 4:55 pm
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
October 23, 2013 at 6:58 am
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)
October 23, 2013 at 9:46 am
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
October 23, 2013 at 10:03 am
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.
October 23, 2013 at 10:18 am
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
October 23, 2013 at 10:30 am
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
October 23, 2013 at 10:33 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply