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.

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 SQLDatabaseinstancedbname    */
/******************************************************/
SET @NewDBLocation = @DBDriveLetter + ':SQLDatabases' + @InstanceName + '' + @DatabaseName + ''
SET @NewLogLocation = @LogDriveLetter + ':SQLLogs' + @InstanceName + '' + @DatabaseName + ''
SET @NewFTLocation = @DBDriveLetter + ':SQLFullText' + @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


SQL: Reinstalled SQL Server 2005, with the wrong collation??

If yes, then following the instructions below[ref]Please ensure you test any script taken from my website on a test/development machine, before running on a production server.[/ref], from MSDN (http://msdn.microsoft.com/en-us/library/ms179254.aspx):

  • Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them. See my note below
  • Export all your data using a tool such as bulk copy
  • Drop all the user databases.
  • Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

  • Create all the databases and all the objects in them.
  • Import all your data.

However, if you detach your databases instead of exporting all the data, you can simply re-attach the databases once the rebuild is complete.  I was having problem with an installation of SQL Server, and when I had the server rebuilt, I selected the wrong collation for the server.  The database was correct, so I just detached the database before rebuilding the master, and now everything is fine

🙂

SQL: Problems with installing SQL Server

If you are trying to install SQL Server 2005, and receive an error regarding the SQL Server Native Client package being missing, go to your “servers” install CD/folder, and right-click on the sqlncli.msi file, and click on Uninstall.

Once uninstalled, just re-run the setup again, and it should install successfully.

SQL: Move “tempdb” database

When SQL Server is installed, the “tempdb” database is placed by default, on the same drive that SQL Server is installed on.

As this is usually the same drive as the operating system, it is best to move the “tempdb” database to a separate drive.

To move the “tempdb” database to a separate drive to improve performance, follow these steps:

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

  1. Open “SQL Server Management Studio”.
  2. Connect to the relevant server.
  3. Click the “New Query” button.
  4. Copy and paste the following into the query pane:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{location}tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{location}templog.ldf');
GO
  1. Change the {location} reference in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.
  2. Click Execute.
  3. Open “Control Panel” and then select the “Administrative Tools”, and select “Services”.
  4. Stop and Start (or click on Restart) the “SQL Server (MSSQLSERVER)” service.
  5. Go back to “SQL Server Management Studio” and open a new query pane.
  6. Copy and paste the following to verify that tempdb has moved to the new location:
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
  1. Click Execute.
  2. In the “physical_name” column, you should see the path to the new location.