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:

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.

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.