Create a copy database from a .sqb backup
toad2898
Posts: 41
Hello.
Because of the many advantages I've recently moved to using SQLBackup Pro over native SQL backups, however I've discovered one scenario that means I might have to keep using native backups for a couple of databases.
On a scheduled basis I create a couple of "copy" databases from the latest full backup of the production databases. Here's the T-SQL I use to do it:
RESTORE DATABASE Prod_LiveCopy
FROM Prod
WITH REPLACE,
MOVE 'Prod' TO 'E:\SQL MDF\Prod_LiveCopy.mdf',
MOVE 'Prod_Log' TO 'D:\SQL LDF\Prod_LiveCopy_Log.ldf';
GO
I would like to be able to do the same via SQLBackup Pro,I know I can't do anything like this via the UI.The nearest thing I found searching this forum is this:
EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [e:\backups\*AdventureWorks*] LATEST_FULL WITH REPLACE"'
Can I use the MOVE options from my T-SQL in the sqlbackup stored proc?
Cheers
Because of the many advantages I've recently moved to using SQLBackup Pro over native SQL backups, however I've discovered one scenario that means I might have to keep using native backups for a couple of databases.
On a scheduled basis I create a couple of "copy" databases from the latest full backup of the production databases. Here's the T-SQL I use to do it:
RESTORE DATABASE Prod_LiveCopy
FROM Prod
WITH REPLACE,
MOVE 'Prod' TO 'E:\SQL MDF\Prod_LiveCopy.mdf',
MOVE 'Prod_Log' TO 'D:\SQL LDF\Prod_LiveCopy_Log.ldf';
GO
I would like to be able to do the same via SQLBackup Pro,I know I can't do anything like this via the UI.The nearest thing I found searching this forum is this:
EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [e:\backups\*AdventureWorks*] LATEST_FULL WITH REPLACE"'
Can I use the MOVE options from my T-SQL in the sqlbackup stored proc?
Cheers
Comments
You can use the WITH MOVE command in SQL Backup in exactly the same way as SQL Server.
Because you are using the sqlbackup extended stored procedure, though, you may want to use the bracket as a delimiter to avoid escaping issues. For instance:
exec master..sqlbackup '-SQL "RESTORE DATABASE [mydatabase] from disk=[c:\mybackup.sqb] WITH REPLACE,
MOVE [Prod] TO [e:\mssql\data\prod.mdf]"'
e.g.
this works....
EXEC master..sqlbackup '-sql "RESTORE DATABASE Logging_LiveCopy
FROM DISK = [F:\SQL BACKUP\Logging\Logging_FULL_20120107_200002.sqb] WITH REPLACE,
MOVE ''Logging'' TO [E:\SQL MDF\Logging_LiveCopy.mdf],
MOVE ''Logging_Log'' TO [D:\SQL LDF\Logging_LiveCopy_Log.ldf]"'
GO
this doesn't....
EXEC master..sqlbackup '-sql "RESTORE DATABASE Logging_LiveCopy
FROM DISK = [F:\SQL BACKUP\Logging\Logging*.sqb] LATEST_FULL WITH REPLACE,
MOVE ''Logging'' TO [E:\SQL MDF\Logging_LiveCopy.mdf],
MOVE ''Logging_Log'' TO [D:\SQL LDF\Logging_LiveCopy_Log.ldf]"'
returns....
Error 507: No valid backup sets found from provided folder(s).
The only thing I can think of why this doesn't work is because although I'm taking a full back up of the same database to the same folder each night, this does not
mean it is actually a backup "set", so it fails.
Thanks
I guess no files that match the wildcard match a full database backup of the database you want to restore. You can examine the contents of a backup using the RESTORE SQBHEADERONLY command of the sqlbackup extended stored procedure.
If I delete the other two and leave just the one full backup that I have restored by naming explicitly it still wont restore it using the wildcard?
EXEC master..sqlbackup '-sql "RESTORE DATABASE Logging_LiveCopy
FROM DISK = [F:\SQL BACKUP\Logging\*.sqb] SOURCE = ''Logging''LATEST_FULL WITH REPLACE,
MOVE ''Logging'' TO [E:\SQL MDF\Logging_LiveCopy.mdf],
MOVE ''Logging_Log'' TO [D:\SQL LDF\Logging_LiveCopy_Log.ldf]"'