Restoring a database backup from SQL 2000 to SQL 2017 (via SQL 2008) - not good?

Question

Post reply

Restoring a database backup from SQL 2000 to SQL 2017 (via SQL 2008) - not good?

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32571 More actions · Answer 1

You might want to verify the integrity of the sql 2000 backup with dbcc checkdb

Not sure when page verify Checksum was introduced (better than torn-page detection) for corruption detection

https://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/

Steve Jones - SSC Editor SSC Guru Points: 739542 More actions · Answer 2

there are some dbccs for checking data type changes. I forget which one, but it addresses a change after 2000. A little busy now, but I'll try to look later.

Otherwise, the restore is a copy of pages, which structurally haven't changed. You could move all objects easily with something like SQL Compare if you want. I have done a migration of data from 2000 upwards using BCP to simplify things for a company. We used these scripts:

https://www.sqlservercentral.com/scripts/bcp-in-a-table

https://www.sqlservercentral.com/scripts/bcp-out-a-table

Disclosure: I work for Redgate Software.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

You are restoring the database to a clean installation of SQL Server...and no, there will not be any cobwebs or dust bunnies in the database.

There will be - however - things you need to do...here are some of them, but not everything:

DBCC CHECKDB ... WITH DATA PURITY; -- need to perform this one time to update the database for data purity checks
Change database settings - page verify, compatibility level, other settings
Rebuild all indexes - and update all statistics

And - you will need to review all code for any deprecated features. There are some that will no longer work in 2017 - and you could be surprised. For example, you can no longer truncate the log file using BACKUP LOG ... WITH TRUNCATE_ONLY.

I cannot recall all of the items that needed to be done and I am sure I am missing several.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jrhoads23 SSC Enthusiast Points: 191 More actions · Answer 4

Thanks you all for the great feedback. I have some research to do if we want to use our 2000 backup. Is there a good resource I can go to which will let me know everything that should be done (page verify, compatibility level, ...)

simon-811452 SSC-Addicted Points: 435 More actions · Answer 5

One thing to remember is that, while the DB will be entirely in the proper format - DDL dust bunnies will have been vacuumed up! - I believe that the DB will be marked as 100 Compatibility Mode when it ends up on the SQL 2017 box. That will reduce the likelihood of issues (ie with deprecated features) but will leave it in an older format, in effect. That only matters if you want to do any more dev, I think, but I don't like DBs on old compatibility levels just because it's untidy (maybe my OCD is too strong...)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

If your 2000 db doesn't have too many tables and data have you considered simply scripting out the tables of 2000 and into 2017 and then select/insert data from 2000 db to the 2017 db?

I agree with previous posts though. Once you restore it into 2008 run the checkdb with data purity, change the page verify option, rebuild indexes or at bare minimum run the Update stats.