February 14, 2006 at 11:38 pm
Its my first effort at restoring a db onto MS SQL 2005. I've got a backup file from my web host, the db is MS SQL 2005. If I run this SQL I get an error i've never seen before on SQL 2000:
RESTORE
DATABASE ehci FROM DISK='C:\Inetpub\wwwroot\EHCI\ehcibackup.bak' WITH RECOVERY, MOVE 'ehci_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ehci_data.mdf', MOVE 'ehci_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ehci_log.ldf'
GO
Error:
Msg 3132, Level 16, State 1, Line 2
The media set has 2 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.
February 15, 2006 at 1:15 am
Has your database been backed up to append(rather than overwrite) the backup to a previous backup file?
February 15, 2006 at 1:18 am
Thanks for the reply David,
No I logged in to SMSS myself and backed it up to one of my web FTP folders. There was no existing db in the target directory.
February 15, 2006 at 7:22 am
I think that you backed up to 2 files somehow in this case. If you had two files listed in the backup dialog, even if one was selected, the backup was striped.
You need both files to restore.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 15, 2006 at 7:27 am
Ok Steve, I'll run another backup and try again. Thanks.
February 15, 2006 at 7:41 am
Hmm.. even if i try backing up with T-SQL:
BACKUP
DATABASE [ehci] TO DISK = 'C:\Inetpub\www\ehci.com.au\www.ehci.com.au\data_files\ehcibackup.bak'
GO
I get the:
Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
error.
I've tried the backup function in SMSS but I don't see any extra setting to make sure I'm backing up mdf and ldf.
These are the settings I am trying:
Backup Type: Full
on the options page:
Backup to a new media set and erase all existing backup sets.
Verify when finished.
I'm still getting the error on Restore.
February 15, 2006 at 7:43 am
Forgot to mention I'm deleting out the .bak file via FTP between tasks.
February 15, 2006 at 10:43 am
Stephen,
What do you see if you run
RESTORE LABELONLY
FROM DISK = 'C:\Inetpub\wwwroot\EHCI\ehcibackup.bak'
Greg
Greg
February 15, 2006 at 2:34 pm
NULL 2E3812D0-CF25-4DCE-879C-4832DA3A34D7 2 1 B106E6C4-0000-0000-0000-000000000000 1 0 NULL Microsoft SQL Server 4608 2006-02-16 01:32:49.000 1
February 17, 2006 at 2:19 am
Well I tried doing another backup today and I got no errors using the same SQL. Restored fine too. I don't know what changed to make it trouble free this time but thanks for your help guys.
February 17, 2006 at 7:22 am
I THINK !!!
THE backup which you are restoring is not properly taken.
pls take the backup again
and then try to restore it!!!
April 19, 2006 at 12:04 pm
The Backup media you took was invalid.
Backup the database again and then use the restore statement.... It should work fine.
Here is an example:
RESTORE
DATABASE DSDATA
FROM
DISK = 'D:\SQL_BACKUPS\DSDATA_BKUP.BAK'
WITH
MOVE 'DSData_Data' TO 'D:\SQL_DATA\DSDATA_DATA-P1.mdf',
MOVE 'DSData_Data2' TO 'D:\SQL_DATA\DSDATA_DATA-P2.ndf',
MOVE 'DSData_Log' TO 'D:\SQL_LOGS\DSDATA_LOG-P.ldf',
NOUNLOAD
, REPLACE, STATS = 10
GO
D Danial
D. Danial
April 19, 2006 at 2:59 pm
The question is why are you restoring with code when you can just put your .bak file in the backup sub folder in Microsoft SQL Server folder in programs and then go to management studio and use the backup and restore wizard choose the restore from device option. The only thing to remember is make sure the file path created for you is correct. You can use this way to restore a database sent to you on CD or email. The only known issues wrong file path and orphaned permissions, just delete and start again. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply