Move database files

I realized that I'd accidentally forgotten to set the default DATA and LOG folders in SQL before configuring SharePoint... all of the databases (around 25) were using the default directories... D'OH!

CHANGES FROM ORIGINAL:
- Support for separate DATA and LOG folders
- READONLY flag to review changes before applying them
- Improved output formatting

KNOWN LIMITATIONS: Within SharePoint, the Web Analytics Reporting database uses multiple data files... the script cannot (currently) handle multiple data files... you'll see that I intentionally exclude the database
Language:
T-SQL
Keywords:
Code Snippet

-- REF: http://social.technet.microsoft.com/wiki/contents/articles/2239.t-sql-script-detach-move-and-attach-database-en-us.aspx

 

-- ============USER CONFIGURABLE VARIABLES START============= 

DECLARE @READONLY bit   -- bit flag for whether commands should be executed or simulated (0 for READONLY, 1 for READWRITE)

SET @READONLY = 1

 

DECLARE @path_DATA VARCHAR(256) -- path for backup files   

DECLARE @path_LOG VARCHAR(256) -- path for backup files   

DECLARE @enableCMDPermanently bit

SET @path_DATA = 'E:\SQL2008R2\OLTP\DATA\'   -- Destination Path for all Database DATA files

SET @path_LOG  = 'E:\SQL2008R2\OLTP\LOG\'     -- Destination Path for all Database LOG files

SET @enableCMDPermanently =

-- ============USER CONFIGURABLE VARIABLES FINISH============ 

 

 

-- =====Please do not edit variables below this line========= 

DECLARE @name VARCHAR(50) -- database name   

DECLARE @nameDB VARCHAR(50) -- database name   

DECLARE @nameLog VARCHAR(50) -- database log name   

DECLARE @fileNameDB VARCHAR(256) -- filename for backup 

DECLARE @fileNameLog VARCHAR(256) -- filename for backup   

DECLARE @destinationPath VARCHAR(256) -- new path for db 

DECLARE @dbid bigint -- db id 

 

-- Enabled CMD from T-SQL Script

PRINT 'Reconfiguring [show advanced options = 1]'

IF (@READONLY = 0)

BEGIN

  EXEC master.dbo.sp_configure 'show advanced options', 1

  RECONFIGURE

END

PRINT 'Reconfiguring [xp_cmdshell = 0]'

 

IF (@READONLY = 0)

BEGIN

  EXEC master.dbo.sp_configure 'xp_cmdshell', 1 -- enable CMD

  RECONFIGURE

END

 

-- Table variable for name/id key/values 

 

DECLARE @sysdb TABLE 

( 

    dbname nvarchar(max), 

    database_id bigint 

) 

 

INSERT INTO @sysdb (dbname,database_id) SELECT [name],[dbid] FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb', 'SvcApp_WebAnalytics_Default_Reporting')

 

-- Table variable for db file details 

DECLARE @sysfiles TABLE 

( 

    dbname nvarchar(max), 

    physical_name nvarchar(max), 

    database_id bigint, 

    type_desc nvarchar(max) 

) 

 

INSERT INTO @sysfiles (dbname,physical_name,database_id,type_desc) SELECT [name], [physical_name],[database_id],type_desc FROM sys.master_files 

 

-- Start CURSOR to iterate through database ids 

DECLARE db_cursor CURSOR FOR SELECT [database_id] FROM @sysdb 

OPEN db_cursor    

FETCH NEXT FROM db_cursor INTO @dbid    

 

-- BEGIN LOOP

WHILE @@FETCH_STATUS = 0    

BEGIN 

    --Read DB Details & File Locations 

    PRINT 'ID:   ' + CAST(@dbid AS nvarchar(MAX))

    SET @name = (SELECT [dbname] FROM @sysdb WHERE database_id = @dbid)

    PRINT 'NAME: ' + @name

    SET @nameDB = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'ROWS') 

    SET @fileNameDB = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'ROWS') 

    PRINT 'DATA: ' + @fileNameDB

    SET @nameLog = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'LOG') 

    SET @fileNameLog = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'LOG') 

    PRINT 'LOG:  ' + @fileNameLog

 

    DECLARE @cmd nvarchar(MAX) -- temp string for dynamic sql queries 

 

    -- Force Disconnect Active Connections to Database 

    SET @cmd = 'ALTER DATABASE ' +  quotename(@name) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' 

    PRINT '  ' + @cmd 

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

    SET @cmd = 'ALTER DATABASE ' +  quotename(@name) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE' 

    PRINT '  ' + @cmd

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

    -- Point DB to new file location 

    SET @cmd = 'ALTER DATABASE '+ quotename(@name) +' MODIFY FILE ( NAME = '+quotename(@nameDB)+', FILENAME = '+quotename(@path_DATA + @nameDB  + '.mdf')+ ' )' 

    PRINT '  ' + @cmd

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

    SET @cmd = 'ALTER DATABASE '+ quotename(@name) +' MODIFY FILE ( NAME = '+quotename(@nameLog)+', FILENAME = '+quotename(@path_LOG + @nameLog  + '.LDF')+ ' )' 

    PRINT '  ' + @cmd

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

    -- Detach DB 

    SET @cmd = 'sp_detach_db ' + quotename(@name) 

    PRINT '  ' + @cmd 

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

 

    -- Execute move command on shell to move physical file to new directory 

    PRINT @fileNameDB + ' => ' + @path_DATA + @nameDB + '.mdf'

    SET @cmd = 'xp_cmdshell ''move ' + '"'+@fileNameDB+'"' + ' ' + '"'+@path_DATA + @nameDB + '.mdf'+'"'''

    PRINT '  ' + @cmd

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

 

    -- Execute move command on shell to move physical file to new directory 

    PRINT @fileNameLog + ' => ' + @path_LOG + @nameLog + '.LDF' 

    SET @cmd = 'xp_cmdshell ''move "' + @fileNameLog + '" "' + @path_LOG + @nameLog + '.LDF"'''

    PRINT '  ' + @cmd

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

    -- Reattach Database to SQL Instance 

    SET @cmd = 'sp_attach_db ' + quotename(@name) + ', ' + quotename( @path_DATA + @nameDB + '.mdf') + ',' + quotename(@path_LOG + @nameLog + '.LDF') 

    PRINT '  ' + @cmd

    IF (@READONLY = 0) BEGIN EXECUTE sp_executesql @cmd END

 

 

    FETCH NEXT FROM db_cursor INTO @dbid    

END    

 

--Close Cursor 

CLOSE db_cursor    

DEALLOCATE db_cursor  

 

--OPTIONAL 

IF @enableCMDPermanently =

BEGIN 

      PRINT 'Reconfiguring [show advanced options = 1]'

      IF (@READONLY = 0)

      BEGIN

        EXEC master.dbo.sp_configure 'show advanced options',

        RECONFIGURE 

      END

 

      PRINT 'Reconfiguring [xp_cmdshell = 0]'

      IF (@READONLY = 0)

      BEGIN

        EXEC master.dbo.sp_configure 'xp_cmdshell', 0 --disable CMD 

        RECONFIGURE 

      END

END


Created 2012-08-14
comments powered by Disqus
Login