Detach and Move db files then re-attach

Bob Mihada, Sunday, March 28, 2010 



--//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

at 11:12 PM 0 comments
Labels: , ,

click for avg

Bob Mihada, Monday, March 22, 2010

Get AVG from CNET





at 9:48 AM 0 comments

View Last 20 Database Backups

Bob Mihada, Sunday, March 14, 2010


DECLARE @DBName VarChar(128)
SELECT @DBName = 'YourDB'
SELECT
TOP 20
bs.machine_name
,bs.database_name
,bs.backup_start_date
,bf.physical_device_name
FROM
msdb.dbo.backupset bs (NOLOCK)
JOIN msdb.dbo.backupmediaset bms (NOLOCK) ON bms.media_set_id = bs.media_set_id
JOIN msdb.dbo.backupmediafamily bf (NOLOCK) ON bf.media_set_id = bms.media_set_id
WHERE
bs.database_name = @DBName
ORDER BY
bs.backup_set_id desc

at 12:35 AM 0 comments
Labels: , , ,

How 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

at 2:27 AM 0 comments
Labels: , , ,

Felts like sharing again.

Bob Mihada, Wednesday, March 3, 2010

"Every day another imposter gwan come take away we place
And everywhere we go there's another one want come point in our we face
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"

at 10:09 PM 0 comments

Restore most rececent backup sequence(FULL/DIFF/LOG)

Bob Mihada, Monday, March 1, 2010

/*
Hope this help someone in a pinch. This "SHOULD" generate
a valid restore script inclusive of your FULL, DIFF*, and LOG(S)
*=I didn't take into account multiples of this
Restore most recent backup sequence(FULL/DIFF/LOG)
*/
SET NOCOUNT ON
DECLARE
@DBName NVARCHAR(128)
,@dSQL VARCHAR(MAX)
,@restoreFULL VARCHAR(MAX)
,@restoreDIFF VARCHAR(MAX)
,@restoreLOG VARCHAR(MAX)
,@MIH VarChar(MAX) --// MAKE IT HAPPEN
,@restoreType Varchar(25) --// NEW | RESTORE

SELECT @DBName = 'ansi', @restoreType = '~NEW'

DECLARE @recent TABLE (
Database_Name NVARCHAR(128)
, FIRST_FULL_LSN NUMERIC(25,0)
, FIRST_FULL_DATE DATETIME
, FIRST_FULL_BACKUP_SET_ID INT
, FIRST_DIFF_DATE DATETIME
, FIRST_DIFF_BACKUP_SET_ID INT
, FIRST_LOG_DATE DATETIME
, FIRST_LOG_BACKUP_SET_ID INT
)
INSERT INTO @recent
SELECT TOP 1
D.Database_Name
,D.first_lsn
,D.backup_finish_date
,D.backup_set_id
,I.backup_finish_date
,I.backup_set_id
,L.backup_finish_date
,L.backup_set_id
FROM
MSDB.dbo.BackupSet D (NOLOCK)
LEFT JOIN MSDB.dbo.BackupSet I (NOLOCK) ON I.Type = 'I' AND I.database_backup_lsn = D.checkpoint_lsn
LEFT JOIN MSDB.dbo.BackupSet L (NOLOCK) ON L.Type = 'L' AND L.database_backup_lsn = D.checkpoint_lsn AND CASE WHEN I.backup_finish_date > L.backup_finish_date THEN 0 ELSE 1 END = 1
WHERE
D.Database_Name = @DBName
AND D.Type = 'D'
GROUP BY
D.Database_Name
,D.first_lsn
,D.backup_finish_date
,D.backup_set_id
,I.backup_finish_date
,I.backup_set_id
,L.backup_finish_date
,L.backup_set_id
ORDER BY
D.backup_finish_date DESC
, D.backup_set_id ASC
, I.backup_finish_date ASC
, I.backup_set_id ASC
, L.backup_finish_date ASC
, L.backup_set_id ASC
IF object_id('tempdb..#fileinfo') IS NOT NULL drop table #fileinfo

