March 20, 2012 at 6:23 am
I have two servers (production and disaster recovery). My requirement is that I have to copy and restore the backup jobs from production to DR every two hours. To meet this requirement, I set up a job on production that takes a full backup daily, and differential backups every two hours. I then went to DR and setup a copy job that copies (pulls) the full and differential backups to DR every two hours. I set another job (on DR) to restore those backups to DR every two hours. The full backup runs and restores with no problems, but the differential errors. After some research, the error I encountered has to do with the differential backup file id sequence. I changed my script to grab the latest differential file id, and it worked properly--so I know this is the issue.
My question is how to grab and use the latest differential as part of the restore operation in my script. Does anyone have a script snipet they could share to do this. Essentially, I need to use the latest differential as part of the restore operation.
My restore script is pretty straightforward...Here it is....
--Sets db to single user
ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--Restores full DBNAME database with no recovery--
RESTORE DATABASE DBNAMEfrom disk = N'C:\MSSQL_Data\SQLBAK\Backups\DBNAME_full.bak'
WITH FILE = 1, NOUNLOAD, NORECOVERY, REPLACE;
go
--Restores differential DBNAME database with recovery--
RESTORE DATABASE DBNAME
from disk = N'C:\MSSQL_Data\SQLBAK\Backups\DBNAME_diff.bak'
WITH FILE = 452, NOUNLOAD, REPLACE, RECOVERY;
go
--Sets db back to multuser mode
ALTER DATABASE [DBNAME] SET MULTI_USER;
GO
March 20, 2012 at 7:06 am
if you have subsequent diff backups for the databases to restore then you need to leave them in norecovery mode, other wise you need to restore the full backup and the latest diff again
you could get the latest file from msdb.dbo.backupset for the database on the primary and push it into the seconday so you can select the filename and build it dynamically inter the script using a variable
March 20, 2012 at 7:17 am
Thanks for the response.
Understood about the restore sequence...full first, then last differential.
I was curious if anyone had a code snipet that would dynamically pull the latest differential from the msdb and use it.....
March 20, 2012 at 7:26 am
select top 1 b2.physical_device_name from msdb.dbo.backupset b1
inner join msdb.dbo.backupmediafamily b2
on b1.media_set_id = b2.media_set_id
where b1.type = 'I' and b1.database_name = ''
order by b1.backup_finish_date desc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply