April 30, 2014 at 3:01 pm
We have the following databases on one of our SQL Serve instances.
•ABC123
•ABC456
•ABC765
•ABC234
•ABC_SETTINGS
•ABC
The ABC[numbers] databases are different versions. The ABC database is a copy of the latest ABC[numbers] database. The ABC Setting contains setting and history data for each ABC[numbers] database.
Within the ABC_SETTINGS database there is a table called VERHIS which contains details of the version history if the database
Name_DBDate Version
ABC123 01/01/2014 1
ABC456 4/4/2014 2
ABC765 7/4/2014 3
ABC234 10/4/2014 4
We currently backup all the databases but would like to know if it possible to set a job to check the latest ABC[] database and restore to ABC?
May 1, 2014 at 1:29 am
Get the latest backup history and generate a restore SQL script then run it on a schedule from sql agent job, eg:
DECLARE @SQLCMD nvarchar(MAX)
,@DBtoRestore nvarchar(256)
,@BackupLocation nvarchar(MAX)
,@BackupPosition int
SELECT TOP 1
@DBtoRestore = bs.database_name
,@BackupLocation = mf.physical_device_name
,@BackupPosition = bs.position
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
JOIN ABC_SETTINGS abc
ON bs.database_name = abc.Name_DB
WHERE type = 'D'
ORDER BY abc.Version DESC, bs.position DESC
SELECT @SQLCMD = N'USE ['+ @DBtoRestore + ']; ALTER DATABASE ['+ @DBtoRestore + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
SELECT @RestoreSQL = N'USE [master]; RESTORE DATABASE [' + Name_DB + '] FROM DISK = '''+ @BackupLocation + ''' WITH REPLACE, RECOVERY'
SELECT @SQLCMD = @SQLCMD + @RestoreSQL
EXEC @SQLCMD
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply