Red Gate forums :: View topic - Restore latest backup to a different database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

Restore latest backup to a different database

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
RichardRayJH



Joined: 21 Sep 2009
Posts: 13
Location: Teton Village, WY, USA

PostPosted: Fri Apr 06, 2012 7:12 pm    Post subject: Restore latest backup to a different database Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Mon Apr 09, 2012 7:55 am    Post subject: Reply with quote

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:
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:
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
Back to top
View user's profile Send private message Send e-mail
RichardRayJH



Joined: 21 Sep 2009
Posts: 13
Location: Teton Village, WY, USA

PostPosted: Mon Apr 09, 2012 5:47 pm    Post subject: Re: Reply with quote

...

Code:
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
Back to top
View user's profile Send private message Send e-mail
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Tue Apr 10, 2012 12:43 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Colin Millerchip



Joined: 31 Oct 2007
Posts: 65
Location: Cambridge, UK

PostPosted: Wed Apr 11, 2012 11:02 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group