Move all databases to new Server

Question

Hi All,

I have an SQL server which has a number of OS issues and the only way to resolve these is a OS rebuild.

Because of this I'm looking to build a new server and follow the below process to move the databases to that server:

- Build fresh OS

- Create the drives using same drive letters

- Installed the correct version of SQL server

- Detach database from old server and attach to new

- Restore system databases from old server to the new server to preserve logins and agent jobs

- Disconnect old server form the network

- Rename New server so that it has the same name as the old server

Can you please advise if there is a better method to doing this of if I need to complete any additional steps

Thanks

Answer 1

Hello,

If you will use detach/attach method, my suggestion is to copy the detached database files to the new SQL Server instance and then attach the copies of the database files to the new server.

Instead of detach/attach method you can also use backup/restore method.

After renaming the computer, you need to run a few statements as explained on the following article:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

When moving an entire SQL instance to another server, it's also important to remember to migrate not just the databases themselves, but also all the non-database-specific objects as well, such as jobs, logins, SSIS packages, instance configurations, DB Mail configurations and anything else that might be relevant/in use.

I would strongly recommend a nifty PowerShell tool called "dbatools" which has built-in functionality exactly for those purposes:

https://dbatools.io/getting-started/

It's completely free, too.

---

Eitan Blumin; SQL Server Consultant - Madeira Data Solutions;

Answer 3

Backup and restore is best process to move the databases from one server to another

Once the backups are successfully moved., bring databases offline on old server (or) sql server on old server

As you are restoring master and msbd from old server., the sql server configurations also carry forwarded.

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)