1

I have got a production server with SQL Server 2008 Standard version doing backup copies using maintenance plans. I am doing one full copy on Sundays and one differencial copy the rest of the days of the week.

The problem comes when I take the copies to other servers to restore them. In the original server I verify the bak file is OK, but when I do the same on the other servers, it says:

Msg 3241, Level 16, State 0, Line 5
The media family on device '[path].bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 5
VERIFY DATABASE is terminating abnormally.

I use this script to verify the database:

DECLARE @full_fillename varchar(1024);

SET @full_fillename = N'[path].bak';

RESTORE VERIFYONLY
FROM DISK = @full_fillename
GO

IF @@ERROR = 0
BEGIN
   PRINT 'Backup usable'
END
ELSE
BEGIN
    PRINT 'Backup not usable'
END
GO

I test this on my local machine with SQL Server 2008 Enterprise, SQL Server 2012 Standard, SQL Server 2008 Express, SQL Server 2012 Express. But this only works on the original server where the backup is done.

Other notes about the problem:

To restore the database I need to use MOVE commands. For example I use this script to restore the backup file:

RESTORE DATABASE [db_name]
    FROM DISK = '[path].bak'
    WITH 
        FILE = 1,
        MOVE N'[db_name]_cs_as' TO N'C:\Archivos de programa\Microsoft SQL Server\MSSQL10.CLASS\MSSQL\DATA\[db_name]_cs_as.mdf',
        MOVE N'[db_name]_cs_as_log' TO N'C:\Archivos de programa\Microsoft SQL Server\MSSQL10.CLASS\MSSQL\DATA\[db_name]_cs_as_log.ldf'  

See that my database name is "XXXX" and the files to move are called "XXXX_cs_as" and "XXXX_cs_as_log". This issue is because I rename the database in the past.

UPDATE

Rename logic and physical names does not solve the problem.

This is fustrating. The original server is a SQL SERVER 2008 STANDARD SP4, and the backup created by this server cannot be restore in any other SQL SERVER that I tested (SQL SERVER 2008 ENTERPRISE, SQL SERVER 2008 EXPRESS, SQL SERVER 2012, etc).

UPDATE 1

Server: Windows Server 2008, SQL SERVER 2008 STANDARD SP4

select @@version

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Other computer: Windows 8.1, SQL SERVER 2008 STANDARD SP4

select @@version

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) 

In other computer I cannot restore the backup, neither execute this script:

RESTORE FILELISTONLY 
FROM DISK = @full_fillename

Can Windows 8 be the problem?

UPDATE 2

I've downloaded the SQL SERVER 2008 ENTERPRISE EVALUATION, I install this version on the original server and my computer (windows 8).

I create a new database in the original server and import a script with the content of the database with the problem (there are only tables and rows).

I make a backup from the new SQL SERVER 2008 ENTERPRISE. I take the backup (.bak) and put it in my computer with the same version of SQL SERVER.

The backup cannot be restored!

UPDATE 3

At this time, the problem must be Windows 8, because I virtualized one WS2008 and the backup was restored without problem.

  • How do you copy the backup to other server? via FTP? if yes , is it in Binary mode? – KASQLDBA Dec 2 '15 at 11:35
  • I using Team Viewer to get a copy. Also I bring the files zipped by rar. – dlopezgonzalez Dec 2 '15 at 11:36
  • The thing is that I have created a test database with one table, then I made a backup copy and I can see that this copy is verified in the other server. The issue must be something of the database. – dlopezgonzalez Dec 2 '15 at 11:40
  • "The issue must be something of the database." - not necessarily. Your test with a simple small database does not rule out transfer errors that are more likely to affect larger files than smaller ones. To confirm that the file is the same once it has arrived at the other end of the transfer, try running a checksum over the source and destination files (if you don't already have a tool for that there are many available like bitser.org/checksum-calculator-windows-7-freeware.shtml). – David Spillett Dec 2 '15 at 14:05
  • The checksum is not the problem. – dlopezgonzalez Dec 2 '15 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.