Terry : Moving SQL Server Database locations

This page last changed on Jan 12, 2012 by terry.

Moving SQL Server Database locations using Detach & Attach functions

Prerequisites
  • Make a current backup of all databases, especially the master database, from their current location.
  • You must have system administrator (sa) permissions.

  • You must know the name and the current location of all data files and log files for the database.

    Icon

    You can determine the name and the current location of all files that a database uses by using the sp_helpfile stored procedure:

    use <database_name>
go
sp_helpfile
go
  • You should have exclusive access to the database that you are moving. If you experience problems during the process, and if you cannot access a database that you have moved or if you cannot start SQL Server, examine the SQL Server error log and SQL Server Books Online for more information about the errors that you are experiencing.

Moving user databases (Detach and Attach can also be done via the GUI - SQL Server Management Studio)

The following example moves a database that is named alidb. This database contains one data file, alidb.mdf, and one log file, alidb_log.ldf. If the database that you are moving has more data files or log files, specify the files in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because the sp_detach_db procedure does not list the files.

Detach the database as follows:

Next, copy the data files and the log files from the current location (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to the new location (D:\SQLServer\Data).

Re-attach the database. Point to the files in the new location as follows:

use master
go
sp_attach_db 'alidb','D:\SQLServer\Data\alidb.mdf','D:\SQLServer\Data\alidb_log.ldf'
go

Verify the change in file locations by using the sp_helpfile stored procedure:

use alidb
go
sp_helpfile
go

The filename column values should reflect the new locations.

Note Microsoft Knowledge Base article 922804 describes an issue for SQL Server 2005 databases on a network-attached storage. For more information, click the following article number to view the article in the Microsoft Knowledge Base: 922804 FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database

Consider this issue. Additionally, consider the permissions that are applied to a database when it is detached in SQL Server 2005. For more information, see the "Detaching and Attaching a Database" section of the "Securing Data and Log Files" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn2.microsoft.com/en-us/library/ms189128.aspx

Reference

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server