Restore SQL Server 2008 database onto SQL Server 2014?

Question

I have a backup file that I need to restore daily on schedule based.

But the backup file is from SQL Server 2008 and restore destination is SQL Server 2014. And also the backup file is from cross network. I have copied to local shared folder and trying to restore it.

The code I used:

USE [master]
RESTORE DATABASE MyDB FROM
DISK = N'\\Mylocation\DBRestores\MyBackupFile'
WITH
 FILE = 1, -- 1 = .bak, 2 = .trn type backup
 MOVE N'MyDB_data' TO N'S:\MSSQLsql\mssql\DATA\MyDB.mdf',
 MOVE N'MyDB_log' TO N'L:\MSSQLsql\mssql\DATA\MyDB_log.ldf',
 NOUNLOAD,
 STATS = 5 
go

but I get an error

Msg 3241, Level 16, State 0, Line 3
The media family on device '\Mylocation\DBRestores\MyBackupFile' is incorrectly formed.
SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Any help? thanks.

• Alex K.: 2008 => 2014 should work fine. That's a path to a file with no .bak extension? - What's the output of RESTORE FILELISTONLY FROM DISK='\\Mylocation\DBRestores\MyBackupFile'
• marc_s: What is the compatibility level of that source database? SQL Server 2012 and newer do not support compatibility level 80 (SQL Server 2000) anymore ....
• Ali Razeghi - AWS: Prior to checking compatibility level which will take effect after the restore, please check to see if your backup script includes 'WITH FORMAT' and let us know. Thanks.
• user3583912: Hi @Alex, the output is: Cannot open backup device '\\Mylocation\DBRestores\MyBackupFile'. Operating system error 2(The system cannot find the file specified.). Thanks
• user507: Please show your backup command from the original server.

Answer 1

Check if the SQL Server user account is able to read from "\Mylocation\DBRestores\", the security permissions from file share and file system.

To really test that it works, copy the backup to local server and run it from there.

Answer 2

This part of your SQL is incorrect

DISK = N'\Mylocation\DBRestores\MyBackupFile'

it should be for example

DISK = N'\Mylocation\DBRestores\MyBackupFile.bak'

you are supplying a directory but should be supplying the full spec of the backup file.

Answer 3

For your environment you need something like:

USE [master];
RESTORE DATABASE MyDB FROM
DISK = N'\\Mylocation\DBRestores\MyBackupFile.bak'
WITH
 FILE = 1,
 MOVE N'MyDB_data' TO N'S:\MSSQLsql\mssql\DATA\MyDB.mdf',
 MOVE N'MyDB_log' TO N'L:\MSSQLsql\mssql\DATA\MyDB_log.ldf',
 NOUNLOAD, REPLACE, RECOVERY, 
 STATS = 5
;
GO

Hope it will work.

• RDFozz: Given that the problem reported is that the RESTORE doesn't seem to understand the format of the backup file, not sure how adding REPLACE and RECOVERY will make things work.