Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restore Backup from 2005 to 2008

Author  Topic 

kbarne
Starting Member

4 Posts

Posted - 2010-08-03 : 10:32:39
I am using SQLServerExpress2008R2 on a Window7 Professional PC (new) and want to get my current DB created in SQLServerExpress2005 on a Windows XP onto my new PC

I used the SSMS on SQLExpress2005 to back up the database to removable media. Then I used SSMS on the new PC and followed the steps for a restore from back up and got the following error.

"TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'HomeWin7-PC\sqlexpress'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476"

I tried the link and it says there is no further information.

The code behind the GUI back up is as follows:

BACKUP DATABASE [Stocks] TO  DISK = N'J:\StocksBackUp72710',
 DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BackupStocks.bak' WITH NOFORMAT, NOINIT,
NAME = N'Stocks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


Is there another way to move the db from one PC and SQL version to another or do I need to tweak the backup statement?

Thanks--Ken

Ken

Kristen
Test

22859 Posts

Posted - 2010-08-03 : 10:41:56
Dunno why there are two DISK commands in there, but I reckon there should only be one (the J: presuming that is your removable media)

FWIW we only ever take backups to a specific disk location, and then COPY them to where they are needed (J: in your case). The full backup you are making, direct to removable media, will break the backup-chain if any differential backups are made (and then relied on later)
Go to Top of Page

ssivaprasad
Starting Member

9 Posts

Posted - 2010-08-06 : 09:33:31
Detach the databases, copy across the mdf, ndf and ldf files to the media.

Use command below to attach the file

CREATE DATABASE database_name FOR ATTACH

Sivaprasad S - SIVA

http://sivasql.blogspot.com
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-07 : 09:16:55
Perhaps you misunderstood what multiple DISK options do in a backup. When you have something like this:

BACKUP DATABASE ... TO DISK = '<file x>'

SQL Server backs up the database to a single file.

With something like this:

BACKUP DATABASE ... TO DISK = '<file x>', DISK = '<file y>'

SQL Server backs up the database to 2 files, but the contents of the backup are spread across the 2 files. It doesn't mean that you are creating 2 independent backup files. You are still creating a single backup set, that is spread across 2 files. In order to restore from that backup set, you need to use both files e.g.

RESTORE DATABASE ... FROM DISK = '<file x>', DISK = '<file y>'

If you restored the database using only one file, SQL Server raises the error you encountered i.e.

'The media set has 2 media families but only 1 are provided.'

If you want to create multiple backup files which are independent, use the MIRROR TO option e.g.

BACKUP DATABASE ... TO DISK = '<file x>' MIRROR TO DISK = '<file y>'


Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-08-07 : 09:24:47
Thanks YRM ... in that case O/P needs to get BOTH the files to that target machine, and presumably only the file on J: will have been on removable media:

J:\StocksBackUp72710
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BackupStocks.bak

although it strikes me that making a single backup file, for the purposes of this transfer, would make this job easier
Go to Top of Page

kbarne
Starting Member

4 Posts

Posted - 2010-08-10 : 15:43:22
Thank you all for your input. Based on what I read here I redid the full backup but NOT to removable media. Then I copied the backup file (of which there was only one--unlike when I backed up to removable media which put one file on hard drive and one file on removable media) to jump drive and on the new pc invoked the restore database command pointing to the jump drive and the restore completed successfully.

Problem solved--thanks again

Ken
Go to Top of Page

IDontExist
Starting Member

3 Posts

Posted - 2010-08-11 : 09:23:12
Just to reiterate what Ray said - it's not backing up to removable media that caused the issue, it was backing up to two different files. Backing up directly to removable media is perfectly valid, but can cause issues when you're dealing with transaction log backups (as Kristen pointed out).
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 2014-05-03 : 01:43:04
follow this path to restore backup database, right click on the database go to tasks-> restore then database.
Note: Its mandatory to take backup of the log file too.
Go to Top of Page
   

- Advertisement -