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.
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).
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?
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!
At this time, the problem must be Windows 8, because I virtualized one WS2008 and the backup was restored without problem.