Sql-server – Trying to restore 2008 backup to 2016

sql serversql-server-2008-r2sql-server-2016

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.

Best Answer

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.

Related Solutions

Sql-server – How to restore multiple backups

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Sql-server – Backup and restore database – occured system error 5

Sounds like a permissions error is being masked. You have to remember that the user that needs access to the folder where the backup resides (and where you restore the backup, if using WITH MOVE explicitly) is the SQL Server service account, not regular users.

If the folder listed in the error message does not represent the same instance where you're trying to restore, it's unlikely the service has access to the folder. Try moving the .bak file to the current instance's data or log folder, where SQL Server will certainly have adequate permissions.

Best Answer

Related Solutions

Sql-server – How to restore multiple backups

Sql-server – Backup and restore database – occured system error 5

Related Question