0

I backed up a 2008 R2 database and copied to a server with a 2016 SQL Server. I tried using the restore database dialog but it does not seem to want to work. When I use the dialog I have the following:

Restore database dialog

The drop down for Database is empty. Also when I grabbed that sibk file the open dialog was looking for *.bak, *.tm and *.log and the backup created in 2008 is none of those. So I chose All Files. Not sure that's a clue to the problem.

  • try restoring using T-SQL and also refer here spaghettidba.com/2016/02/23/… which could be the misleading space case in your scenario as well – BeginnerDBA Nov 9 '17 at 22:48
  • What is T-SQL sorry? I am not a database guy. – Richard Nov 9 '17 at 22:51
  • T-SQL is SQL Server's version of SQL (Structured Query Language). Whatever you do in the GUI, SQL Server Management Studio uses to generated the actual T-SQL code that's run to accomplish the task. By writing the T-SQL code directly, you can sometimes work around things the GUI won't let you do (especially if there's a bug in the GUI, as in the link in the comments above. – RDFozz Nov 9 '17 at 23:01
  • If you're 100% sure the sibk file is your SQL Server 2008 R2 database backup from the other server, then using "All Files" to select it (presumably because it doesn't have a file extension) should be fine. If it's not an actual backup file, then how you selected it won't matter - it won't work. Did you use the GUI on the SQL Server 2008R2 box to generate the backup file? Are you 100% sure this is that file? Does the file have an extension (and if so, what)? – RDFozz Nov 9 '17 at 23:07
  • It is the right file. I think now it might be a permissions issue. – Richard Nov 9 '17 at 23:18
2

The fact that he database drop-down is blank suggests to me that the backup file is invalid/corrupt in some way.

First, try and restore the backup file header to confirm that the file is a valid SQL backup. In Management Studio, connect to your SQL 2016 instance and open a new query window. Run the following:

RESTORE HEADERONLY FROM DISK = 'C:\Users\nasscribe\Documents\sibk\sibk'

If you receive an error from this query, either it is not a valid SQL backup file (possible that there was a write error during the backup process on your 2008 R2 instance, or you just grabbed the wrong file), or it is a permissions issue where the SQL Service account cannot access the file, and you'll see something similar to below:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Users\nasscribe\Documents\sibk\sibk". Operating system error 5: "5(Access is denied.)".

If you receive the above message, copy the backup file to somewhere like C:\Temp\ and retry.

If instead you see an error like this:

Msg 3241, Level 16, State 0, Line 3 The media family on device 'C:\Users\nasscribe\Documents\sibk\sibk' is incorrectly formed. SQL Server cannot process this media family.

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

Then the backup file is not a valid SQL backup. Provided you still have access to your old server, connect to it and re-run the backup:

BACKUP DATABASE TO DISK = '[Backup path here]\sibk.bak' WITH STATS=5

Once done copy it over to your 2016 server and run the restore again.

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.