Mark Minasi's Tech Forum
Register Calendar Latest Topics
 
 
 


Reply
  Author   Comment  
7thSun

Still Checking the Forum Out
Registered:
Posts: 2
Reply with quote  #1 
Hi Everyone!

I have a small SQL 2005 Express application I want to migrate to SQL 2017.
I need to keep the name of the SQL server the same, so I will not be able to have the old SQL Server running while I setup a new VM.

Can anyone give me some pointers on the best way to migrate to the new system?

Someone said I can simply copy databases with backup and restore, but that seems too simplistic. 



Thank you!

0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 288
Reply with quote  #2 
Well, as far as moving databases is concerned it is as simple as that. BUT.

1. you might want to look at the database structure, stored procedures and so on becase you cannot have 2005 compatibility mode on 2017. On the other hand, if the databases aren't using anything fancy it might just work in 2017 mode.
2. you will definitely have to map permissions. If you are using AD users it will probably be sufficient to recreate them on the new instance. SQL users you will need to copy after you restore the databases. Here's how: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server Since you will be doing a very long jump, version-wise, you need to read the entire article very carefully because there might be caveats. Build a test VM with SQL 2017 and test the migration before moving production DBs.

__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
7thSun

Still Checking the Forum Out
Registered:
Posts: 2
Reply with quote  #3 
Hi cj_berlin, thank you for the reply.

Would you recommend that I run the Microsoft Data Migration assistant on the original 2005 SQL Server for this type of job?  That should tell me if the Databases are doing anything fancy?

Thank you!

G
0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 288
Reply with quote  #4 
Absolutely. I wasn't sure if 2005 is still supported, but apparently it is.
__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.