Skip to content

How to move database files from one drive to another or from one location to another in SQL Server?

February 28, 2013

There are times when due to database growth sometime the drives are not able to accommodate any more data growth or you may want to move one database files to another drive to fix performance issue or someone created database with default file locations and you want to transfer files to appropriate folders. It can easily be done in SQL Server. In this article I would cover some of the easy ways to do, considering that it’s not a database migration to another server, database is not part of replication or mirroring. Important Points :

  • Take appropriate downtime (database will not be available during movement). Time required depends on size of database files and how fast database files are getting copied from one location to another.
  • No active connections to database. Kill all the active connections to the database.

Method 1: Detach and Attach: Detach Database so that the files become independent, cut and paste the files from source to destination and attach again. This can be done from Management Studio via GUI or script. Before you proceed with database detach make sure you have physical location  and logical name of database files. You can do it either by [Sp_helpdb Database_name] or Right click database in management studio and select properties → Files.

Method 1 a) Using GUI:  Connect to instance in Management Studio and select database whose files you want to move → Right Click → Tasks → Detach (Note: if there are active connection you receive an error Please close the active connections before detaching the database. Refer Screenshot below). Kill any active connections and try again.

Movedatabasefiles1

Movedatabasefiles2

Once database is detached you will not be able to see it in Management studio so it’s important to have physical and logical names of database files handy. Now move the physical files from source location to destination by copy and pasting, once done delete the file from source location (I do not recommend cut and paste because if there is some issue while copying you still have the source file) . Once done go back to management studio and select databases → Right Click → Attach → Add (Add both Data and logfile from new locations) and click OK .

Movedatabasefiles3

Method 1 b) T-SQL Script to detach and attach database. Use below script to detach and attach database files:

-- Get physical and logical names of database files and keep it somewhere safely.
Sp_helpdb testdb -- Replace Testdb with you DB name.

USE [master]
go
-- Replace TestDB with database name you want to deatch
EXEC master.dbo.Sp_detach_db  @dbname = N'TestDB'
go

-- PHYSICALLY MOVE FILES FROM SOURCE AND DESTINATION AS DONE IN PREVIOUS STEP

-- Attach Database Script. Replace Testdb with your DB name and new physical locations of files.
Sp_attach_db 'testdb', N'C:\MSSQL\DATA\TestDB.mdf', N'C:\MSSQL\DATA\TestDB_log.ldf'
GO

Method 2: T-SQL script to take Database offline and move database files and then bring database online. This much is faster and convenient. Use below script.

-- Get physical and logical names of database files and keep it somewhere safely.
sp_helpdb TestDB -- Replace Testdb with you DB name.
GO

USE [master]
GO
-- Replace Testdb with you DB name.
ALTER DATABASE TestDB SET OFFLINE

-- PHYSICALLY MOVE FILES FROM SOURCE AND DESTINATION AS DONE IN STEP 1a ONCE DONE EXECUTE BELOW SCRIPT TO UPDATE SYSTEM
CATALOGS WITH NEW FILE LOCATIONS.

USE master
GO
-- Replace Testdb with you DB name and locations with new file locations.
ALTER DATABASE TESTDB MODIFY FILE (NAME = TestDB, FILENAME = N'C:\DATA\TestDB\TestDB.mdf')
GO
ALTER DATABASE TESTDB MODIFY FILE (NAME = TestDB_log, FILENAME = N'C:\DATA\TestDB\TestDB_log.ldf')
GO

USE [master]
GO ALTER DATABASE TestDB SET ONLINE
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: