Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Let me start by saying that I am not a DBA, but I am in a position where I am responsible for moving just under 100 MS-SQL 2008 DBs from our current development server, to a new/better/faster development server.

As this is just a local dev server, temporary downtime is acceptable, but I am looking for a way to move all of the databases (preferably in bulk).

I know that I could take a bak of each, and restore it on the new server, but given the volume of DBs, I am looking for a more efficient way.

I am not opposed to learning a new piece of software, writing code or any other requirement, so long as it speeds up the process.

share|improve this question
    
Is your new server the same version? And, is this a one-time shot? (sounds like yes) –  squillman Jun 6 '12 at 11:55
    
Yes, same server version and yes, one time only. –  jc4rp3nt3r Jun 6 '12 at 12:04

3 Answers 3

up vote 1 down vote accepted

1) The following script will generate T-SQL to create the databases once you've moved the files. It's important to do this step before anything else.

SELECT 'create database ' + QUOTENAME(DB_NAME(d.database_id)) + ' on ' + 
STUFF((
    SELECT ',(name = ''' + name + ''', filename = ''' + [physical_name] + ''')'
    FROM sys.[master_files] AS mf
    WHERE [mf].[database_id] = d.[database_id]
    FOR XML PATH('')
), 1, 1, '') + ' for attach'
FROM sys.[databases] AS d
WHERE d.name NOT IN ('master', 'model', 'tempdb', 'msdb')

If the path to the data files on the new server isn't the same as on the old server, you can edit the file names in the script (or do something fancier in the query to do it for you automatically).

2) The following code will generate a T-SQL script to set the database status to offline for all non-system databases. If your plan is to set the original server offline (which wouldn't be a bad idea), then this step is unnecessary as the database files will not be in use by the server.

select 'alter database ' + quotename(name) + ' set offline'
from sys.[databases] as d
WHERE d.name NOT IN ('master', 'model', 'tempdb', 'msdb')

3) Bring the databases offline either through shutting down the original server or executing the T-SQL from step 2 on the original server

4) Move the files to the new server

5) Execute the script from step 2 on the new server.

share|improve this answer
    
Thank you! I will give this a go. I appreciate your expertise. –  jc4rp3nt3r Jun 7 '12 at 16:27

Since you are going to restore to the same version of SQL Server, I did the following in my environment when this task came up:

1) Restore the master database backup from source to destination by keeping that server in master-only mode through T-3608 flag.

2) After you restore master, you should make sure that the new dev server has same structure of folders like old server. for example. if mdf files need to go to 'e:\data' make sure that you set this folder on the new server. Make sure to note down paths of each database in advance so that you can place mdf and ldf files on identical locations on the new server

3) Now, shut down the old dev server. Copy and paste mdf and ldf for all databases including model and msdb(tempdb will be recreated).

4) Once you paste the files on identical location on the new server,remove the tace flag and start sql service normally.

5) There will be chances of sql not able start, please check eventviewer for errors.

6) In case you find that one database is on a wrong location but sql expects it on some other location, you have to use trace flag again and alter the location of the file to point it to new directory.

If the above steps are easier for you to grasp, please go ahead and post any issues else use a backup and restore method(long but easy).

Thanks Chandan

share|improve this answer

If the database versions are the same, you can physically move the files after taking the databases offline; first collect all database names:

SELECT Name FROM sys.databases WHERE owner_sid <> 1

Then take them offline:

EXEC sp_msforeachdb 'ALTER DATABASE ? SET OFFLINE'

Now move the physical mdf and ldf files to the new server, and script the same set of databases for CREATE:

CREATE DATABASE $foo ON 
  (FILENAME = 'mdf_location'), 
  (FILENAME = 'log_location') FOR ATTACH

Note: do not do this for the master database, it will not work.
Take a normal backup and restore.

share|improve this answer
    
Gah! There's a lot missing from this solution. 1) You're assuming that all of his non-system databases are owned by someone other than sa. 2) The sp_msforeachdb ignores the output from step 1 and just takes all of the databases offline. –  Ben Thul Jun 6 '12 at 14:28
    
Gah! I never said it was a complete solution; he clearly indicated that he was prepared to script it. –  adaptr Jun 7 '12 at 8:56

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.