Restore latest backup to a different database

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Restore latest backup to a different database

Postby RichardRayJH » Fri Apr 06, 2012 7:12 pm

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
RichardRayJH
 
Posts: 13
Joined: Mon Sep 21, 2009 9:49 pm
Location: Teton Village, WY, USA

Postby petey » Mon Apr 09, 2012 7:55 am

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:

Code: Select all
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.

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby RichardRayJH » Mon Apr 09, 2012 5:47 pm

...

Code: Select all
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
RichardRayJH
 
Posts: 13
Joined: Mon Sep 21, 2009 9:49 pm
Location: Teton Village, WY, USA

Postby petey » Tue Apr 10, 2012 12:43 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby Colin Millerchip » Wed Apr 11, 2012 11:02 pm

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.
Colin Millerchip
 
Posts: 66
Joined: Wed Oct 31, 2007 5:31 pm
Location: Cambridge, UK


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests