I.T. Discussion Community!
-Collapse +Expand
Search DBA Group:

-Collapse +Expand DBA Store

Prestwood eMagazine

February Edition
Subscribe now! It's Free!
Enter your email:

   ► KBSQL ServersMicrosoft SQ...MS SQL 2005   Print This     
  From the June 2015 Issue of Prestwood eMag
DBA MS SQL 2005:
Moving Databases with SQL Server 2005
Posted 14 years ago on 5/9/2006 and updated 2/26/2008
Take Away: SQL Server 2005's available management tools make moving databases from one machine to another a snap. This article describes the steps


Microsoft SQL Server databases are stored in two files: one with the extension .MDF, the other with the extension .LDF

In a typical installation, these files would be stored in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
(under SQL Server 2005)

In order to "install" an MS SQL Server DB you've created, you need to deposit those two files into the Data directory, and then use an attach command to make them "known" to the database server.  [Note, there is another method for duplicated and deploying databases among SQL Server instances where you use backup and restore commands, I am not covering that here]

So how do you get the files in the first place?  Well, assuming you have a running SQL Server installation, you can copy the database from that running copy.  If you've ever tried to do this directly, you've likely encountered an error: SQL Server keeps those files open pretty much full time, so you really can't get at them while it is accessing the database.  So your first step will be to detach the database from the running server, so you can either move or copy it to your deployment server.

DETACHING the database

First make sure "everybody's out of the pool" (make sure noone is accessing the database you need to detach and move/copy), then:

1) Launch Microsoft SQL Server Management Studio (for this article, I used MSSMS Express)
2) Login to the database server you want to copy/move a database from
3) In the left-hand tree, under the database server you're copying from, expand the Databases node, and then right-click on the Database to move (for this example, I'll use WARS)
4) Click Tasks > Detach...

You should see your database listed. 

Here, you may select to have the database Drop Connections (if you didn't get everyone "out", or if someone has since logged in to the database, this is a last-ditch mechanism you can use to forcefully accomplish the move -- though this is risky as it may leave your database in an incomplete state; hardly the best way to start off a fresh install of a new database).

You may select to Update Statistics

You may select to Keep the Full Text Catalogs (default to this is on)

You can observe the status of the database.  If all is clear and there are no locks on the database, this should read "Ready".

When everything's the way you want it, click [OK]

The database is now detached, and the .mdf and .ldf files can now be moved or copied to another database installation...

5) for our example, I might then copy wars.mdf and wars.ldf to another machine with SQL Server 2005, depositing them into the Data directory for the new instance [Note: normally, you'd probably want to do a "MOVE" here, since having two running copies of a database is a sure-fire way to get your data out of sync between the two copies -- you'd want to COPY the data if you're keeping a test or development copy separate from the production copy; and synchronization between them will never be an issue]

ATTACHING the database
1) in the management console, with the "new" database server connected (this must be a server where the "new" database is not already attached), Right-Click the Databases node, and Click Attach...
2) Click the [Add...] button
the Data directory for this database server will already be selected for you [Thank you Microsoft for a useful and logical feature]
3) Select the .mdf file you wish to attach (wars.mdf for this example), and Click [OK]
4) You can choose to change the name the database will be known as with the "Attach As" attribute.  This can be used to prevent a naming conflict on the database server between two like-named databases.  For this example, I changed the name to warsTCG.
Generally speaking, that is the only change you might make, so
5) Click [OK]

That's it!  you're up and running.  Use the management console to open your new database and make sure all is well.  You may need to change the database owner in order to get all features to work, since the owner is coded into the database, and the accounts on this new server may well not match those of the previous server.


Share a thought or comment...
Write a Comment...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.

Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1253A1
Enter key:
KB Post Contributed By Joshua Delahunty:
I am a software engineer with many years professional experience, using everything from 6809 assembly language to C, C++, Delphi, Paradox for Windows, and Borland JBuilder.
Visit Profile

 KB Article #100372 Counter
Since 4/2/2008

Follow PrestwoodBoards on: 

©1995-2020 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech