2

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.

migrated from stackoverflow.com Jul 16 '15 at 19:32

This question came from our site for professional and enthusiast programmers.

  • 2
    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' – Alex K. Jul 16 '15 at 15:36
  • What is the compatibility level of that source database? SQL Server 2012 and newer do not support compatibility level 80 (SQL Server 2000) anymore .... – marc_s Jul 16 '15 at 15:40
  • 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. – Ali Razeghi Jul 16 '15 at 19:39
  • Hi @Alex, the output is: Cannot open backup device '\\Mylocation\DBRestores\MyBackupFile'. Operating system error 2(The system cannot find the file specified.). Thanks – user3583912 Jul 16 '15 at 20:53
  • Please show your backup command from the original server. – Shawn Melton Jul 17 '15 at 4:02
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.

0

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.

0

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.

  • 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. – RDFozz Sep 22 '17 at 15:12

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.