--//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
Detach and Move db files then re-attach
Bob Mihada, Sunday, March 28, 2010
Labels:
sp_attach_db,
sp_detach_db,
SQL
Subscribe to:
Post Comments (Atom)
Comments :
Post a Comment