A discussion on LinkedIn led to this hypothetical “real world” question:
Problem statement: I have a SQL Server 2000 database backup that I need to restore to a supported version of SQL Server (preferably SQL Server 2022). How do I do that?
If this was my problem to solve, I would:
- Install SQL Server 2008 on a virtual machine
- Restore the database
- Run the expensive version of
DBCC CHECKDB(WITH DATA_PURITY) - Upgrade the compatibility level to
100 - Rebuild all the indexes
- Back up the database
- Restore the 2008 backup to SQL Server 2022
- Run
DBCC CHECKDBon the restored database
If there were many databases, I would script it out, and might even use dbatools.
This is a question that invites comments, so go ahead and comment. I may take time to approve them, though. (Yes, comments are back!)