Restore db to new one from last backup

Question

Restore db to new one from last backup

branovuk

SSCommitted

Points: 1774
More actions
August 5, 2009 at 10:07 am

#385120

Hi,
How to automate process of restoring db to new one every night. Problem is next: I am doing daily backup with maintenance plan, which gives new name to backup, you know, with date part ( e.g. db_200908050600) on the end, but I need last one (3 days backup is in backup folder) to be restored.
Thanks,
brano

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

I addressed this issue with a batch replication process put in place for a third party app's database.

Create a backup sproc that uses a naming standard (and dates). Schedule backup and push through SqlAgent. Relevant Sql [backup database]

Create a restore sproc that uses naming standard to restore said database. Relevant SQL Restore database.

Other Concerns; Off-site databases may have similar names that you may need to rename. Orphans. Error catching. physical filename.

JamieX Hall of Fame Points: 3763 More actions · Answer 2

You could also read the msdb tables and find the latest file backed up.

I just whipped this together and could probably be written better - and should be tested! But should work if you're only doing disk backups on the server. Then you can take this value and write a restore statement to your new database.

SELECT
bs.[database_name]
,bs.[backup_start_date]
,bf.[physical_device_name]
FROM [msdb].[dbo].[backupset] bs
inner join [msdb].[dbo].[backupmediafamily] bf on bs.media_set_id = bf.media_set_id
inner join
(select [database_name], MAX([backup_start_date]) [backup_start_date]
FROM [msdb].[dbo].[backupset] bs
WHERE database_name in ('master','msdb')
GROUP BY [database_name]) latest on latest.[database_name] = bs.[database_name] and
bs.backup_start_date = latest.backup_start_date

branovuk SSCommitted Points: 1774 More actions · Answer 3

Thank you guys,

there are some great information in your posts, regards,

Brano

branovuk SSCommitted Points: 1774 More actions · Answer 4

jamie (8/5/2009)
You could also read the msdb tables and find the latest file backed up.
I just whipped this together and could probably be written better - and should be tested! But should work if you're only doing disk backups on the server. Then you can take this value and write a restore statement to your new database.
SELECT
bs.[database_name]
,bs.[backup_start_date]
,bf.[physical_device_name]
FROM [msdb].[dbo].[backupset] bs
inner join [msdb].[dbo].[backupmediafamily] bf on bs.media_set_id = bf.media_set_id
inner join
(select [database_name], MAX([backup_start_date]) [backup_start_date]
FROM [msdb].[dbo].[backupset] bs
WHERE database_name in ('master','msdb')
GROUP BY [database_name]) latest on latest.[database_name] = bs.[database_name] and
bs.backup_start_date = latest.backup_start_date

Nice, but it giving me trn file as a last one, not bak - since I have regular trn backups. Must work on it :), thanks again ...

JamieX Hall of Fame Points: 3763 More actions · Answer 5

Oh ya, forgot you need bs.type = 'D' in the where clause

branovuk SSCommitted Points: 1774 More actions · Answer 6

jamie (8/6/2009)
Oh ya, forgot you need bs.type = 'D' in the where clause

Thanks, SSC Veteran, that is the way!

Thanks again, regards,

Brano

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 7

I have a few scheduled prod db backups, xcopy to test server and restore automated. Basically I manually coded the db backup.. backup database xyx to disk= 'e:bcksql\xyz.bak'. This will give you a constant name, then xcopy to test server, next step delete backup file. On test server the restore syntax is the same everytime, upon restore success delete .bak file.

Or to simplify it even more backup accross the network.