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  

click for avg

Get AVG from CNET

Try this one

http://pcsupport.about.com/od/fixtheproblem/ss/safemodexp.htm

http://www.bleepingcomputer.com/virus-removal/remove-antivirus7

View Last 20 Database Backups

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

How much longer for the RESTORE or BACKUP?

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

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

http://p-a-todareggae.blogspot.com/2009/12/home-t-cocoa-tea-cutty-ranks-another.html

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

/*

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