Cool Tips‎ > ‎Database Setup‎ > ‎

Database Setup - 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


ċ
Relocate all database files (ALTER DATABASE version).sql
(4k)
Andy Hughes,
Jun 23, 2012, 1:12 AM
ċ
Relocate all database files (CREATE FOR ATTACH version).sql
(4k)
Andy Hughes,
Jun 23, 2012, 1:12 AM
Comments