SQL 2008 DB -> Newer SQL Versions

[mdrabble]

I have a box running SQL 2008 and I want to move the Databases to another box running a new version of SQL.

 

Should it be a simple case of backing up the DB and then restoring it onto the new box, or would it be better to detach, move to new box and then reattach?

 

Cheers

[admars]

i find backup then restore better for moving databases

[PlantHead]

i find backup then restore better for moving databases

 

This - it is pretty simple.

You will also need to move the security logins - you move the DB with the logins in the DB but they won't correspond to SQL Logins, so you just need to recreate them.

 

Once you have copied the DB across you will need to change the compatability to whatever version of SQL you are running

Something like this

[color=#0000FF]USE[/color][color=#000000] [/color][color=gray][[/color][color=#000000]master[/color][color=gray]]
[/color][color=#0000FF]ALTER[/color][color=#000000] [/color][color=#0000FF]DATABASE[/color][color=#000000] [/color][color=gray][[/color][color=#000000]test[/color][color=gray]][/color][color=#000000] [/color][color=#0000FF]SET[/color][color=#000000] COMPATIBILITY_LEVEL [/color][color=gray]=[/color][color=#000000] [/color][color=#000000]130[/color]

 

See the guided workflow here if you are going to 2016 or later.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-2017

 

 

I would test the upgrade first to make sure you won't break anything in the DB once you upgrade it to the new version of SQL