Restore DB Works in SQL Pro 7 GUI, Errors TSQL Manually

DBNewbie_2007DBNewbie_2007 Posts: 47 Bronze 3
edited May 12, 2015 8:36AM in SQL Backup Previous Versions
I am trying to restore about 70 databases individually (to do some synchronizations on a few archive SQL servers) from a backup on SQL Server 2005 to SQL Server 2008 R2. When I try to run the following command in SQL Server,

RESTORE DATABASE MyDBk
FROM DISK = '\KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb'
WITH MOVE 'MyDBk_Data' TO 'G:KProdMyDBk.mdf',
MOVE 'MyDBk_Log' TO 'G:KProdMyDBk.ldf', Stats = 10;

I receive the following error:

Msg 3132, Level 16, State 1, Line 2
The media set has 3 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

When I do a restore using the SQL Pro 7 GUI, it works fine (restoring a new database to the same file locations as listed above).

Is there a parameter or some other option that I need to include?

Running the following, everything looks fine:

RESTORE FILELISTONLY FROM DISK = '\KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb'

LogicalName PhysicalName Type FileGroupName Size MaxSize FileId
MyDBk_Data D:SQLServer2005DataMyDBkMyDBk.mdf D PRIMARY 524288000 35184372080640 1
MyDBk_Log D:SQLServer2005DataMyDBkMyDBk.ldf L NULL 66191360 2199023255552 2

Thanks! :)
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    What's the result when you run the following:
    EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [\KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb]"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • DBNewbie_2007DBNewbie_2007 Posts: 47 Bronze 3
    Sorry... I did not have "Notify me when a reply is posted" set.... here are the results:

    SQL Backup v7.7.0.7
    Reading SQB file header of "\KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb"

    Backup group ID : 142334605
    File number : 1 of 1
    Threads : 3 (multi-threaded file)

    Backup type : 1 (Database)
    Native backup size : 18.625 MB
    Database size : 563.125 MB
    Backup start : Sunday, April 05, 2015 3:36:27 AM
    Backup end : Sunday, April 05, 2015 3:36:28 AM

    Server name : KProdMSMSQL05
    Database name : MyDBk

    First LSN : 248000000052800147
    Last LSN : 248000000058900001
    Checkpoint LSN : 248000000052800147
    Database backup LSN : 248000000014200147


    name value
    exitcode 0
    sqlerrorcode 0
    filename001 \KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb
    · Share on Twitter
  • DBNewbie_2007DBNewbie_2007 Posts: 47 Bronze 3
    Looking over the documentation from RedGate, it appears the syntax should look like this:
    EXEC master..sqlbackup '-sql "RESTORE DATABASE MSMK_Temp FROM DISK = ''\KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb'' WITH MOVE ''MyDBk_Data'' TO ''G:KProdMyDBkMyDBk.mdf'',  MOVE ''MyDBk_Log'' TO ''G:KProdMyDBkMyDBk.ldf'' "'

    The "Stats = 10" appears to be unsupported. Once I removed it, it appears to work fine.. difference between RedGate syntax/options and Microsoft syntax/options. I just like the "Stats" options on larger (100G+ DBs) to know where we are in the restore process.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If you want to monitor the progress of the restore, you can run
    EXEC master..sqbstatus 1

    from Management Studio. This displays the progress of any active backups and restores.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.