Moving Database files

Detatching files
SQL Server files are server centric. In genereal the assumption is that they will not be moved, and in real life they are usually too big to move with any ease. But it is possible to move them by detatching them, copying them and then reattaching them on a different machine.

There are a minimum of two files and you must copy them both. There is a .mdb database file and a .ldf log file.

You must detatch them from the server to copy them.
To detach the files:
1. Make sure all windows connecting to the database you want to detatch are closed. There can be no active connections.
2.Right click on the database in the Object Explorer, select Tasks/ Detatch
3. Accept the options in the dialog box as they are and click ok.
The database files can now be copied.

The files are most likely located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data though this could vary depending on your installation.
Copy the files

Re-attatching the files
to reattach the files on the machine where you copied them
1. Right click on databases in the object explorer
2. Select attach files
3. In the dialog box browse until you find the files. It will only show the .mdb file
4. Click OK and Ok

Use the same process on the other machine. You can place the files in any folder on the root drive, but you cannot place them on a user specific location such as the desktop.

Generating an SQL Script
A second way to move or share a database is to generate an SQL script. To do this
Right click on the database and choose tasks. Choose Generate scripts. In the wizard make sure that everything you want scripted is selected. (the data itself is not selected by default.) Generate the script to a new Query window. Save the file. This file can be used to re create the database on any SQL Server.