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?

jrhoads23

SSC Enthusiast

Points: 191
More actions
May 15, 2020 at 1:45 pm

#3751998

We are upgrading our old SQL 2000 box to SQL 2017. I know you cannot restore a backup from 2000 directly to 2017, so we are first restoring it to a SQL 2008 box, backing it up from there and then restoring that to SQL 2017.
When we restore that to SQL 2017 will that be of the same quality and integrity as if we created a new SQL 2017 database from scratch and rebuilt it manually? My mind is stuck on the operating system world and if you installed Windows 95 on a computer, did an upgrade to Windows 98, then an update to 2000, then to Windows 7, then to Windows 8 and then to Windows 10 - there are going to be a lot of cob webs and dust bunnies (IT terms) in that O/S and it wouldn't be as lean and mean as if you just did a fresh install of Windows 10. Is the same true with restoring a SQL 2000 database to SQL 2008 and then to SQL 2017?
Our SQL 2000 database has a ton of fat it in. So we don't know if we should start fresh with a new 2017 database and build it up, or restore it from the 2000 backup and trim away everything we no longer need. If starting with a fresh 2017 database gives us a better foundation to work on, that would point us to that direction.

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

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

Jo Pattyn SSC-Dedicated Points: 32072 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: 734469 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: 90253 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.