Moving Database File Location

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 21 Sep 2010

Description

As a contract DBA (and even in the early stages of a permanent role), one common task is to ensure that all Database servers conform to database setup best practices.

A common error that I have found is the inappropriate placement of database files.

Minor inconsistencies in file storage are common and relatively harmless, but major errors such as positioning database log files on the system disk alongside Windows page file can have a disastrous effect on performance.

In all cases, I like to fix these inconsistencies where possible, as it makes automation of most tasks a lot easier.

The script below takes parameters for new and current database file names and paths and will produce TSQL commands to detach and attach the databases. The necessary file copy process that takes place between these two commands is up to you.

Only databases that will change are actually part of the final scripting, so it is easy to use the script to change the file names of a subset of databases.

The script uses the CREATE DATABASE ... FOR ATTACH syntax

I have also included a modified version (in the file attachments only) that uses the ALTER DATABASE... MODIFY FILENAME | SET OFFLINE | SET ONLINE syntax

Code

SET NOCOUNT ON

CREATE TABLE #DataFiles

(

DBName SYSNAME,

FileType VARCHAR(255),

DBLogicalFileName SYSNAME,

CurrentFileName VARCHAR(255),

CurrentFilePath VARCHAR(255),

NewFileName VARCHAR(255),

NewFilePath VARCHAR(255)

)

DECLARE

@CurrentFileNamePart VARCHAR(255),

@NewFileNamePart VARCHAR(255),

@CurrentFilePathPart VARCHAR(255),

@NewFilePathPart VARCHAR(255),

@DBName VARCHAR(2000),

@Cmd VARCHAR(2000),

@DetachCommand VARCHAR(1000),

@AttachCommand VARCHAR(1000),

@AttachCommand_Files VARCHAR(1000)

SELECT

-- change these 2 pairs of variables to represent any change on file name or file path that is to be implemented

@CurrentFileNamePart = NULL,

@NewFileNamePart = NULL,

@CurrentFilePathPart = 'D:\Log',

@NewFilePathPart = 'D:\Logs',

@DetachCommand = '',

@AttachCommand = '',

@AttachCommand_Files = ''

-- get the list of databases to process and their file names

SELECT @DBName = MIN(Name)

FROM sys.databases (NOLOCK)

WHERE Name NOT IN ('master', 'model', 'tempdb', 'northwind', 'pubs', 'msdb')

WHILE @DBName IS NOT NULL

BEGIN

SELECT @Cmd = ''

SELECT @Cmd = 'USE ' + @DBName +

' SELECT ''' + @DBName +''' As DBName,'

+ 'Name AS DBLogicalFileName, '

+ 'Type_Desc AS FileType, '

+ 'REVERSE(SUBSTRING(REVERSE(Physical_Name), 1, CHARINDEX(''\'', REVERSE(Physical_Name)) - 1)) AS CurrentFileNamePart, '

+ 'SUBSTRING(Physical_Name, 1, CHARINDEX(REVERSE(SUBSTRING(REVERSE(Physical_Name), 1, CHARINDEX(''\'', REVERSE(Physical_Name)) - 1)), Physical_Name)-1) AS CurrentFilePathPart '

+ 'FROM sys.database_files'

INSERT #DataFiles

(

DBName,

DBLogicalFileName,

FileType,

CurrentFileName,

CurrentFilePath

)

EXEC (@Cmd)

SELECT @DBName = MIN(Name)

FROM sys.databases (NOLOCK)

WHERE Name NOT IN ('master', 'model', 'tempdb', 'northwind', 'pubs', 'msdb')

AND Name > @DBName

END

-- now perform the remapping

UPDATE #DataFiles

SET

NewFileName = CASE

WHEN @NewFileNamePart IS NULL THEN CurrentFileName

ELSE REPLACE(CurrentFileName, @CurrentFileNamePart, @NewFileNamePart)

END,

NewFilePath = CASE

WHEN @NewFilePathPart IS NULL THEN CurrentFilePath

ELSE REPLACE(CurrentFilePath, @CurrentFilePathPart, @NewFilePathPart)

END

-- now, finally , we can create the detach and attach commands

-- to move only the databases which have new file names or file paths

SELECT @DBName = MIN(DBName)

FROM #DataFiles (NOLOCK)

WHERE ((NewFileName <> CurrentFileName) OR (NewFilePath <> CurrentFilePath))

WHILE @DBName IS NOT NULL

BEGIN

SELECT

@DetachCommand = '',

@AttachCommand = '',

@AttachCommand_Files = ''

SELECT @DetachCommand = @DetachCommand + 'master.dbo.sp_detach_db @dbname = N''' + @DBName + ''' '

SELECT @AttachCommand = @AttachCommand

+ 'USE master '

+ 'CREATE DATABASE [' + @DBName + '] ON'

SELECT @AttachCommand_Files = @AttachCommand_Files

+ '( FILENAME = N''' + NewFilePath + NewFileName + ''' ),'

FROM #DataFiles

WHERE DBName = @DBName

ORDER BY FileType DESC

SELECT @AttachCommand_Files = SUBSTRING(@AttachCommand_Files, 1, DATALENGTH(@AttachCommand_Files) - 1)

-- I'm assuming that the db should be owned by sa. The attach will allocate the logged in user as owner

-- so an additional command is required

SELECT @AttachCommand = @AttachCommand + @AttachCommand_Files

+ 'FOR ATTACH '

+ ' EXEC [' + @DBName + '].dbo.sp_changedbowner @loginame=N''sa'''

SELECT @DetachCommand

SELECT @AttachCommand

SELECT @DBName = MIN(DBName)

FROM #DataFiles (NOLOCK)

WHERE DBName > @DBName

AND ((NewFileName <> CurrentFileName) OR (NewFilePath <> CurrentFilePath))

END

DROP TABLE #DataFiles

SET NOCOUNT OFF