Keeping QA Up To Date - Part 2

  • Comments posted to this topic are about the content posted at

  • 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)



       FROM DISK = @DBBackupFileName



  • 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.

  • 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.


    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