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.
Author |
Topic |
kbarne
Starting Member
4 Posts |
|
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) |
|
|
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 fileCREATE DATABASE database_name FOR ATTACHSivaprasad S - SIVAhttp://sivasql.blogspot.com |
|
|
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 MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL 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 |
|
|
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:\StocksBackUp72710c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BackupStocks.bakalthough it strikes me that making a single backup file, for the purposes of this transfer, would make this job easier |
|
|
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 againKen |
|
|
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). |
|
|
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. |
|
|
|
|
|
|
|