BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Moving SQL Server Database Files

Over time the requirements of a SQL Server system can change. Sometimes this will necessitate the relocation of databases from one disk or server to another. This can be easily achieved using the standard SQL functions to detach and attach databases.

Determining Which Files to Move

If you are unsure of the files that are used by a database then you can use a built-in SQL Server stored procedure to generate a file list. This will include all of the data and log files for the database, these being the files that you want to relocate. To determine the file list, execute the following query within SQL Analyser, SQL Server Management Studio or another query tool, ensuring that you are linked to the correct database:

sp_helpfile

Detaching the Database

SQL Server database files are always in use whilst the service is running. To detach the database from the service, ensure that no users are connected to the database and then execute the following command. Replace the 'testdb' name with the name of the database to disconnect. After this step, you will be able to move the files to their new location using Windows Explorer or any another appropriate file manager tool.

sp_detach_db 'testdb'

NB: You must not be connected to the database whilst detaching it. Connect to another database before executing the command.

Reattaching the Database

Once the database files have been moved, the database can be reattached to SQL Server. To attach the database, the sp_attach_db command is used. This command requires parameters. The first parameter names the database; the remaining parameters list the database and log files to attach. Once executed, the database is again available for use.

sp_attach_db 'testdb', 'e:\data\testdb.mdf', 'e:\data\testdblog.ldf'

Known Issue With Network-Attached Database Files

A known issue exists with attaching to database files that are stored on network-attached storage. This should be considered before attempting such a relocation, as should any other appropriate Microsoft information.

25 October 2007