What are the challenges you face when working across database platforms? Take the survey
Options

Create a copy database from a .sqb backup

toad2898toad2898 Posts: 41
edited January 9, 2012 10:05AM in SQL Backup Previous Versions
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

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    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]"'
  • Options
    Cheers. The move worked but I can't restore the latest backup from the 3 individual backups that are in the folder.

    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
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    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.
  • Options
    That kind of doesn't make sense as I know one of the files is a full backup that can be restored

    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?
  • Options
    Oh I see. If using the wildcard I need to tell it what the source is.

    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]"'
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Looks like the SOURCE keyword is what you'd use when the source and destination database names are different.
Sign In or Register to comment.