create table #fileinfo (
[db] varchar(100),
name varchar(100),
filename varchar(100),
FileType varchar (100))

SELECT @dSQL= 'INSERT INTO #fileinfo([db],name,filename, FileType)
SELECT DISTINCT ''[' + @DBName + ']'',rtrim(name),rtrim(filename), CASE WHEN status & 0x40 = 0x40 THEN ''DATA'' ELSE ''LOG'' END from [' + @DBName + ']..sysfiles'

EXEC(@dSQL)

--SELECT * FROM #fileinfo

SELECT @restoreFULL = 'RESTORE DATABASE [' + @DBName + ']
FROM '
+
(SELECT
CASE WHEN family_sequence_number > 1 THEN ' ,' ELSE '
' END +
'DISK = ''' + bmf.physical_device_name + '''
'
FROM
msdb..backupset bs (NOLOCK)
JOIN msdb..backupmediafamily bmf (NOLOCK) ON bs.media_set_id = bmf.media_set_id
JOIN @recent R ON R.FIRST_FULL_BACKUP_SET_ID = bs.backup_set_id

ORDER BY
bs.backup_set_id DESC
,bs.media_set_id DESC
,bmf.family_sequence_number asc
FOR XML PATH('')) +
'WITH ' +
CASE WHEN @restoreType = 'NEW' THEN (
SELECT '
' + CASE WHEN ROW_NUMBER() OVER (ORDER BY db) > 1 THEN ' ,' ELSE ' ' END + 'MOVE [' + name + '] TO ''' + filename + '''
,'
FROM
#fileinfo
FOR XML PATH('')) ELSE '
' END + 'NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10'
SELECT @restoreFULL = REPLACE( REPLACE(@restoreFULL,'&#x0D;',''),'&amp;','&')

SELECT @restoreDIFF = 'RESTORE DATABASE [' + @DBName + ']
FROM '
+
(SELECT
CASE WHEN family_sequence_number > 1 THEN ' ,' ELSE '
' END +
'DISK = ''' + bmf.physical_device_name + '''
'
FROM
msdb..backupset bs (NOLOCK)
JOIN msdb..backupmediafamily bmf (NOLOCK) ON bs.media_set_id = bmf.media_set_id
JOIN @recent R ON R.FIRST_DIFF_BACKUP_SET_ID = bs.backup_set_id

ORDER BY
bs.backup_set_id DESC
,bs.media_set_id DESC
,bmf.family_sequence_number asc
FOR XML PATH('')) +
'WITH NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10'
SELECT @restoreDIFF = REPLACE( REPLACE(@restoreDIFF,'&#x0D;',''),'&amp;','&')

SELECT @restoreLOG = 'RESTORE DATABASE [' + @DBName + ']
FROM '
+
(SELECT
CASE WHEN family_sequence_number > 1 THEN ' ,' ELSE '
' END +
'DISK = ''' + bmf.physical_device_name + '''
'
FROM
msdb..backupset bs (NOLOCK)
JOIN msdb..backupmediafamily bmf (NOLOCK) ON bs.media_set_id = bmf.media_set_id
JOIN @recent R ON R.FIRST_LOG_BACKUP_SET_ID = bs.backup_set_id

ORDER BY
bs.backup_set_id DESC
,bs.media_set_id DESC
,bmf.family_sequence_number asc
FOR XML PATH('')) +
'WITH NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10'
SELECT @restoreLOG = REPLACE( REPLACE(@restoreLOG,'&#x0D;',''),'&amp;','&')

SELECT @MIH =
@restoreFULL
+ @restoreDIFF
+ @restoreLOG
+ '
RESTORE DATABASE [' + @DBName + '] WITH RECOVERY'

PRINT @MIH
SET NOCOUNT OFF

at 2:27 AM 0 comments
Labels: , , , , , ,
Subscribe to: Posts (Atom)