Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite
1

The old database server is getting tired, and I'm upgrading it to fresh hardware. What I need is an exact copy of the old ~100 databases to the new computer.

I have read some about replication and mirroring, but I can't figure out what would be the "best" course of action. I believe many database administrators are used to this scenario and know how to do it, how to move all databases, roles, schemas, stored procedures, etc., and whatever else is needed.

I know I could use backup on each database, but I have hundreds of databases to copy. Is there a simpler way?

A step-by-step guide with short descriptions how to do it would be great. And maybe some links to deeper information about the topic if needed.

1: Start [SQL Server Management Studio][1]
2: Right-click Server Instance
3: .....
share|improve this question
1  
The correct answer is: Backup and restore. That is simple enough. And as you already have a backup (hopefully!) you can use that. – mailq Sep 5 '11 at 14:22
Yes mailq, We have backups of course. But the problem is time for us and take backup, move, restore +(configure logins, jobs etc) on over 100 Databases is not efficient. I am looking for a way to automate these steps. We cant be the first that want to move a SQL installation (everything like it is) from oldComputer to newComputer? Windows Server 2003 cant change it's C: size and therefor something like Norton Ghost would be useless :( – Niike2 Sep 12 '11 at 14:57
What? Who told you about that limit? With an external partitioning program like gparted this is the easiest thing in the world. – mailq Sep 12 '11 at 21:06
Even when the server with Windows Server 2003 SP2 is on a Virtual Server infrastructure? Host is VMware... IT Support says its impossible to change the C: drive size on this OS. And – Niike2 Sep 14 '11 at 13:01

2 Answers

up vote 2 down vote

You have two viable options:

  • Backup and restore.
  • Copy all the files and simply mount the databases again.

Note that both operation can be easily scripted so you can export a list of DBs from the first server and re-import it in the second one. However, you'll have to rebuild a number of element for the import to work perfectly: pretty much all DB logons will have to be rebuild.

share|improve this answer
1: Backup and Restore one by one is no option. Not with >100 DBs. How would these scripts look like if they are easy? Is there any program or available scripts for these actions? If logins needs to be rebuilt, can they also be automated with scripts? 'Backup - Copy backup to new server - Restore' or 'Copy files to new server - Mount' is almost same timeconsuming if done by hand!? Safer with Backup and Restore? – Niike2 Sep 12 '11 at 15:05
The SQL management studio doesn't do anything you cannot do using a command-line script. Check the BACKUP DATABASE and RESTORE DATABASE commands in SQL help. – Stephane Sep 13 '11 at 12:25
up vote 0 down vote

Get the new server up and running and configure anything that you need, like email before you start moving anything. Think of it like you would get the water and electricity going in a house before anyone moves in.

Use your existing backups and restore them to the new server. Leave msdb, model, tempdb and master alone. When you bring up the database on the new server, go to the old server and set the corresponding database to 'OFFLINE' or detach it. This prevents a surprising number of 'oops' problems. Leaving the old database files offlined or detached, but in place, gives you a possible way out if something unforeseen and catastrophic happens. (Failure of the new SAN, for example.)

Use SSMS to script out linked servers, operators, jobs and any other, similar server-level objects. Remember that SSMS will not script out a password in a linked server SQL login, so you will need to know what it is. If you can, use trusted security for logins.

Don't forget about DTS and SSIS packages.

If you have a lot of items to do, you might need to write a little scripting. If you are going from SQL 2000 to SQL 2005, it is a good idea to reindex everything once you get it onto the new server.

Use sp_help_rev_login to help recreate your logins on the new server. KB article describing sp_help_rev_login This will preserve passwords and SIDS for SQL Server users, if you are not using domain security.

Don't forget that you may have to update connection strings in any application that touches your databases. If you are going to "throw away" the old server, or reformat and use it for something else, talk to your network guy and ask him if he can recreate the old name as a "DNS C name", and have it point to your new server. This is a butt-saver when you have lots of connection strings (that might not be under your control, say in old Access databases on someone's desktop in some far-flung corner of the company) pointing to the old server.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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