How to move SQL Server database files to different location

How to move SQL Server database files to different location

(Works for SQL 2005/2008)

In case you need to move database files (data or log) to new location one way to do it is to detach the database, move the files to location and attach it back

1)      Detach database: Detaching a database removes it from the instance of the Microsoft SQL Server Database Engine but leaves intact the database, with its data files and transaction log files. Following script can be used

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

GO

2)      Move files to new location: You can manually copy the files but I prefer to use XCOPY. Here is how

xcopy "C:\DATA\MyDatabase_Data.mdf" "D:\DATA\MyDatabase_Data.mdf" /P

3)      Attach the database: Attaching a database places it in exactly the same state that it was in when it was detached. Here is how

USE master;

GO

CREATE DATABASE MyAdventureWorks

    ON (FILENAME = 'D:\DATA\MyDatabase_Data.mdf')

    FOR ATTACH;

GO

4)    Final step is to verify that files are where you expect them to be

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'MyDatabase');

Adding and removing TempDB files

Add/Remove data file to avoid TempDB Running out of space

Today I ran into a situation where there was heavy use of TempDB which caused data files to grow beyond the capacity of the drive.

So as an immediate solution I decided to add one more file to TempDB on a separate drive where there was space available. As you know this is going to be a NDF file.

To avoid new drive running out of space this new file needs to be restricted in maximum growth.

Use following script to add the file

USE [master]

GO

ALTER DATABASE [tempdb]

ADD FILE ( NAME = N'tempdev01',

FILENAME = N'D:\DATA\tempdev01.ndf' ,

SIZE = 10240000KB ,

MAXSIZE = 76800000KB ,

FILEGROWTH = 102400KB )

GO

Now once you know use of TempDB has receded and it is safe to remove the NDF file following script should be used. This is done in 2 parts.

1)      File can only be removed if it is empty so first we empty the file

use tempdb

go

DBCC SHRINKFILE ('tempdev01' , EMPTYFILE) ;

go

2)      After the file is empty then file can be removed

USE Master

GO

alter database tempdb REMOVE file [tempdev01] ;

So in short it is possible to temporarily allocate disk space to TempDB to avoid stoppage.

PS: No need to restart SQL Service for removing TempDB files