Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restoring the latest database Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:04 AM
Points: 14, Visits: 81
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_DB Date 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?
Post #1566609
Posted Thursday, May 1, 2014 1:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:34 AM
Points: 1,143, Visits: 1,585
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


Post #1566644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse