3

I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80. Initially I took a full database backup and tried to restore it to the new server, but got the following error:

Msg 9014, Level 16, State 2, Line 32 An error occurred while processing the log for database 'nrf'. The log block version 2 is unsupported. This server supports log version 3 to 5.

I went back to the original server and restored the backup to a new name, nrf_temp, and then altered that database to compatibility level 100 (for 2008 R2). I took a new backup and again tried to restore to my new server, but got the same error again. Then I went back to the original server, deleted my new name database, nrf_temp, and did a restore from that last backup that was taken after I put the compatibility level to 100 - thinking maybe a recreation of the physical files might help. I took another backup and again tried the restore to my new server, but took the same error again.

Is there any way to get the restore to work on the 2017 server or will I have to define the database completely new and import data? Note that I was able to successfully back up the other databases from that old server, that were already at compatibility 100, and restore those backups to my new 2017 build.

  • 1
    Maybe you can try to attach the DB in the new server, It may create a new log with the "new format", not sure if it will work but may worth the try. Just ensure you have a clean shutdown , copy JUST the datafiles and let SQL server create a new log. – dbamex Nov 12 at 22:19
  • Just a question to rule things out, you did backup your database to a new file right? (Not appended to an existing backup file) since the first backup would then be restored by default (the old compat 80 db). – Randi Vertongen Nov 13 at 9:03
  • I did back up to a completely new backup set & file for each backup that I took. I will try doing the attach method. Thanks. – Sheila Nov 13 at 14:30
  • @dbamex - Attaching the data file to the new server and letting it create the log file worked. Thanks much. – Sheila Nov 13 at 15:17
0

Make sure you've installed the minimum service pack level for 2008 on your source instance before making the backup. It appears to need to be on SP4 for it to be restorable to SQL Server 2017: Supported Version and Edition Upgrades for SQL Server 2017

  • The old server is 2008 R2 and the referenced page says it needs to be at SP3 for that version. The instance is showing that it is "Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34" - which indicates that I'm good there. – Sheila Nov 12 at 21:42

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.