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.

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.