SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restoring the latest database


Restoring the latest database

Author
Message
system243trd
system243trd
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 117
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?
Andrew G
Andrew G
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2816 Visits: 2265
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search