patternsqlMinor
Restore SQL database to newer version
Viewed 0 times
sqlversionnewerdatabaserestore
Problem
I'm using Drupal 7 on Windows and its using SQL Server 2008 RS2 (SP2).
Now, I have to move my site and new server using SQL Server 2016.
How can I restore my database to new server? Is it possible?
Thanks.
Now, I have to move my site and new server using SQL Server 2016.
How can I restore my database to new server? Is it possible?
Thanks.
Solution
Backup the database on 2008R2, then restore it on the new server. You can change the compatibly level to the new version.
The last thing to do is to add the logins from the old server.
You can't jump from SQL Server 2000 SP4 straight to SQL Server 2014. You are going to have to do this in two steps, restore the SQL 2000 database to a SQL2008R2 version, and change the compatibility level to 100, then back that up, and restore it to your new 2014 server.
See ALTER DATABASE (Transact-SQL) Compatibility Level in the documentation.
The last thing to do is to add the logins from the old server.
You can't jump from SQL Server 2000 SP4 straight to SQL Server 2014. You are going to have to do this in two steps, restore the SQL 2000 database to a SQL2008R2 version, and change the compatibility level to 100, then back that up, and restore it to your new 2014 server.
See ALTER DATABASE (Transact-SQL) Compatibility Level in the documentation.
Context
StackExchange Database Administrators Q#181919, answer score: 3
Revisions (0)
No revisions yet.