In the last Express Essentials column, "Where the Data's At" ( http://www.sqlmag.com/articles/index.cfm?articleid=94776& ), you saw that SQL Server 2005 Express Edition actually stores its database data in two OS files. The first, the primary file, is the Data file, which ends in the extension .mdf and stores the database data. The second is the Log file, which ends with the extension .ldf and stores the database transactions that have taken place. The primary purpose of the Log file is to enable you to roll back changes that have occurred in the database--restoring it to a known state at some specified point in time.
Knowing where your data is located is an important step in knowing how to move your SQL Server Express databases between different systems. Although moving databases isn’t difficult, it's not quite as simple as just copying files from one system to another. Copying files is an essential part of the process of moving SQL Server Express databases between systems, but the other part is telling the SQL Server Express instance how to identify those files. You give the SQL Server Express instance that information by using the sp_attach and sp_dettach stored procedures. The sp_attach stored procedure tells SQL Server Express the location of the data files to use, and the sp_dettach stored procedure tells SQL Server Express to stop using a set of data files.
Now, I'll show you the steps and T-SQL commands you'll use to move a SQL Server Express database from one system to another:
1. Detach the database from the source system:
EXEC sp_detach_db 'MyDatabase'
2. Copy the Data and Log files to the target system and move them into the SQL Server\Data directory:
XCOPY MyDatabase.mdf \\server\share
XCOPY MyDatabase.ldf \\server\share
3. Attach the database to the target system:
EXEC sp_attach_db 'MyDatabase', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.LDF'
You can run the sp_attach and sp_detach stored procedure from SQL Server Management Studio Express (SSMSE). Run the XCOPY command from the command prompt.
