XML FeedsFor one of the databases that I have to manage we were running out of space, so we got a shiny new 10.9 TB sized drive.
I was asked to move some files used by one database to this new drive. I decided to write up a little blog post just in case you ever need to do this so that you don't backup and restore (with move) because there is another way.
First create this test database with 3 data files and 1 log file, the data files will be in the C:\DB_Files directory
- USE master
- GO
- CREATE DATABASE [TestMove] ON PRIMARY
- ( NAME = N'TestMove', FILENAME = N'C:\DB_Files\TestMove.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
- ( NAME = N'TestMove2', FILENAME = N'C:\DB_Files\TestMove2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
- ( NAME = N'TestMove3', FILENAME = N'C:\DB_Files\TestMove3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
- LOG ON
- ( NAME = N'TestMove_log',
- FILENAME = N'C:\MSSQL\DATA\TestMove_log.ldf' ,
- SIZE = 1024KB , FILEGROWTH = 10%)
- GO
Now just create a test table, insert one row and do a simple select.
- USE TestMove
- GO
- CREATE TABLE Test(id INT)
- GO
- INSERT Test VALUES(1)
- GO
- SELECT * FROM Test
Now instead of having the data files in the C:\DB_Files we want to move them to the D:\DB_Files directory. You can use ALTER DATABASE...MODIFY FILE to do that, you just specify the new file locations, just make sure that the directory exists.
The following code will change the location of the data files
- USE master;
- GO
- ALTER DATABASE TestMove
- MODIFY FILE
- (
- NAME = TestMove,
- FILENAME = N'D:\DB_Files\TestMove.mdf'
- );
- GO
- USE master;
- GO
- ALTER DATABASE TestMove
- MODIFY FILE
- (
- NAME = TestMove2,
- FILENAME = N'D:\DB_Files\TestMove2.ndf'
- );
- GO
- USE master;
- GO
- ALTER DATABASE TestMove
- MODIFY FILE
- (
- NAME = TestMove3,
- FILENAME = N'D:\DB_Files\TestMove3.ndf'
- );
- GO
You will see the following message
The file "TestMove" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "TestMove2" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "TestMove3" has been modified in the system catalog. The new path will be used the next time the database is started.
[EDIT]
Paul Randal mentioned that you don't have to shut down SQL Server, you can also just take the database OFFLINE, see here for more detail: http://www.sqlmag.com/blogs/SQLServerQuestionsAnswered/SQLServerQuestionsAnswered/tabid/1977/entryid/72328/A-Safe-Method-for-Moving-a-Database-to-a-New-Location.aspx
[/EDIT]
Now, the first thing you want to do is stop SQL Server or take the database offline. You can stop SQL Server in a variety of ways, if you want to use the command line (NET STOP), take a look here: Using the Command Line to manage SQL Server services. You can also use the SQL Server Configuration manager, services under Control Panel/Administrative Tools or you can use SSMS.
To take the database offline, you can run this
- ALTER DATABASE TestMove
- SET OFFLINE;
If you do not stop SQL Server or take the database offline, you won't be able to move the files and you will get a message like the one below
After SQL Server is stopped, move the files to the new location
In my case, here is where the files are currently C:\DB_Files\
And after the move, this is the location of the files now D:\DB_Files\
Start SQL Server again or make the database online again.
To set the database online, run this
- ALTER DATABASE TestMove
- SET ONLINE;
After SQL Server is up and running. run the following query.
- SELECT name, physical_name AS CurrentLocation, state_desc
- FROM sys.master_files
- WHERE database_id = DB_ID(N'TestMove');
You should see something like the following
| name | CurrentLocation | state_desc |
|---|---|---|
| TestMove | D:\DB_Files\TestMove.mdf | ONLINE |
| TestMove_log | C:\MSSQL\DATA\TestMove_log.ldf | ONLINE |
| TestMove2 | D:\DB_Files\TestMove2.ndf | ONLINE |
| TestMove3 | D:\DB_Files\TestMove3.ndf | ONLINE |
Finally, just run this simple query again to verify that you didn't corrupt anything
- USE TestMove
- GO
- SELECT * FROM Test
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Don't shut down SQL Server. You can just set the DB offline, copy the files, and set the DB online again. See post at 
Thanks Paul, I will add that link to this post 
I put together a post on moving databases to new locations which uses the option of setting the database offline http://www.englishtosql.com/english-to-sql-blog/2010/6/23/moving-sql-datafiles.html 
I'd love to see a walkthrough of moving indexes to new filegroups (and back?). Apparently this is the only way to get a table both fully reindexed and shrunk *at the same time*. 
Hi Denis,
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.