There’s a persistent myth that you cannot upgrade a database to a version more that is more then two versions newer.
It’s not true.
You can backup+restore (safest way) or detach+attach (not a safe way) from SQL Server 2005 (or higher) to any other newer version. You just can’t do an in-place upgrade to the latest version from SQL Server 2005.
I didn’t know this until a few weeks ago, and it’s not common knowledge, hence this short blog post.
Here’s the proof, restoring a SQL Server 2005 database directly to SQL Server 2016, and I tried it successfully on SQL Server 2014 and SQL Server 2012 as well.
SELECT @@VERSION; GO RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\SalesDB2005.BAK'; GO
Version
--------------------------------------------------------------
Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64) <snip>
(1 row(s) affected)
<snip> DatabaseName DatabaseVersion DatabaseCreationDate <snip>
--------------------------------------------------------------------
SalesDB 611 2008-08-06 12:47:41.000
(1 row(s) affected)
RESTORE DATABASE [SalesDB] FROM DISK = N'C:\SQLskills\SalesDB2005.BAK' WITH MOVE N'SalesDBData' TO N'C:\SQLskills\SalesDBData.mdf', MOVE N'SalesDBLog' TO N'C:\SQLskills\SalesDBLog.ldf', REPLACE; GO
Processed 24480 pages for database 'SalesDB', file 'SalesDBData' on file 1. Processed 2 pages for database 'SalesDB', file 'SalesDBLog' on file 1. Converting database 'SalesDB' from version 611 to the current version 852. Database 'SalesDB' running the upgrade step from version 611 to version 621. Database 'SalesDB' running the upgrade step from version 621 to version 622. Database 'SalesDB' running the upgrade step from version 622 to version 625. Database 'SalesDB' running the upgrade step from version 625 to version 626. Database 'SalesDB' running the upgrade step from version 626 to version 627. Database 'SalesDB' running the upgrade step from version 627 to version 628. Database 'SalesDB' running the upgrade step from version 628 to version 629. <snip> Database 'SalesDB' running the upgrade step from version 845 to version 846. Database 'SalesDB' running the upgrade step from version 846 to version 847. Database 'SalesDB' running the upgrade step from version 847 to version 848. Database 'SalesDB' running the upgrade step from version 848 to version 849. Database 'SalesDB' running the upgrade step from version 849 to version 850. Database 'SalesDB' running the upgrade step from version 850 to version 851. Database 'SalesDB' running the upgrade step from version 851 to version 852. RESTORE DATABASE successfully processed 24482 pages in 0.142 seconds (1346.892 MB/sec).
Very cool! This is going to make upgrading some of our clients a lot easier.
Also, remember that upgrading is a one-way operation. You absolutely cannot take the database and attach or restore it to an older version of SQL Server.
Some Microsoft links around this:
- Are you upgrading from SQL Server 2005?
- Upgrade a Database Using Detach and Attach
- Choose a Database Engine Upgrade Method
- Plan and Test the Database Engine Upgrade Plan
- Complete the Database Engine Upgrade
Enjoy!
48 thoughts on “You can upgrade from any version 2005+ to any other version”
Just be aware that your compatibility level will be increased to the lowest level supported by the higher version.
Using backup restore from 05 > 12 worked for me but I ran into issues for databases that had full text catalogs. My options were to drop them or middleman the upgrade with an 08r2 instance. They would not backup, messages about incorrect metadata. A DMV that escapes me at the moment had a row to the folder FTC would sit in during 2005 time even though it got converted to a NDF during the upgrade. I was able to just drop them for my upgrade and I’m not sure how widespread a thing this is but wanted to note.
Just did a few 05 to 12 on the basis of the “max two releases” theory. I decided against the attach/detach method as it’s just as time consuming for smaller DBs as the restore. I noticed you say attach/detach is not as safe and that confirms my suspicions.
The reason detach/attach is not as safe is that you lose your backout option.
With backup/restore, if something goes wrong you can simply switch on the old server again, and if that fails you can restore the backup. That’s two backout options.
With detach/attach, the data file will be modified during the upgrade. If it fails, you cannot attach it to the old version anymore, so you have no backout mechanism. (You could of course make a file system copy of the .mdf and .ldf files before attaching, but then you probably invest more time and effort then with the backup/restore option)
I agree with that, Hugo. But if I’m detaching and copying to a new machine that consideration goes out the window. I still have my original file. In most cases I’m upgrading to a new version of windows anyway. So I have a parallel system for comparison before the final restore/cutover anyway. It’s a good time to find out if there’s any corroption in the DB after the restore to the new machine anyway.
Nope – I still wouldn’t detach because if there’s something wrong with the database that causes the attach to fail, now you’ve got a detached database on both servers that you can’t attach. Always better to backup/restore, or set the database offline, copy, attach, then at least it’s still attached on the source server if something goes wrong with the attach on the new server.
Ahh very good point.
For my personal preference I like the detach/copy/attach/. You have a clean cut off so that nothing sneaks in and you don’t have the worry of someone accidentally attaching to the old db (yes you can take it offline). I would never try to attach my ONLY copy of a database to a newer version. :-) With backup/restore on a large database you’re waiting the hours to backup and then the hours to copy (if you’re not backing up to the destination server) and the hours to restore. With detach/copy/attach you’re just waiting on copy time and bring online time (which sometimes can take a bit of time). Shrinking log files before detaching also helps speed up copy time.
While restore seems to work from very old versions, when they’re talking about “upgrading”, I’m guessing they’re talking about in-place upgrading of a server. I’m guessing I could restore a SQL Server 7 backup on SQL Server 2016 but I doubt it would do an in place upgrade. In fact, both the 2016 doco say they only does upgrades from 2008+, and that upgrades from 2005 are not supported. It spells it out in detail further down this page: https://technet.microsoft.com/en-us/library/ms143393(v=sql.130).aspx. Attach/restore, etc. are fine. Upgrading is not.
Yup – I say in the blog post above that you can’t do an in-place upgrade and only attach/detach and backup/restore are supported from 2005 to 2016.
I’m just guessing they are referring to “in place upgrades” when they say “upgrading”, so that could confuse someone.
Yup – I say in the blog post above that you can’t do an in-place upgrade. It specifically calls that out in the doc links I listed.
BOL does actually state in the small print that this is the case
Great post. Firstly thank you for the information, but I was wondering if when you upgrade with backup/restore of one version to a more than two versions newer if it will be supported for Microsoft later in an eventual incident.
I have no idea what their future plans are, but I’d be very surprised if they decided to enforce the old two-versions rule.
Thank you for the feedback Paul.
A slightly off-topic comment but I noticed the speed of the restore:
RESTORE DATABASE successfully processed 24482 pages in 0.142 seconds (1346.892 MB/sec).
What kind of hardware are you using, just out of interest?
One of our Dell R720s with a 2.4TB Fusion-io ioDrive2 Duo (screaming fast!)
and this does not apply to SQL Server 2000:
Msg 3169, Level 16, State 1, Line 3
The database was backed up on a server running version 8.00.2039. That version is incompatible with this server, which is running version 13.00.1601. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Yup – that’s why I said 2005+…
For SQL 2000, you must restore it on either 2005, 2008 or 2008R2, create a new backup there and then continue to any other newer version.
Hi Paul,
Great Post, I did what you mentioned and successfully restore/upgrade SQL 2005 to SQL 2016 using backup restore method. For compatibility mode, I run SQL Statement “ALTER DATABASE [DB1] SET COMPATIBILITY_LEVEL = 130” which successfully applied. However, when I checked DB Properties using GUI (I mean right click on DB and property – option), compatibility=130 has not been shown in property window. Is this normal or Am I missing something? or because it has been restored directly from SQL 2005 to 2016, DB compatibility won’t be shown on property window.
Please note that after restoring backup of SQL 2005 to SQL 2016, there is no DB compatibility option appear under compatibility drop down box.
Regards,
Nizar
Sorry Paul, my bad. I was connecting to SQL 2016 server using SSMS 2012. That’s the reason compatibility was not showing on restored db of 2016. Thanks.
Based off this post (thank you BTW, saved me a test) Is it safe to assume that log-shipping from 2008R2 up to 2016 *should* work?
Yes (and you’re welcome!)
I love this post Paul, and it was definitely something I was wondering.
I expect that this doesn’t apply to the system databases tho! (However it’d be interesting to experiment with if I can find my SQL 2005 media)
With backup/restore, you can use a FULL backup earlier with DIFF right before the move. That is, you can get the bulk of the moves done ahead of time.
At final cutover, one last DIFF (per db of course), is all that you have to copy and restore before the final system start up.
My company just moved *all* our dbs, many TBs, to a new site and servers. The final moves, including transfer time over the wire, was less than 5 hours, because of careful timing of the last fulls and final diffs.
Attach / detach takes longer also because you have to run db checks very close to cutover time, to be sure that it’s really safe to detach the db. And you *must* keep 2 copies of the file, because a failed attach for any reason could modify the file being attached and force you to go back to an original copy of the file for another try.
Absolutely. Glad it went well. And I’ve never recommend detach/attach, as there’s no guarantee the file will attach (anywhere) again. Always better to leave the database attached and operational on the old system.
Thank-you sir.
Here I have query when we can restore from 2005 to 2017 then why can’t we setup log shipping.
Because it’s not supported.
If you initialise the secondary on the 2017 instance first, you can then log ship from 2005.
Hi Paul,
Thank you for the post. I have one question though: I have a database on a SQL 2014 SP1 server and I am migrating to a SQL 2016 SP2 server. I do it using backup and restore. However, after I restore it on 2016 and run a DBCC checkdb or DBCC checkalloc, it yields corruption on 2 PFS pages. The output of DBCC looks like this – Database error: Page (1:860785) is marked with the wrong type in PFS page (1:857328). PFS status 0x40 expected 0x60. If I restore the same backup on a 2014 server and do a checkdb, I cannot see any corruption. Nor do the weekly integrity checks on the live database output any corruption. I have restored multiple backups of the same DB on multiple SQL 2016 servers, all with the same result. This issue only occurs on one specific database that is running on Compatibility Level 2008 and is enabled for CDC. The specific pages to which the PFS point to are of a table that is partitioned. I have done multiple tests to try and find the reason for corruption at restore, and so far I have not found anything. Any suggestions? Can it be a bug in the upgrade mechanism at restore?
Thank you!
There is an issue somewhere that’s causing this as I’ve seen many people report this. Checking with Microsoft…
Thank you! That would be of great help. So far I have not found any way that doesn’t end up in corruption after the restore. I do have one other question: after the DBCC runs on 2016 and outputs corruption for the two PFS pages, if I run a DBCC repair with allow data loss, it fixes it in two cycles. I have read that allow data loss for PFS corruption is the one exception to the Allow_data_loss option that does not have data loss. Is that always the case?
Yes, it’s a known issue (I’ll blog about it next week). And yes, that’s a safe repair to do.
Do you have perhaps a reference about this issue? I have started a support case with Microsoft and currently there is no resolution to this, as they could not pinpoint the problem so far. If you can help me with more information, it would help a great deal.
Looking forward to your blog post! Thanks again.
There’s no reference – I’ll blog about it on Tuesday when I’m not teaching.
How to upgrade the mdf file of older version of database to new version of database without attaching and i did not have .BAK file, i have only MDF File and LDF file,
I have sql server 2014 version but mdf file is of lower version, so please let me know how to upgrade it?
Let me try to make sense of what you’re saying… what version of SQL Server is the database you have? And what version are you trying to upgrade to – 2014?
Hi thanks for the information as this has become useful, I have a quick question as I am new to server upgrades. We are in need of upgrading however the latest version we have is 2005. We have decided to go to 2016 so as I understand it and upgrade to 2016 is different to backup 2005 and restore 2016. After looking at the compatibility level it has changed from 90 to 100 after a backup restore option. Can someone explain or point me in the direction as to what the difference is between 130 and 100. After all this is a production db that has been fine on the older version and suddenly needing to have fancy new ways of doing things within 2016 is probably not needed.
See https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017
Paul – Microsoft says:
Assess compatibility requirements. When planning for a SQL Server database upgrade, you’ll want to understand what features are deprecated, discontinued or changed in the new version. Being aware of these changes in advance helps you prevent performance problems and issues related to application availability.
Should we be concerned that we’re going to break our applications when moving from SQL 2005 to a higher version because some feature that exists within SQL 2005 does not exist within a newer version.
Thank you.
Yes, that’s exactly what that means. You should always do extensive testing on the new version before moving to it in production, especially around compatibility levels and the changed cardinality estimators from 2014 onward.
Can we direct upgarde from SQL Server 2005 to SQL Server 2017
I don’t see why not, but I haven’t tried it. Let me know when you do. Thanks
Great blog article Paul. Thanks to everyone for their questions and comments. This is a very succinct and thorough coverage of the issues. A great help to me.
Thanks Paul. It was very useful article.
We are planning to upgrade our Sql server 2000 and wondering which latest edition i can upgrade it?
It will be a side by side upgrade. I know that i have to upgrade first 2008R2 then next edition but little confused as It’s only support from 2008R2 to Sql 2014/2016 or i can upgrade it to Sql 2017?
Ex. Sql 2000 ==> Sql 2008R2 ==> which edition latest?
Is it better i should go Sql 2000 ==> Sql 2005 ==> which edition latest?
Thanks
You’ll need to check the documentation at https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?view=sql-server-ver15 – but it seems the higher you initially upgrade from 2000, the better.