次の方法で共有

Database internal file versions–Where is the Undo button for a database upgrade ?

  • [アーティクル]

 

A common question is how to move back to the older version of the database once upgraded. The answer is, you can´t.

By attaching the database to the new SQL Server version an upgrade is done internally which can be seen if you run the attach commands in e.g. the script window of SSMS. In the following sample, I am attaching a SQL Server 2005 database to a SQL Server 2008 instance.

The result is that it is upgraded during the attachment process (which also happens while restoring a backup) showing the internal file structure versions:

Converting database 'Test' from version 611 to the current version 655.
Database 'Test' running the upgrade step from version 611 to version 621.
Database 'Test' running the upgrade step from version 621 to version 622.
Database 'Test' running the upgrade step from version 622 to version 625.
Database 'Test' running the upgrade step from version 625 to version 626.
Database 'Test' running the upgrade step from version 626 to version 627.
Database 'Test' running the upgrade step from version 627 to version 628.
Database 'Test' running the upgrade step from version 628 to version 629.
Database 'Test' running the upgrade step from version 629 to version 630.
Database 'Test' running the upgrade step from version 630 to version 631.
Database 'Test' running the upgrade step from version 631 to version 632.
Database 'Test' running the upgrade step from version 632 to version 633.
Database 'Test' running the upgrade step from version 633 to version 634.
Database 'Test' running the upgrade step from version 634 to version 635.
Database 'Test' running the upgrade step from version 635 to version 636.
Database 'Test' running the upgrade step from version 636 to version 637.
Database 'Test' running the upgrade step from version 637 to version 638.
Database 'Test' running the upgrade step from version 638 to version 639.
Database 'Test' running the upgrade step from version 639 to version 640.
Database 'Test' running the upgrade step from version 640 to version 641.
Database 'Test' running the upgrade step from version 641 to version 642.
Database 'Test' running the upgrade step from version 642 to version 643.
Database 'Test' running the upgrade step from version 643 to version 644.
Database 'Test' running the upgrade step from version 644 to version 645.
Database 'Test' running the upgrade step from version 645 to version 646.
Database 'Test' running the upgrade step from version 646 to version 647.
Database 'Test' running the upgrade step from version 647 to version 648.
Database 'Test' running the upgrade step from version 648 to version 649.
Database 'Test' running the upgrade step from version 649 to version 650.
Database 'Test' running the upgrade step from version 650 to version 651.
Database 'Test' running the upgrade step from version 651 to version 652.
Database 'Test' running the upgrade step from version 652 to version 653.
Database 'Test' running the upgrade step from version 653 to version 654.
Database 'Test' running the upgrade step from version 654 to version 655.

You see that the database has undergone some structural changes moving from internal version 611 to 655. Next time you want to open the database with the SQL Server 2005 instance, you will receive something like “ 'Test' cannot be opened because it is version 655. This server supports version 611 and earlier. ”

This cannot be undone. So once you decide to move the database, be sure that you don´t want to come back to an earlier version.

Ok, you didn't read my blog earlier and you are now in the situation of having an upgraded database ?Well, several options:

  • Use the new SQL Server instance now as you new database version system
  • Use the SSIS / Import Export wizard to create the database from script on the old system (If you do not have a script from your old system, create a new script with the new SQL Server instance in SSMS, Right click the database in SSMS > Tasks > Generate Script. Make sure that you choose compatibility mode of the old database (Create Script for SQL Server version XXXX)
  • Use the database publishing wizard to transfer the data

Are backups compatible with earlier version of SQL Server ?

The common answer is no. If you do have a backup of your database and you are not sure which version this contains, you can read the backup headers by using the command:

RESTORE HEADERONLY FROM DISK=N'FILENAME' WITH NOUNLOAD

image

The DatabaseVersion attribute will reveal the information of the current status of the database.

Restoring a backup on an earlier version of the database will bring the following error message:

The media family on device <BackupFIleNameHere> is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

-Jens

Comments

  • Anonymous
    September 05, 2011
    Hi Jens, It is the 1st time i find an explanation why it is impossible to come back to an earlier version. Thanks. Papy Normand PS : sorry, i have done an error with my mouse which skidded, so the  final rating of this post  is exactly the contrary of the wanted rating.I hope that you will excuse me.

  • Anonymous
    September 05, 2011
    Hi Jens, My rating is good now

  • Anonymous
    September 06, 2011
    The comment has been removed