What happens when you restore system databases to a SQL Server with a different name?

Question

I'm currently looking at disaster recovery plans and restoring backups of master/msdb/model.

In theory if I restore master, msdb, model and the user databases onto a fresh server install (with the same service packs etc) then I will have an exact copy of the original server, with all the maintenance plans and jobs and everything.

But what happens if the new server name is different to the original? Does it just cope, or are there some extra steps needed in this case?

(The MSDN documentation is a bit brief in these areas)

I'm on SQL 2005.

edit: there's a couple of answers below that are helpful, but neither of them clearly answers the question as far as I can tell. My question was: after restoring master, msdb etc, (and assuming I'm not using any of that SQL encryption stuff) will everything work OK if the new server and SQL instance has a different name to the original? Or do I have to keep the same name? The accepted answer tick is still up for grabs!

Answer 1

Correct, everything should be identical and ready to go.

The server name change is described here: How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005

Edit, based on comment:

When you restore onto the BCP server, then you are effectively changing the server name that hosts the SQL Server instance. You do the sp_dropserver/sp_addserver bit to make everything line up (basically, you fix @SERVERNAME)

Edit, again:

To answer your question, SQL Server will just run. Except for any code that relies on @@SERVERNAME matching the physical server name.

@@SERVERNAME takes it's information from the master database. If you restore master, then the instance thinks it has the old name. So it's an effective rename.

However, any clients don't care: they use DNS/physical server name to find the server.

More info under @@SERVERNAME about this differs from SERVERPROPERTY that will pick up the physical server name.

Answer 2

If your database uses SQL encryption functions or linked servers (thier credentials are internally crypted), then you must ensure that either computer SID or AD domain remains same OR you need to have backup of "service master key" (SMK).

We have once stuck with such problem (after disaster of course :)). Although we didn't use encrupted data, we couldn't link to remote servers; only possibility was to drop old encrypted SMK and generate new. If we had encrypted data on that server, then they would be lost.

MSDN: BACKUP SERVICE MASTER KEY (Transact-SQL) (and other related articles)