SQL: Detach, Move, and re-Attach database

The Transact-SQL Script below, is useful if you want to standardise the location of your databases. I have been managing a server whereby databases created by other people were created all over the place!! Therefore, this script was written to make life so much easier. 🙂

Be sure to read through the script before running it.

You may need to allow xp_cmdshell access. Use the Surface Area Configuration tool to do this.

If you get an error, please refresh your list of databases in Management Studio. If you database has disappeared, it will just need to be re-attached.

Please also note, that I haven’t tested it in SQL Server 2000 where a full text catalogue exists.

As always 1, please ensure you test any script taken from my website on a test/development machine, before running on a production server.

If you want to produce a list of database files (either before or after), please see My related post – List of database files

Please leave any comments that you have 🙂

USE master
GO

DECLARE @DatabaseName NVARCHAR(100)
DECLARE @InstanceName NVARCHAR(100)
DECLARE @NewDBLocation NVARCHAR(100)
DECLARE @NewLogLocation NVARCHAR(100)
DECLARE @NewFTLocation NVARCHAR(100)
DECLARE @Cmd NVARCHAR(2000)
DECLARE @TypeDesc NVARCHAR(60)
DECLARE @Name SYSNAME
DECLARE @PhysicalName NVARCHAR(260)
DECLARE @File TINYINT
DECLARE @NewFile NVARCHAR(100)
DECLARE @Attach NVARCHAR(2000)
DECLARE @SlashPos INT
DECLARE @LastSlashPos INT
DECLARE @DoIt BIT 
DECLARE @MDDB BIT
DECLARE @MDLog BIT
DECLARE @MDFT BIT
DECLARE @DBDriveLetter CHAR(1)
DECLARE @LogDriveLetter CHAR(1)

/******************************************************/
/* To display information of what this procedure will */
/* do, set @DoIt to zero (0).  If you set it to 1,    */
/* actions will be performed                          */
/******************************************************/
SET @DoIt = 0 -- See actions to be performed
--SET @DoIt = 1 -- Performs the actions

/******************************************************/
/* Amend the @DatabaseName accordingly                */
/******************************************************/
SELECT @DatabaseName = 'database name'

/******************************************************/
/* Set the drive letter for the new locations         */
/******************************************************/
SET @DBDriveLetter = 'D'
SET @LogDriveLetter = 'E'

SET @MDDB = 1
SET @MDLog = 1
SET @MDFT = 1

