Restore multiple db's onto another server

zarty

I just started a new job for a company as their new DBA and I've inherited Redgate SQL Backup. What I want to do is restore multiple backups taken from our nightly backup and restore them all onto a dev or test server. If I were using standard SQL backup, it would be too easy so I won't even explain how I would do it. However, I'm finding it really difficult to find a sensible way to do this using Redgate. Help, suggestions please?

Part 1 of the problem is that the nightly backups create backup file names with the date and time in them. While this is necessary for keeping a few backups of the same db on disk, it's a pain in the glutes to work with when it comes to trying to automate a restore process. Is there way for Redgate to just look for the latest backups? I don't mean manually clicking through files!!! I mean a scheduled job that does these restores to a dev or test server.

Part 2 of the problem is that when looking at the Restore wizard, there does not seem to be a way to restore to a remote server. I should note that the remote servers in question do have a Redgate Backup licence. Looking at the help, it seems the only way to specify a remote server is if restoring a single database, not multiple databases?

I've looked at the command line option. I can certainly script out multiple restores, which is great. However, I am then stuck because of the timestamps in the backup file names.

Thanks,
Clive

HJoyce

Hi Clive.

Part 1 of the problem is that the nightly backups create backup file names with the date and time in them

The default file name format includes the date stamp, however you can specify your own file name format for each server from the Options dialog on the Tools menu. There's information about using tags to create a file name format in the help file http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=SQL_Backup/help/5.4/SBU_FileLocationTags.htm&toc=SQL_Backup/help/5.4/toc545271.htm.


There isn't an option in the GUI to allow you to restore multiple databases, but you're right that this can be scripted. There's a post here which you might find useful: http://www.red-gate.com/messageboard/vi ... php?t=4625

when looking at the Restore wizard, there does not seem to be a way to restore to a remote server

You can use the Restore wizard to restore to a remote server. You need to add the server you want to restore to in SQL Backup. When you open the Restore wizard you can then select the SQL Server on step 1. You will need to browse for the backup files to restore.

Hope that helps.
Helen

dylanfinney

You can use the Restore wizard to restore to a remote server. You need to add the server you want to restore to in SQL Backup. When you open the Restore wizard you can then select the SQL Server on step 1. You will need to browse for the backup files to restore.

And to be clear that server must also have SQL Backup server components installed on it.

Clive, can you get the file names that you need for your script out of the
[msdb].[dbo].[backupset] and [msdb].[dbo].[backupmediafamily] tables? Here's a chunk of code that might help you out.

SELECT		[backupset].[backup_set_uuid],
			[backupset].[backup_start_date],
			[backupset].[type],
			[backupmediafamily].[physical_device_name],
			RIGHT([backupmediafamily].[physical_device_name],CHARINDEX('\',REVERSE([backupmediafamily].[physical_device_name])) - 1) AS [FileName]

FROM		[msdb].[dbo].[backupset]
INNER JOIN	[msdb].[dbo].[backupmediafamily]
ON			[backupset].[media_set_id] = [backupmediafamily].[media_set_id]

WHERE	[backupset].[database_name] = @RestoreFromDB
AND		[backupset].[backup_start_date] BETWEEN @RestoreStart AND @RestoreEnd
AND		[backupmediafamily].[logical_device_name] LIKE 'Red%'

If you need the actual file paths of the log and data files you can find those out in [msdb].[dbo].[backupfile]