2

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.

I did back up to a completely new backup set & file for each backup that I took.

1
  • @dbamex - Attaching the data file to the new server and letting it create the log file worked. Thanks much.
    – Sheila
    Nov 13, 2019 at 15:17

2 Answers 2

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

1
  • 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, 2019 at 21:42
0

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.

Ensure you have a clean shutdown and copy JUST the datafiles and let SQL Server create a new log. – dbamex

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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