SQL Server: How to move user database files?
Today we had a situation where one of the drives of a box that hosts the database files of a SQL Server 2008 instance hardly had any more room to grow. Our options we had to handle this issue were a) adding more disk space b) move the database files to another drive that has a decent amount of free space. In this post I will show you how option b) can be implemented in a couple ways. Following are the some of the methods that can be employed for this purpose:
- SQL Server Management Studio.
- Back and Restore
There could be two scenarios for this situation.
- Moving the database files to a new drive, location with the same instance of SQL Server. We can use only options 1 or 2 for this scenario.
- Moving the database files to a new SQL Server instance or server. We can use any one of the above mentioned methods for this scenario.
Moving the files using Transact-SQL.
Take a backup of your database before performing this activity. The first step in this process is to detach the database by using the sp_detach_db stored procedure. Execute this stored procedure against the master database like in the code below:
USE master GO sp_detach_db 'Demo' GO
Now that the database has bee detached from the SQL Server instance, we can physically move the related .mdf and.ldf files to a new location. This step is followed by reattaching the files to the Sql server Instance like in the code below
USE master GO sp_attach_db 'Demo', 'D:\NEWFILELOCATION\Demo.mdf', 'D:\NEWFILELOCATION\Demo.ldf' GO
Moving files using SQL Server Management Studio 2008
1. Right click on the database, go to Tasks and click on Detach in the context menu.
2. Copy the file to a new location.
3. Right click on the instance of SQL Server. Click on Attach to see the “Attach Databases dialog box”.
4. Click the Add button related to the “Databases to attach” section and select the database files from the new location and click OK.
5. At this point you have successfully moved your database files to a new location.
1. MSDN Online