Restore Problem in SQL 2008

Question

question

Leo asked Sep 8, '11

Restore Problem in SQL 2008

Hi, I am getting the following error message yesterday when I restored one Database onto another server. >Msg 3241, Level 16, State 0, Line 1 The media family on device E:\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\TestDB.bak is incorrectly formed. SQL Server cannot process this media family. >Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. I didn't change anything on Server and both server has the same service pack. I am getting the error message on Restore Command and same in GUI as well. Any Idea? [Edit] Results of Restore Headeronly and Restore Filelistonly HeaderOnly NULL,NULL,1,NULL,0,1,2,sa,PRODUCT_01,TestDB,655,2010-08-19 11:27:10.000,3885596672,597911000000346400043,597914000003892100001,597911000000346400043,597911000000339300149,2011-09-08 15:20:28.000,2011-09-08 15:21:00.000,52,0,1033,196609,80,4608,10,0,4000,TMS_SQL_01,512,CA58D763-3748-4BE7-B107-37D262AD455D,FB445964-004E-47DE-9DB0-1C0FE5FDDF88,SQL_Latin1_General_CP1_CI_AS,1FA525B4-40F6-497C-8570-B2AA938C297B,0,0,0,0,0,0,0,0,0,0,FB445964-004E-47DE-9DB0-1C0FE5FDDF88,NULL,FULL,NULL,NULL,Database,01C2B429-6B28-4CD4-8ED6-1F808989997F,3885596672 FilesOnly DataTakeon_Data,E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB.mdf,D,PRIMARY,30774198272,35184372080640,1,0,0,00000000-0000-0000-0000-000000000000,0,0,3804889088,512,1,NULL,597911000000339300149,463A52B5-02E3-497E-B9A4-632F63423AA0,0,1,NULL DataTakeon_Log,F:\SQL_Server_Logs\TestDB.ldf,L,NULL,20925841408,35184372080640,2,0,0,00000000-0000-0000-0000-000000000000,0,0,0,512,0,NULL,0,00000000-0000-0000-0000-000000000000,0,1,NULL

sql-server-2008 sql database backup-restore

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-09-08T07:14:26Z

Kev Riley answered Sep 8, '11

Try copying the file from the source server again. How are you transferring the file? Using any compression or FTP?

12

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo commented · Sep 08, 2011 at 07:16 AM

@Kev, normal copy and past into another server. I did try on different server as well to make sure Server B is causing the Disk Issue but I am getting the same restore on every servers.

0 ·

Kev Riley ♦♦ commented · Sep 08, 2011 at 07:19 AM

Can you VERIFY the backup on the original server? RESTORE VERIFYONLY FROM DISK='filename';

0 ·

Leo commented · Sep 08, 2011 at 07:28 AM

@Kev, it said - The backup set on file 1 is valid.

0 ·

Kev Riley ♦♦ commented · Sep 08, 2011 at 07:49 AM

can you restore that backup to another database on the original server?

0 ·

Leo commented · Sep 08, 2011 at 08:36 AM

@Kev - Yes...that work on same Server with diff db name.

0 ·

Show more comments

Answer 2 · 2011-09-08T07:19:25Z

Fatherjack answered Sep 8, '11

What are the results to these queries /* verify backup set is valid */ RESTORE VERIFYONLY FROM DISK = N'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\TestDB.bak' /* list backups within device */ RESTORE HEADERONLY FROM DISK = N'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\\Backup\TestDB.bak'' /* list file names in backup set */ RESTORE FILELISTONLY FROM DISK = N'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\TestDB.bak''

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo commented · Sep 08, 2011 at 07:30 AM

@John... it said - The backup set on file 1 is valid. I can try other 2 for Header Only and File Only... I will let you know. Is that safe to do it on Production Server?

0 ·

Fatherjack ♦♦ commented · Sep 08, 2011 at 07:40 AM

yes it is safe but dont believe me. Highlight RESTORE |next word| in SSMS and press F1 to read the help. Header only - http://msdn.microsoft.com/en-us/library/ms178536.aspx Verifyonly - http://msdn.microsoft.com/en-us/library/ms188902.aspx

0 ·

Leo commented · Sep 08, 2011 at 02:24 PM

@John... I just done other 2. I saved the result set in CSV file. How can I upload the results?

0 ·

Fatherjack ♦♦ commented · Sep 08, 2011 at 02:33 PM

just edit your question and paste the details into there. Is there anything interesting in them?

0 ·

ThomasRushton ♦♦ commented · Sep 08, 2011 at 03:44 PM

@Leo - who's @John? Can't be him - he's not been here for nearly 2 years...

0 ·

Show more comments

Answer 3 · 2011-09-08T07:31:16Z

Håkan Winther answered Sep 8, '11

Is one of the servers standard edition and the backup is compressed? Compressed backup in SQL2008 is an enterprise feature.

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo commented · Sep 08, 2011 at 07:33 AM

No. They both STD Edition.

0 ·

Kev Riley ♦♦ commented · Sep 08, 2011 at 07:36 AM

are they both SQL 2008, and not one is a 2008R2?

0 ·

Leo commented · Sep 08, 2011 at 07:40 AM

They are on same SQL 2008 STD Edition.

0 ·

Håkan Winther commented · Sep 08, 2011 at 07:43 AM

Are you using the native backup or are you using at third party tool?

0 ·

Leo commented · Sep 08, 2011 at 07:46 AM

Native Backup. It used to work but since last week, restore doesn't work. We did attach to SAN but we are not using any Data on SAN yet. All the DB are still in Local Disk.

0 ·

Answer 4 · 2011-09-08T07:52:31Z

Fatherjack answered Sep 8, '11

is there enough room on the server for the restore to take place?

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo commented · Sep 08, 2011 at 08:42 AM

Yes..it has enough space.

0 ·

Answer 5 · 2011-09-08T10:43:08Z

Shawn_Melton answered Sep 8, '11

From reading all the comments you might want to update your question with everything that you have done and output from the questions/comments, so everyone knows what has happened so far. Can you combine all the output from the BACKUP argument commands @Fatherjack provided and add those to your question as well? ServerA is the original instance where the backup came from, you are trying to restore that database backup to a new (or existing database) on ServerB? You can restore this backup to ServerA but with a different database name? Then a few things to check to just be sure...What was the result of the last DBCC CHECKDB command on the original database (was it clear of errors)? If you create a new database on ServerB, back it up, and then try and restore it with the same command you are using for the other backup do you get the same results?

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo commented · Sep 08, 2011 at 02:25 PM

Run DBCC CHECKDB already - there is no errors in Database.

0 ·

Answer 6 · 2011-09-09T08:00:16Z

Fatherjack answered Sep 9, '11

OK, the problem is the location of the database files. Your backup is expecting to restore the mdf to the E:\ and the Log file to the F:\. I am guessing that you dont have both of those drives on your machine where you are trying to restore. Try executing a restore using the MOVE argument. Assuming you want to restore both mdf and ldf to the same drive which is labelled E:\\ the command would be RESTORE DATABASE TestDB FROM DISK=N'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\TestDB.bak' WITH MOVE 'DataTakeon_Data' TO 'E:\DatabaseDirectory\DataTakenon.mdf', MOVE 'DataTakeon_Log' TO 'E:\DatabaseLOGDirectory\DataTakenon.ldf'

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo commented · Sep 09, 2011 at 09:10 AM

Still no luck - I am getting same error on Destination Server. Msg 3241, Level 16, State 0, Line 1 The media family on device 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\TestDB.bak' is incorrectly formed. SQL Server cannot process this media family. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

0 ·

question