Can I restore a database backup from SQL Server 2005 to SQL Server 2017?

Question

Hi everyone!

Can I restore a database backup from SQL Server 2005 (details below) to a SQL Server 2017 Standard?

(Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2))

Thanks.

Answer 1

Hi @Doria ,

>Can I restore a database backup from SQL Server 2005 (details below) to a SQL Server 2017 Standard?

Yes, you can. But you need to check if you have used any Enterprise Features in your databases. We usually check it by querying sys.dm_db_persisted_sku_features (Transact-SQL). Unfortunately the DMV only supported from SQL 2008, you can try to use the T-SQL from this blog Identifying SQL Server 2005 Enterprise Edition features in use. If you are using any Enterprise Features, you would have to stop using it, take fresh backup and then restore. By the way, the restored fail error message will also point the enterprise features used.

In addition, if you restore a SQL Server 2005 database backup to a SQL Server 2017 instance, the database compatibility level for that restored database would be changed to 100.

According to MS document, databases restored from any earlier version of SQL Server, the database retains its existing compatibility level, if it is at least minimum allowed for that instance of SQL Server. Moving a database with a compatibility level lower than the allowed level by the Database Engine, automatically sets the database to the lowest compatibility level allowed.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Yes, it's possible & supported, see SQL Server 2017 (14.x) Support for SQL Server 2005 (9.x)

Microsoft SQL Server 2005 - 9.00.1399.06

That's RTM version, very bad; better install first SP 4 and the latest CU before you try to migrate the database; see https://sqlserverbuilds.blogspot.com/#sql2005

Answer 3

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft

You have 32 bit version of SQL Server 2005 and SQL Server 2017 only has 64 bit version but good thing is you can restore 32 bit version on 64 bit server. The point here is you cannot do direct in-place upgrade to SQL 2017 you need to build new SQL Server 2017 server and restore user database backup. Script out the logins, permission and jobs and then move it to new server.

Answer 4

Thanks for all answers.

Regards.