0

We have a situation in which we need to transfer all sets of SQL Server from Server A (Windows Server 2012) to Server B (Windows Server 2016) and both servers are installed with SQL Server 2015.

I read some posts that mentioned Replication. However, from what I understand there, replication is running continuously till the future while my situation is only a one-time migration. Hence, it sounds extreme in my case.

Then I read about Backup. However, we have multiple databases while the backup is only for a single database and does not include all the other configurations. Other than that, I read that we cannot restore system database backups to a different build of SQL Server

Hence, what is the best practice?

2
  • Do you need to clone system databases for some reason? You generally shouldn't be doing development on those. Maybe expand on what your end goal is. Is your goal to have a reporting server? Readonly replica? Fail over instance? Different solutions are going to depend on the nature of the details.
    – Xedni
    Commented Dec 19, 2023 at 5:01
  • 2
    There's no sql server 2015, so a good start would be to figure out the real version. You can run SELECT @@VERSION on both servers for that. What do you mean with 'other configuration'? I would probably use backups to transfer the databases, as long as you're on same major version it shouldn't be a problem. But it sounds like you might need to hire a DBA Commented Dec 19, 2023 at 6:56

1 Answer 1

0

The systems you can use to migrate your data, depend a lot on how much downtime you have. Mirroring/Replication/Log shipping/(Distributed) Availability groups are all systems you can use to have less downtime when migrating databases over.

Backup/Restore is the easiest method with the least amount of prep, however, it is also the one with the most downtime. The downtime will depend a lot on how fast you can backup and restore the databases, so storage/database size are very important factors.

As for the system databases, the most common procedure is to just copy over all of the data you need from the system databases. Examples:

  • Logins
  • Linked Servers
  • Proxies
  • Credentials
  • Jobs

Depending on how often this changes in your environment, these are things you can already prepare in advance.

I think the most important piece of advice anyone can give you is:

Do a test migration

Prepare everything you're going to do + script out everything. Then do a migration to a new SQL, or to the one you've set up now just to see if everything works.

In an ideal scenario you've got a test environment that you can migrate to a new test environment, if this is not the case, consider setting up a SQL Test environment now.

Scripting can be done in TSQL, however, if you're familiar with powershell dbatools can make things a lot easier for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.