Moving Database File To Another Disk

All our databases server using SAN (Storage Area Network) for the disk storage. Last time we used DELL AX100 for the storage. After that we upgraded to DELL MD3000.  DBA was asked to move the databases to the new disk.

We have hundred of databases on one server. The best way to do this is using detach and attach method. In this case, we want to move all of user databases, so what we did is just detach all databases, stop the SQL Server, move the data file to the new disk, start the SQL Server and attach all the data files. We need downtime quite long to copy the files to new storage. If you does not allow to downtime quite long, just detach and attach the databases one by one. In my case, it is fine to downtime about 1 hour .

What I want to share is a script to generate T-SQL for detaching and attaching the database. Can you imagine if we use Enterprise Manager to detach and attach thousand or hundred databases ?

Step by step to move the data file

1. Before you stop the SQL Server, run below script to generate attach script for all user databases.

SET NOCOUNT ON DECLARE @dbname SYSNAME DECLARE @sqlstr VARCHAR(4000) DECLARE @filename VARCHAR(4000) DECLARE @result VARCHAR(8000) DECLARE @Counter TINYINT DECLARE @NFiles TINYINT IF OBJECT_ID('tempdb..#db') IS NOT NULL DROP TABLE #db --exclude system databases SELECT name INTO #db FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','tempdb','msdb') ORDER BY name IF OBJECT_ID('tempdb..#files') IS NOT NULL DROP TABLE #files CREATE TABLE #files ( DbName SYSNAME, LogicalName SYSNAME, FileId INT, FileName VARCHAR(1000), ) IF OBJECT_ID('tempdb..#result') IS NOT NULL DROP TABLE #result CREATE TABLE #result ( sql VARCHAR(8000) ) --get all files WHILE (1 = 1) BEGIN SET @dbname = NULL SELECT TOP 1 @dbname = name FROM #db IF @dbname IS NULL GOTO _FINISH1 SET @sqlstr = ' USE ' + @dbname + ' INSERT INTO #files (DbName, LogicalName, FileId, FileName)' + ' SELECT db_name(), name, fileid, filename ' + ' FROM sysfiles ' + ' ORDER BY fileid ' EXEC (@sqlstr) DELETE FROM #db WHERE name = @dbname END _FINISH1: --generate attach script WHILE (1 = 1) BEGIN SET @dbname = NULL SELECT TOP 1 @dbname = DbName FROM #files ORDER BY DbName IF @dbname IS NULL GOTO _FINISH2 SET @Counter = 1 SELECT @NFiles = COUNT(*) FROM #files WHERE DbName = @dbname SET @result = 'exec sp_attach_db N''' + @dbname + '''' WHILE (@Counter <= @NFiles) BEGIN SELECT @Filename = FileName FROM #files WHERE DbName = @DbName AND FileId = @Counter SET @result = @result + ',@filename' + CAST(@counter as VARCHAR) + ' = N''' + RTRIM(@FileName) + '''' SET @Counter = @Counter + 1 END INSERT INTO #result SELECT @result DELETE FROM #files WHERE DbName = @dbname END _FINISH2: SELECT * FROM #result DROP TABLE #db SET NOCOUNT OFF

This is an example of the result of above script for AdventureWorks and AdventureWorksDW.

exec sp_attach_db N'AdventureWorks',@filename1 = N'D:DataAdventureWorks_Data.mdf' ,@filename2 = N'E:LogAdventureWorks_Log.ldf' exec sp_attach_db N'AdventureWorksDW',@filename1 = N'D:DataAdventureWorksDW_Data.mdf' ,@filename2 = N'E:LogAdventureWorksDW_Log.ldf'

In my local PC, I put all data files (*.mdf, *.ndf) in D:DATA and log files (*.ndf) in E:LOG

Keep this result to another text file

2. Detach All User Databases
Run below query than run the result of this query to detach all user databases. To remainder, detach process will fail if there is an application that using this database. Just make sure there is no process hold this database if detach process is failed. If you detach database using Enterprise Manager, you will see how many connection that holding specified the database. Just clear the connection if you want to detach that database immediately without want to wait the process is done.

--exclude system databases SELECT replace('exec sp_detach_db ''@dbname'',''true''','@dbname', name) AS result FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model')

3. Stop SQL Server Database Engine

4. Move all data files and log files to the new disk

5. Start SQL Server Database Engine

6. From result of step 1, change old path with new path e.g.: D:DATA be F:SQLDATA than run this script to attach back all databases.