IF CHARINDEX('\', @@ServerName) > 0
	SELECT @InstanceName = SUBSTRING(@@ServerName, CHARINDEX('\', @@ServerName) + 1, LEN(@@ServerName) - CHARINDEX('\', @@ServerName) + 1)
ELSE
	SELECT @InstanceName = @@ServerName

PRINT 'Database Name: ' + @DatabaseName
PRINT 'Instance Name: ' + @InstanceName

/******************************************************/
/* Amend the locations below accordingly.  I have set */
/* the locations to \SQL\Database\instance\dbname\    */
/******************************************************/
SET @NewDBLocation = @DBDriveLetter + ':\SQL\Databases\' + @InstanceName + '\' + @DatabaseName + '\'
SET @NewLogLocation = @LogDriveLetter + ':\SQL\Logs\' + @InstanceName + '\' + @DatabaseName + '\'
SET @NewFTLocation = @DBDriveLetter + ':\SQL\FullText\' + @InstanceName + '\' + @DatabaseName + '\'

/******************************************************/
/* For SQL Server 2005, use the following statement   */
/* (be sure to comment out the line for SQL 2000)     */
/******************************************************/
SET @Cmd = 'SELECT type_desc, name, physical_name INTO TempDMA FROM [' + @DatabaseName + '].sys.database_files ORDER BY type'
/******************************************************/
/* For SQL Server 2000, use this statement:           */
/* (be sure to comment out the line above)            */
/******************************************************/
--SET @Cmd = 'SELECT (CASE status & 0x40 WHEN 0x40 THEN ''LOG'' ELSE ''ROWS'' end) as type_desc, name, filename as physical_name INTO TempDMA FROM [' + @DatabaseName + ']..sysfiles ORDER BY (CASE status & 0x40 WHEN 0x40 THEN ''LOG'' ELSE ''DATA'' end)'

EXEC sp_executesql @Cmd

DECLARE Files CURSOR FAST_FORWARD FOR 
	SELECT * FROM TempDMA

IF @DoIt = 1
BEGIN
	/******************************************************/
	/* For SQL Server 2005, use the following statement   */
	/* (be sure to comment out the line for SQL 2000)     */
	/******************************************************/
	EXEC sp_detach_db @dbname = @DatabaseName, @keepfulltextindexfile = 'true'
	/******************************************************/
	/* For SQL Server 2000, use this statement:           */
	/* (be sure to comment out the line above)            */
	/******************************************************/
	--EXEC sp_detach_db @dbname = @DatabaseName
END

SET @Cmd = ''
SET @File = 1
SET @Attach = 'EXEC sp_attach_db @dbname = [' + @DatabaseName + '] '

OPEN Files

FETCH NEXT FROM Files 
	INTO @TypeDesc, @Name, @PhysicalName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @PhysicalName = LTRIM(RTRIM(@PhysicalName))
	PRINT '---> ' + @TypeDesc + ' <--> ' + @PhysicalName
	IF @TypeDesc = 'ROWS'
	BEGIN
		IF @MDDB = 1
		BEGIN
			SET @Cmd = 'MD "' + @NewDBLocation + '"'
			IF @DoIt = 1
				EXEC xp_cmdshell @Cmd
			ELSE
				PRINT 'CMD --> ' + @Cmd
			SET @MDDB = 0
		END
		SET @Cmd = 'MOVE "' + @PhysicalName + '" "' + @NewDBLocation + '"'
		IF @DoIt = 1
			EXEC xp_cmdshell @Cmd
		ELSE
			PRINT 'CMD --> ' + @Cmd
		SET @SlashPos = CHARINDEX('\', @PhysicalName)
		IF @SlashPos = 0
			SET @NewFile = @PhysicalName
		ELSE
		BEGIN
			WHILE @SlashPos > 0
			BEGIN
				SET @SlashPos = CHARINDEX('\', @PhysicalName, @SlashPos+1)
				IF @SlashPos > 0
					SET @LastSlashPos = @SlashPos
			END
			SET @NewFile = @NewDBLocation + SUBSTRING(@PhysicalName, @LastSlashPos+1, len(@PhysicalName)-@LastSlashPos)
		END
	END
	IF @TypeDesc = 'LOG'
	BEGIN
		IF @MDLog = 1
		BEGIN
			SET @Cmd = 'MD "' + @NewLogLocation + '"'
			IF @DoIt = 1
				EXEC xp_cmdshell @Cmd
			ELSE
				PRINT 'CMD --> ' + @Cmd
			SET @MDLog = 0
		END
		SET @Cmd = 'MOVE "' + @PhysicalName + '" "' + @NewLogLocation + '"'
		IF @DoIt = 1
			EXEC xp_cmdshell @Cmd
		ELSE
			PRINT 'CMD --> ' + @Cmd
		SET @SlashPos = CHARINDEX('\', @PhysicalName)
		IF @SlashPos = 0
			SET @NewFile = @PhysicalName
		ELSE
		BEGIN
			WHILE @SlashPos > 0
			BEGIN
				SET @SlashPos = CHARINDEX('\', @PhysicalName, @SlashPos+1)
				IF @SlashPos > 0
					SET @LastSlashPos = @SlashPos
			END
			SET @NewFile = @NewLogLocation + SUBSTRING(@PhysicalName, @LastSlashPos+1, len(@PhysicalName)-@LastSlashPos)
		END
	END
	IF @TypeDesc = 'FULLTEXT'
	BEGIN
		IF @MDFT = 1
		BEGIN
			SET @Cmd = 'MD "' + @NewFTLocation + '"'
			IF @DoIt = 1
				EXEC xp_cmdshell @Cmd
			ELSE
				PRINT 'CMD --> ' + @Cmd
			SET @MDFT = 0
		END
		SET @Cmd = 'MD "' + @NewFIle + '"'
		IF @DoIt = 1
			EXEC xp_cmdshell @Cmd
		ELSE
			PRINT 'CMD --> ' + @Cmd
		SET @Cmd = 'MOVE "' + @PhysicalName + '" "' + @NewFTLocation + '"'
		IF @DoIt = 1
			EXEC xp_cmdshell @Cmd
		ELSE
			PRINT 'CMD --> ' + @Cmd
	END
	SET @attach = @attach + ', @filename' + LTRIM(RTRIM(STR(@file))) + ' = ' + CHAR(39) + @NewFile + CHAR(39) + ' '
	FETCH NEXT FROM Files INTO @TypeDesc, @Name, @PhysicalName
	SET @File = @File + 1
END

IF @DoIt = 1
	EXEC sp_executesql @SQLString = @Attach
ELSE 
	PRINT 'SQL --> ' + @Attach

CLOSE Files
DEALLOCATE Files

DROP TABLE TempDMA
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.