Detach and Move db files then re-attach



--//Detach and Move db files then re-attach
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE
@DBName VarChar(128)
,@data_filename varchar(256)
,@log_filename varchar(256)
,@new_data_filename varchar(256)
,@new_log_filename varchar(256)

,@new_data_file_path VarChar(256)
,@new_log_file_path VarChar(256)
,@cmd VarChar(512)
,@sqlcmd VarChar(512)
,@GOBANANA BIT

SELECT
@DBName = 'bobland'
,@new_data_file_path = 'L:\SQL_DATA\RAID10\'
,@new_log_file_path = 'E:\SQL_LOGS\RAID10\'
,@GOBANANA = 1

IF NOT EXISTS(SELECT name FROM sys.databases where name = @DBName)
BEGIN
PRINT 'Excuse me, but that database, "'+ @DBName + '",does not exist here'
END

IF OBJECT_ID('tempdb..#db_files') IS NOT NULL DROP TABLE #db_files

CREATE TABLE #db_files
(
[name] varchar(128)
, [fileid] int
, [filename] varchar(256)
, [filegroup] varchar(128)
, [size] varchar(18)
, [maxsize] varchar(18)
, [growth] varchar(18)
, [usage] varchar(9)
)

-- store db files data into #db_files
SELECT @sqlcmd = 'USE [' + @DBName + ']
INSERT INTO #db_files EXEC sp_helpfile'

EXEC(@sqlcmd )

SELECT
@data_filename = MAX(DATA_FILE)
,@log_filename = MAX(LOG_FILE)
FROM (
SELECT
CASE WHEN UPPER(RIGHT([filename],3)) = 'MDF' THEN [filename] END DATA_FILE
,CASE WHEN UPPER(RIGHT([filename],3)) = 'LDF' THEN [filename] END LOG_FILE
FROM
#db_files
) X

SELECT
@new_data_filename = REVERSE(SUBSTRING(REVERSE(@data_filename), 0, CHARINDEX('\', REVERSE(@data_filename), 1)))
,@new_log_filename = REVERSE(SUBSTRING(REVERSE(@log_filename), 0, CHARINDEX('\', REVERSE(@log_filename), 1)))

SELECT
@new_data_file_path = @new_data_file_path + @new_data_filename
,@new_log_file_path = @new_log_file_path + @new_log_filename

PRINT 'CURRENT SOURCE----------------------'
PRINT @data_filename
PRINT @log_filename
PRINT CHAR(10)
PRINT 'NEW DESTINATION ----------------------'
PRINT @new_data_file_path
PRINT @new_log_file_path


SELECT @sqlcmd = 'sp_detach_db ''' + @DBName + ''''
PRINT CHAR(10)
PRINT 'DETACH @sqlcmd: ' + @sqlcmd
IF @GOBANANA = 1 EXEC(@sqlcmd)

IF @data_filename <> @new_data_file_path
BEGIN
SELECT @cmd = 'MOVE ' + @data_filename + ' ' + @new_data_file_path
PRINT CHAR(10)
PRINT 'MOVE FILE @cmd: ' + @cmd
IF @GOBANANA = 1 exec master..xp_cmdshell @cmd
END
ELSE
BEGIN
PRINT CHAR(10)
PRINT 'DATA file destination is identical to source'
END

IF @log_filename <> @new_log_file_path
BEGIN
SELECT @cmd = 'MOVE ' + @log_filename + ' ' + @new_log_file_path
PRINT CHAR(10)
PRINT 'MOVE FILE @cmd: ' + @cmd
IF @GOBANANA = 1 exec master..xp_cmdshell @cmd
END
ELSE
BEGIN
PRINT CHAR(10)
PRINT 'LOG file destination is identical to source'
END

SELECT @sqlcmd = 'sp_attach_db @dbname =''' + @DBName + '''
, @filename1 = '
''+ @new_data_file_path + '''
, @filename2 = '
'' + @new_log_file_path +''''
PRINT CHAR(10)
PRINT 'ATTACH @sqlcmd: ' + @sqlcmd
IF @GOBANANA = 1 EXEC(@sqlcmd)

SET ANSI_WARNINGS ON
SET NOCOUNT OFF

Comments :

0 comments to “Detach and Move db files then re-attach”