DBA and SysAdmin World

December 8, 2008

Moving Database File To Another Disk

Filed under: Script, SQL Server — Tags: , , — solihinho @ 3:09 PM
moveAll 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 Winking.

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'
Pumpkin 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.

Advertisements

7 Comments »

  1. This week the Blackboard data (file system and databases) will be migrated to the new Storage Area Network (SAN). This new SAN should improve the reliability and performance of Blackboard.

    Comment by storage area networks — January 23, 2009 @ 2:50 PM

  2. How do you determine if a database is currently in use? I am trying to write a script to accomplish this.

    Comment by Marilyn — February 5, 2009 @ 3:15 AM

    • Hi Marilyn,

      Thanks 4 the question. May be you can try select from table sysprocesses on master database e.g.:select count(*)
      from sysprocesses
      where dbid = [your database id]. If the result of the query > 0 it means your database currently in use.

      Comment by solihinho — February 5, 2009 @ 6:25 AM

  3. Thanks for this!!! You saved me a ton of work. I owe you drinks!

    Rick

    Comment by Rick Holland — March 11, 2009 @ 12:57 AM

  4. For writing script of attach db, below code will also do —>

    select ‘TEXT’ = case
    when fileid = 1 then
    char(13)+char(10)+’EXEC sp_attach_db @dbname = N”’+db_name(dbid) +”’, ‘+char(13)+char(10)+
    ‘@filename1 = N”’+filename+”’ ‘+char(13)+char(10)
    else
    ‘, @filename’+cast(fileid as varchar(10))+’ = N”’+filename+””
    end
    from sysaltfiles
    where dbid > 4

    Comment by Boolean — March 20, 2009 @ 2:54 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: