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

DBNewbie_2007

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!

petey

What's the result when you run the following:

EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [\KellyBackupsKProdMyDBk_BACKUP_20150405_033627.sqb]"'

DBNewbie_2007

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

DBNewbie_2007

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.

petey

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.