Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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:
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.
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.
sibkfile 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