Move MSSQL Database File Location

MS SQL server default installed to your system C drive, as long as you have sufficient disk space you do no need to worry about the following, but in case you need to move the data to another location, you need to follow the below steps.

Let’s take the database "websitedb" for example, to move the data and log file to D:\MSSQL Data directory.

1. Run the following statement.

ALTER DATABASE websitedb SET OFFLINE;

2. Move the file or files to the new location (under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
3. For each file moved, run the following statement.

ALTER DATABASE websitedb MODIFY FILE ( NAME = websitedb_log, FILENAME = ‘D:\MSSQL Data\websitedb_log.ldf‘ );

–repeat the above to move the websitedb.mdf as well.

4. Run the following statement.

ALTER DATABASE websitedb SET ONLINE;

5. Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’<websitedb>’);


More details can be found at this page

http://msdn.microsoft.com/en-ca/library/ms345483.aspx

Share

- has written 121 posts.


Leave a Reply

You must be logged in to post a comment.