September 2, 2004 at 11:40 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/kee
September 29, 2004 at 9:50 am
This is what I have used in the past to find the latest backup from a particular database and use the file to restore over an existing database:
DECLARE @DBBackupFileName as varchar(128)
DECLARE @DBRestoreName as varchar(40)
DECLARE @DBName as varchar(40)
SET @DBName = 'Prod_DB_Name'
SET @DBRestoreName = 'QA_DB_Name'
SET @DBBackupFileName = (
SELECT t1.physical_device_name
FROM msdb.dbo.backupmediafamily t1
JOIN msdb.dbo.backupset t2
ON t1.media_set_id = t2.media_set_id
WHERE t2.database_name = @DBName
AND t2.backup_finish_date IN
(SELECT MAX(t2.backup_finish_date)
FROM msdb.dbo.backupset t2
WHERE t2.database_name = @DBName)
)
RESTORE DATABASE @DBRestoreName
FROM DISK = @DBBackupFileName
September 29, 2004 at 11:18 am
That works great for the same server. Typically in this scenario, the backup is coming from another server, or being pushed to another server. Still this would help with identifying the lastest backup file.
October 3, 2004 at 3:50 pm
Sorry for the distraction with a non-technical question, but I'm missing something here. If QA is working on testing of a new build that is heading for production, for example, won't their testing environment get destroyed each night? Perhaps we have different definitions of "QA". If your version of "QA" as described, is the same as what I will call an "Emergency" environment we are in sync. An emergency environment is where production support could occur in case of an emergency fix. I could see restoring to the emergency environment each night so that it is as close to production as possible.
Thanks! Your thoughts are greatly appreciated.
Mike
p.s. Have a great one!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply