Comments
4 comments
-
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. -
...
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! -
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). -
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.
Best regards,
Colin.
Add comment
Please sign in to leave a comment.
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?