Legacy SQL DB question - Windows 2003, SQL 2000 instance

Question

Hi all

I'm getting rid of a few Windows 2003 Servers littering this estate I've just started managing, and 100's of Windows 2008 servers.
On a specific Windows 2003 server I have around 15 Databases on SQL version 8 (SQL 2000).

The owner of the data tells me these DB's are for a legacy service that has been moved away from, but they want to keep the data for legal\compliance reasons. Now I could get into that with him, and ask why the data was not moved onto the replacement system (or if it indeed was), but to build up some options, could you help me figure out an approach:

1.) My thougth was to backup the 15 DB's locally, then copy that backup onto archive storage or into a Data Lake in Azure.

2.) However, considering these are SQL 2000 generated DB's......what are the options for accessing that data if it was ever required in say 5\10 years?

Should we actually be looking at a data migration out of these SQL 2000 DB's first, into a newer SQL DB, and then backup and archive.....or are there means to still restore a SQL 2000 DB backup file into newer versions of SQL like 2017.

Or.....worst case......if they really needed access to this data in fior example 10 years, could I just offer up that the SQL backups will be available, but they would need to engage the help of a SQL DBA to extract data in a raw and dirty format going forward.

Any guidance appreciated. We have SQL DBA's on hand from an external support vendor if I really needed to progress it that way first, and I can then tell the Data owner that he will have to cough up the cash to have them progress this for him.

Coop

Answer 1

Should we actually be looking at a data migration out of these SQL 2000 DB's first, into a newer SQL DB, and then backup and archive.....

Yes correct that would be correct approach.

or are there means to still restore a SQL 2000 DB backup file into newer versions of SQL like 2017.

No you cannot restore SQL Server 2000 DB backup directly no SQL Server 2017. You have to first upgrade to SQL Server 2008 r2/2008/2005 ( I would suggest SQL Server 2008 R2) and then move to SQL Server 2017.

As I see here if you need the databases data after every 5-10 years then you must upgrade the database to latest version SQL Server 2019 and then take backup on tape and store the tapes. You have to repeat above task to make sure data is moved on latest version and stored.

Don't worry about restoring database. It is not a cumbersome task. A simple google search will help you in restore if needed.

EDIT: From questions you posted later

If I were to take a backup of a Windows 200 Database, and then try to restore it to a Windows 2017 SQL instance, for example, then would that Database just not restore at all?

Yes you would not be able to restore. Moving data out from table is little tricky task as compared to restore.

I presume that is needed because in every one of those stages there are new Database schema additions\attributes added to the customer databases along the way? And without those additions at every stage, the later versions cannot mount the Databases......something like that?

There is no schema addition neither any attributes that i know. The restriction is put from MS side may be because of some internal challenge which I do not know

The point is you can turn it as a diff billable project to upgrade to 2019 but that is up to you and your business. The thing is later even of you have .bak file for SQL Server 2000 unless you have 200 DB engine you would not be able to read it. So put that in front of client, inform him about all the risk and let him take decision.

Answer 2

How large are the database?
If they are less then 10 GB each, then you can use the free Express Edition to host the database.
But it will take some migration efforts, because you can not go from 2000 to current 2019 version, you have to take several migration steps, e.g. 2000 => 2008R2 => 2014 => 2019.

Answer 3

Depending on the size of the data, I would suggest exporting to the data to flat files and just storing the flat files. If you need the data, import the data into a supported database. Otherwise, you will continue to have the problem of the data existing on an unsupported version.

Answer 4

Hi @Cooper ,

SQL server 2000 is out of support. As others mentioned, you can upgrade SQL 2000 to SQL server 2008R2,then apply SP3 for SQL server 20018 R2, upgrade SQL server 2008 R2 SP3 to SQL server 2017. Before upgrade SQL to SQL 2008 R2, suggest you run the Upgrade Advisor on SQL Server 2000. Please refer How to Upgrade to SQL Server 2008 from SQL Server 2000. You can get the SQL server setup file from this link SQL Server Versions and Build Numbers.

If you want to keep data for 10 years, suggest you upgrade SQL 2017 to SQL 2019.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Wonderful response, thank you all. I'd like to follow up a little if I may.

Am I right in the following then:

1. If I were to take a backup of a Windows 200 Database, and then try to restore it to a Windows 2017 SQL instance, for example, then would that Database just not restore at all?
2. The reason I ask the above, is that I don't believe I will need to maintain elegant access to the data in a number of years. If a DBA can Use a tool like Visual studio to export tables from an old SQL 2000 backup, and query only certain information, then as a solution that would work.
   3. These databases in question, the 15 or so SQL 2000 ones, don't appear to have been accessed for years, so ideally I don't want to have to go through the multiple Database upgrade steps 2000-2008-2017-2019 if it's not absolutely crucial. In fact if that were the case, If the customer requested it, I'd tell him this needed to be turned into a costed project bcos of the work, and I suspect he will say no.

What I'd like to offer him is that I can backup the databases into a SQL 2000 .bak format, but only if there was some method of querying that data in an emergency, if a low level Database query tool could be used by our DBA's for example. I clearly would say best efforts, and if that wasn't good enough then he can go down the project cost route.

And one final thing....the upgrade path that is required, 2000-2008-2016 etc....I presume that is needed because in every one of those stages there are new Database schema additions\attributes added to the customer databases along the way? And without those additions at every stage, the later versions cannot mount the Databases......something like that?