Restore latest backup to a different database

RichardRayJHRichardRayJH Posts: 22
edited April 11, 2012 6:02PM in SQL Backup Previous Versions
I have a production database that has a test/training version. The test/training version usually gets updated every night, although users may request that it be left along for up to several days. Currently I run a standard SQL Server backup from the production database and give the backup file a known name. The backup is restored to the test database, several scripts are run to set up the test database for use, and the original backup is deleted. This process takes a fair bit of time, and could be shortened if I didn't have to create the separate backup just to use for the restore.

I have another scheduled job which does my actual working backups and keeps multiple copies. I'd like to write a restore script using SQL Backup Pro such that it would always get the latest of the full backups, restore it to the test database, and then call the setup scripts.I can do all of that aside from being able to figure out the name of the latest backup use for the restore.

Any thoughts on this?
-
Richard Ray
Jackson Hole Mountain Resort
Teton Village, WY
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Try using the LATEST_FULL option e.g. to restore the latest full database backup set from the 'e:\backups\' folder for the 'pubs' database, you could do this:
    EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs FROM DISK = [e:\backups\*.sqb] LATEST_FULL WITH REPLACE"'

    If you were restoring to a different database name, say pubs_copy, you would need to use the SOURCE option e.g.
    EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs_copy FROM DISK = [e:\backups\*.sqb] SOURCE = [pubs] LATEST_ALL"'

    There is also the LATEST_DIFF and LATEST_FULL options. The help file will provide more details on these options.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • RichardRayJHRichardRayJH Posts: 22
    ...
    EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs_copy FROM DISK = [e:\backups\*.sqb] SOURCE = [pubs] LATEST_ALL"'

    Perfect. That's exactly what I needed.

    Now, let me complicate things a bit. I do a similar thing to restore backups to a test server once a week to run DBCC CHECKDB on them and get another level of certainty as to the goodness of the backups. In that case the .sqb file is on an archive volume located on a NAS in another physical location on a fiber connection. Right now, I manually copy the .sqb to the test server to do the restore as I can't see the archive location for restore purposes; I can't see a way to make it appear 'local' to the restore command.

    I could do something tricky with PowerShell to get the file to a location where I could see it, but if there's a better way I'd be happy to hear about it!
    -
    Richard Ray
    Jackson Hole Mountain Resort
    Teton Village, WY
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If you are running the restore using the SQL Backup extended stored procedure, the SQL Backup Agent service startup account needs to have rights to read from the network share.

    If you are running the restore using the command line interface, then the account used to start the command prompt session needs to have rights to read from the network share.

    If there is no way you can allow SQL Backup to read from the network share, then as you mentioned, you may need to first use a script to copy the file to a readable location. You can find details of backups stored in the msdb table on the source instance, in the standard SQL Server backup history tables (backupset, backupmediaset, backupmediafamily etc).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Colin MillerchipColin Millerchip Posts: 80
    RichardRayJH wrote:
    ...
    Now, let me complicate things a bit. I do a similar thing to restore backups to a test server once a week to run DBCC CHECKDB on them and get another level of certainty as to the goodness of the backups.
    Richard, just FYI, SQL Backup v7.0 supports integrated DBCC CHECKDB, meaning that the SQL Backup engine will run the command post-restore, and include the results in its output / email notifications. The beta version of v7.0 is currently available at http://www.red-gate.com/products/dba/sql-backup/version-7/version-7-beta.

    Best regards,


    Colin.
    · Share on Twitter
Sign In or Register to comment.