How to move all databases to another server, preserving the server name

What if you need to replace a server. And you have to preserve the server name. Then follow these steps.

Before you start

  • Have a new server available, with a temporary name.
  • Have the SQL installation files available together with all Service Packs & Hotfixes you have installed on the original server.
  • On the original server run the following query, save the output on the new server (at least NOT on the old server)

select name, recovery_model_desc, is_trustworthy_on, is_broker_enabled from sys.databases order by name

On the original server

  • Freeze all activity. You can use my “Block Remote Users” trigger for that.
  • Stop SQL Agent service (to prevent jobs to start)
  • Create backups of all databases excluding tempdb & model, save them somewhere but NOT on the original server.
  • Stop & Disable the SQL Services.
  • Shutdown the server.

On the new server

  • Rename the machine so it has the name of the original server
  • Install SQL server, Service Packs and Hotfixes
  • Verify the IP gets resolved correctly in your domain.
  • Stop the SQL Service
  • Open CMD and start SQL Server in single user mode (in the SQL Server Binn folder: sqlserv –c –m –f)
  • Open another CMD and connect to SQL (SQLCMD –E), and restore the master database as below:

Restore the master database
restore database master from disk = ‘r:\master.bak’ with replace, stats=1

go

  • Open CMD and start SQL Server in single user mode (in the SQL Server Binn folder: sqlserv –c –m –f –T3608)
  • Open another CMD and connect to SQL (SQLCMD –E)
  • Change the locations of the system databases if they differ from the original server. And shutdown the service

alter database mssqlsystemresource modify file (name=’data’, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf’)

go

alter database mssqlsystemresource modify file (name=’log’, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf’)

go

alter database model modify file (name=’modeldev’, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf’)

go

alter database model modify file (name=’modellog’, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf’)

go

alter database msdb modify file (name=’msdbdata’, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf’)

go

alter database msdb modify file (name=’msdblog’, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf’)

go

alter database tempdb modify file (name=’tempdev’, filename = S:\SQLDATA\tempdb.mdf’)

go

alter database tempdb modify file (name=’templog’, filename = ‘T:\SQLLOG\templog.ldf’)

go

shutdown

go

  • Start SQL Server Service (leave SQL Agent Service stopped, disabled)
  • Restore msdb database
  • Tweak memory settings
  • Restore all other databases. For replicated databases use the KEEP_REPLICATION parameter.
  • On databases using Service Broker  (see previous query output),, activate Service Broker.
ALTER DATABASE MyDB SET ENABLE_BROKER
  • On databases with trusthworty ON (see previous query output), set it ON:
ALTER DATABASE MyDB SET TRUSTWORTHY ON
  • Enable & Start the SQL Agent Service
  • Reactivate activity (if you used my “Block Remote Users” trigger, drop it.
  • Verify your backup & maintenance jobs!
Advertisements

About sqlsmurf
MS SQL Administrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: