January 19, 2011 at 2:50 pm
Hi, I would like to restore all my backups to a test environment. I have a script that populates a variable with the path to a BAK file that has not yet been restored.
I want to make a SQL statement that restores the database specified in the variable like this:
RESTORE DATABASE [' + @DBName + _test'] FROM DISK = @BackupLocation
@DBName stores the name of the database and @BackupLocation is the path to where the backup is stored. Unfortunately the script is failing because the directory structure on the test server is different than to other servers where the backups took place. It wants me to use WITH MOVE to specify the new locations for the files but using a variable and automated script, this does not seem possible. Can I just specify somewhere in the script to use the default location for databases on the test server or strip the restore location out of the FILELIST in the BAK file?
Has anyone done something like this successfully?
Thanks for reading.
Howard
January 20, 2011 at 4:43 am
you could mirror your server builds so disk allocations are always the same, this is the practice most would follow across a SQL Server farm. Other than that, use an SSIS package.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 20, 2011 at 2:17 pm
Perry Whittle (1/20/2011)
you could mirror your server builds so disk allocations are always the same, this is the practice most would follow across a SQL Server farm. Other than that, use an SSIS package.
SSIS Package is a good route to go, I did the automatic process using scripts.. but it's alot easier to manage within SSIS.
January 20, 2011 at 2:39 pm
Thanks for responding. I can't mirror the setup because there are multiple servers that these backups are coming from, built by different people, at different times. The test server is part of a cluster so some of the drive letters are reserved by other cluster nodes.
For SSIS, I did not see a restore task to try to use.
In the meantime, after looking online at several scripts, I put them together and came up with this monster. It was built for a demo so the tables are called DemoBackupHistory and DemoRestoreLog.
DemoBackupHistory was built using a PowerShell script that queried several system tables on the servers registered in my Central Management Server to get the server name, database name, backup location, and the location of the physical files. It reads the DemoBackupHistory table and finds the newest backup for a database that has not yet been restored and then restores it to my test system. It then logs the restore in the DemoRestoreLog table. The next time it is run, it will move on to the next database in the list. I have to build in a way to capture errors to the DemoRestoreLog table.
The only problem is that if the database has more than one datafile or more than one log file then the script fails.
declare @DatabaseName varchar(100)
declare @BackupFileName varchar(100)
declare @DatabaseDataFilename varchar(100)
declare @DatabaseLogFilename varchar(100)
declare @DataFileLocation varchar(100)
declare @LogFileLocation varchar(100)
declare @ExecSql nvarchar(1000)
set @DatabaseName = (select top 1 BH.database_name
from DemoBackupHistory BH
LEFT OUTER JOIN DemoRestoreLog RT
on BH.server_name = RT.server_name and BH.database_name = RT.database_name
where RT.server_name is null
order by backup_start_date desc)
set @BackupFileName = (select top 1 BH.physical_device_name
from DemoBackupHistory BH
LEFT OUTER JOIN DemoRestoreLog RT
on BH.server_name = RT.server_name and BH.database_name = RT.database_name
where RT.server_name is null
order by backup_start_date desc)
select @DataFileLocation = rtrim([file_Name]),
@databaseDataFilename = 'E:\DBA\DemoRestore\RestoredDBs\' + [file_name]
from DemoBackupHistory
where @DatabaseName = database_name and FileType = 'Data'
select @LogFileLocation = rtrim([file_Name]),
@databaseLogFilename = 'E:\DBA\DemoRestore\RestoredDBs\' + [file_name]
from DemoBackupHistory
where @DatabaseName = database_name and FileType = 'Log'
set @ExecSql = '
restore database [test_' + @databaseName + ']
from disk = ''' + @backupFileName + ''' with file = 1, replace,
move ''' + @DataFileLocation + ''' to ' + '''' + @databaseDataFileName + '_test'',
move ''' + @LogFileLocation + ''' to ' + '''' + @databaseLogFileName + '_test'''
print 'Restoring ' + @DatabaseName
EXEC sp_executesql @execSql
insert into DemoRestoreLog
select top 1 BH.server_name, BH.instance_name, BH.database_name, GETDATE(),''
from DemoBackupHistory BH
LEFT OUTER JOIN DemoRestoreLog RT
on BH.server_name = RT.server_name and BH.database_name = RT.database_name
where RT.server_name is null
order by backup_start_date desc
print 'Checking '+ @DatabaseName
EXEC('DBCC CHECKDB ([test_' + @DatabaseName + '])')
PRINT 'Dropping ' + @DatabaseName
EXEC('DROP DATABASE [test_' + @DatabaseName + ']')
January 20, 2011 at 2:45 pm
Just use [msdb].[dbo].[backupfile] as a source to grab all new files, my system adds 36 files each month.
January 20, 2011 at 3:04 pm
Once you have the backup file you want to restore perform a filelistonly restore to get the filenames and logical names. Use these to restore the database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 20, 2011 at 5:18 pm
this is what I use to copy the backup file. in the job step 2, i restore the database.
declare @pathname nvarchar(100)
declare @fname nvarchar(50)
declare @bakfile nvarchar(200)
declare @cmd nvarchar(1000)
declare @destpath nvarchar(100)
set @pathname = '{drive:\path\filename}'
set @destpath = '{drive:\path}'
create table #tmpbaklist
(
fname nvarchar(100)
)
insert into #tmpbaklist
exec xp_cmdshell 'dir /b /O:-D {\\servername\path\filename*.bak'
select top 1 @bakfile = @pathname + fname from #tmpdir
select @cmd = 'copy ' + @bakfile + ' ' + @destpath + 'filename.bak'
exec xp_cmdshell @cmd
drop table #tmpdir
January 20, 2011 at 8:00 pm
The script on this link shows how to generate a restore command using output of the restore HeaderOnly and restore FilelistOnly commands. Using this as a base, you can modify the command to restore the database files to a new location. Note that you would have to modify the temp tables #filelist and #header in the script to allow for new columns returned by the restore HeaderOnly and restore FilelistOnly commands is SQL 2008
Create Restore Command from DB Backup File
January 24, 2011 at 11:25 am
Thanks Michael and others.
Michael, I was able to make your script work in 2008 by adding some additional fields to the temp tables.
I would like to execute the restore instead of writing it in a select. Is there a way that I can execute the restore in the script?
Thanks much,
Howard
January 25, 2011 at 5:30 am
LOG SHIPPING can help you
NEVER NEVER EVER GIVE UP;-)
January 25, 2011 at 8:20 am
These restores are an audit requirement. Log shipping won't help there.
January 26, 2011 at 9:31 am
PHXHoward (1/24/2011)
Thanks Michael and others.Michael, I was able to make your script work in 2008 by adding some additional fields to the temp tables.
I would like to execute the restore instead of writing it in a select. Is there a way that I can execute the restore in the script?
Thanks much,
Howard
You could insert the output from the select into a local variable ( declare @sql nvarchar(max) ), and then execute it using sp_executesql.
You might have to tweak the script a little to place the database files in the correct directories and file names on the new server.
January 26, 2011 at 9:34 am
Thanks Michael. I thought about that but could not get it to work. Since the whole thing has to be inside single quotes, it was not quite working.
Now that I know I'm on the right track, I'll keep working on it. Thanks for your help.
January 27, 2011 at 12:49 pm
The way I did it was I used Management Studio to do the restore and just before I hit OK I hit the script button and it built the restore script just how I wanted the restore. Then I set up that restore script in the restore job to be scheduled.
January 27, 2011 at 3:06 pm
Hi OP,
in my environment, we got it setup like you are talking about. What I did was make a variable and store the entire backup statement into the variable and call it using exec (@cmd). eg:
declare @cmd varchar(8000)
/** Restore database **/
select @cmd = 'Your Restore Code here'
exec (@cmd)
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply