--//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, 2010View Last 20 Database Backups
Bob Mihada, Sunday, March 14, 2010How much longer for the RESTORE or BACKUP?
Bob Mihada, Monday, March 8, 2010/*
Hope this helps. Remember Sharing is caring!
BTW, be sure you have this function, Datedifftowords, found here:
http://www.sqldev.org/sql-server-database-engine/regarding-query-performence-97344.shtml
*/
DECLARE @Count INT,
@Counter INT,
@SQL NVARCHAR(MAX),
@sql_handle VARBINARY(64),
@session_id [SMALLINT],
@command [NVARCHAR](16),
@percent_complete [REAL],
@RunTime VARCHAR(50)
DECLARE @tmp TABLE(
idx INT IDENTITY ( 1 , 1 ),
[session_id] [SMALLINT] NOT NULL,
[command] [NVARCHAR](16) NOT NULL,
[percent_complete] [REAL] NOT NULL,
[StartTime] DATETIME
)
INSERT INTO @tmp
SELECT session_id,
command,
percent_complete,
start_time
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','RESTORE LOG','RESTORE DATABASE')
SELECT @Count = @@ROWCOUNT,
@Counter = 1
WHILE @Counter <= @Count
BEGIN
SELECT @sql_handle = sql_handle,
@session_id = t.session_id,
@command = t.command,
@percent_complete = t.percent_complete,
@RunTime = dbo.Datedifftowords(start_time,Getdate())
FROM sys.dm_exec_requests s
JOIN @tmp t
ON t.[session_id] = s.session_id
WHERE t.idx = @Counter
SELECT @session_id [@session_id],
@command [@command],
@percent_complete [@percent_complete],
@RunTime [@RunTime],
TEXT
FROM sys.Dm_exec_sql_text(@sql_handle)
SELECT @Counter = @Counter + 1
END
Felts like sharing again.
Bob Mihada, Wednesday, March 3, 2010
Though they try to get us out
We've got no fear and we've got no doubt
We've got the talent and we've got the skill
We know the way and we've got the will"