move sql server install to a new PC

When I get a new machine, I always re-install. If there are settings I want to keep, and there are lot of them, I copy registry settings or whatever that is needed.

When it comes to SQL Server, there is all reason to move to a later version of SQL Server, that is SQL 2019. Moving databases is easy with BACKUP/RESTORE. Logins and jobs etc that are in master or msdb is a little more work, but since this sounds like your developer environment, I would not expect a lot of those.

You can get the most recent of version of SSMS here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Hi @Colin Brazier ,

All the searches I do just seem to give me ideas on moving a database, not the install itself.

Method 1: Restore databases,I guess what you get from the cases in google is this one.
Reference this firstly: quickstart-backup-restore-database

1. Install one same version SQL Server and SSMS on your new PC;
2. BACKUP all the user databases in your old PC;
3. Restore them in your new PC.

Method2: migrate all database systems from one to another, no matter which method you use, you need also install one same version SQL Server in you new PC, and you need also have the latest backup files of your system dbs and user dbs.

Prepare background:
Suppose the new server name is sqlserverpc,and SQL Server is the default instance.The backup server has a different name from the original server, and the SQL Server installation path is different. (The installation path of the alternate server is C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL.)

• Because of a good backup strategy, we now have the latest master, msdb and model database backups, as well as all other user database backups.

• Now you need to restore the system database to restore all database system information (users, passwords, tasks, etc.). Only then can the user database be restored.

Then follow steps as next:

I. Confirm that the SQL Server version of the standby server is the same as the original server.

Because we want to restore the system database, we must ensure that the master and msdb databases we restore are consistent with the Resource database of the new machine. Otherwise, SQL Server will not work properly. The so-called consistent version means that the numbers returned by "SELECT @@version" must be exactly the same.

II. In the command line window of the standby server, use the command to start the SQL Server service in single-user mode.

net start MSSQLSERVER /m  

If the command is executed successfully, the following information should be returned:
The SQL Server (DR) service is starting...
The SQL Server (DR) service was started successfully.

III. In the command line window, use the command line tool sqlcmd to connect to SQL Server.

sqlcmd -E -S sqlserverpc  

If the connection is successfully established, the following information should be returned.

1>  

IV. First restore the master database.
(1) In the connection of sqlcmd, run the following recovery statement (assuming the backup file is c:\lab\master.bak).

restore database master from disk ='c:\lab\master.bak'  
go  

It should return information similar to the following:
Processed 360 pages for database'master', file'master' on file 1.
Processed 4 pages for database'master', file'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
The SQL Server service stops automatically.

(2) Because the path of other databases recorded in the restored master database is inconsistent with the current path, restarting SQL Server at this time will fail. Must use trace flag 3608 to start.

net start MSSQLSERVER /f /m /T3608  

If it is normal, the following information should be returned.
The SQL Server (DR) service is starting.
The SQL Server (DR) service was started successfully.

(3) Use sqlcmd to connect and modify the file path of other databases to the existing correct path (C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data).
In the command line window, use sqlcmd to connect again.

sqlcmd -E -S sqlserverpc  

Use the following statement to modify the file path of each system database.

alter database mssqlsystemresource modify file (name=data, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.mdf');  
go  

If it is normal, the following information should be returned.
The file "data" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database mssqlsystemresource modify file (name =log, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mssqlsystemresource.ldf');  
go  

If it is normal, the following information should be returned.
The file "log" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database msdb modify file (name =MSDBData, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf');  
go  

If it is normal, the following information should be returned.
The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database msdb modify file (name =MSDBLog, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf');   
go  

If it is normal, the following information should be returned.
The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database model modify file (name =modeldev, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf');  
go  

If it is normal, the following information should be returned.
The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database model modify file (name =modellog, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf');  
go  

If it is normal, the following information should be returned.
The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database tempdb modify file (name =tempdev, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf');  
go  

If it is normal, the following information should be returned.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database tempdb modify file (name =templog, filename='C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf');  
go  

If it is normal, the following information should be returned.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

After all modifications are completed, run the "exit" command to exit the sqlcmd connection.

(4) Shut down SQL Server.

net stop MSSQLSERVER  

If it is normal, the following information should be returned.
The SQL Server (DR) service is stopping.
The SQL Server (DR) service was stopped successfully.

(5) Start SQL Server in normal mode.

net start MSSQLSERVER  

At this time, SQL Server can start normally. But the system database it uses is the new server's own except for the master. We are going to replace them with the backup on the original server.

V. Restore the msdb database.

Before running the following command, first shut down the SQL Server Agent service. Then use the RESTORE command to restore the mdsb database and point it to the new file path.

restore database msdb from disk ='c:\lab\msdb.bak'  
with move'MSDBData' to'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf',  
move'MSDBLog' to'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf', replace  

If it is normal, the following information should be returned.
Processed 600 pages for database'msdb', file'MSDBData' on file 1.
Processed 7 pages for database'msdb', file'MSDBLog' on file 1.
RESTORE DATABASE successfully processed 607 pages in 0.841 seconds (5.907 MB/sec).

VI. Restore the model database.

restore database model from disk ='c:\lab\model.bak'  
with move'modeldev' to'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf',  
move'modellog' to'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf', replace  

If it is normal, the following information should be returned.
Processed 152 pages for database'model', file'modeldev' on file 1.
Processed 3 pages for database'model', file'modellog' on file 1.
RESTORE DATABASE successfully processed 155 pages in 0.174 seconds (7.273 MB/sec).

VII. Modify the server name.
(1) Run the following statement and you will find that the original server name is returned. This is because the master database comes from that machine.

SELECT @@servername  

(2) Run the following statement to modify the server name.

sp_dropserver'<original server name>'  
GO  
sp_addserver'sqlserverpc','local'  
GO  

(3) Restart the SQL Server service, and then run the following statement, you can see that the current server name is returned.

SELECT @@servername  
GO  

After completing these operations, all the original SQL Server configurations can be restored to the new server. It's just that the user databases are all suspicious, because there are no files for them on the new server. Then you can use the recovery method introduced in the previous article to recover the user databases in sequence.

For SSMS, you can copy the SSMS.exe from the old one to the new ont to reinstall or download the same version.(Actually, you can use the latest SSMS)
For SQL Server, you can also copy the installation package from the old one to the new or download the same version.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Thanks again everybody, it's a pity I cannot accept more than one answer.

I will install a fresh SQL Server 2014 on the new machine, and once that is up and running, try to upgrade to 2019.

Cheers,

Colin

Another great option is to use PowerShell dbatools module from PowerShell Gallery (https://dbatools.io).

There is a command called Start-DbaMigration where you can define the source and destination servers and if you want to do a detach/attach or backup/restore and it will migrate all the jobs, logins, credentials and all the stuff you would do by hand all for you.

Just thought I would add this to the mix of options to migrate a complete instance with little interaction or worry of not getting something.