Bob Mihada

Tasty note to self

Bob Mihada, Wednesday, May 9, 2012

SELECT DISTINCT
MAX(EdiStcID_Grp)EdiStcID_Grp,EdiStcID, DENSE_RANK() OVER(ORDER BY
MAX(EdiStcID_Grp)) [Grp#]
FROM
#tmpSRT tsrt
GROUP BY tsrt.EdiStcID

DarkMagic With RedGate Virtual Restore and a folder full of Backups

Bob Mihada, Wednesday, October 5, 2011

Sorry, blog, for puking all over on this one. I got to excited and had to put it somewhere for my own amazement. This came about cause I got a ton of DBs to restore for our Staging environment and our EMC SnapShot/Clone not completely dialed in yet. So exciting.

Basically, I created a backup folder on the staging server and copied a full set of backups to it. Then using the script below, with minor tweaks to environment specific paths, I can generate a script to do a virtual restore of all my backups. The GUI is great, but I got 50+ DB to get through, and I need to get this to a less manual process for weekly refreshes. Anywho... Enjoy! Sharing is caring.

The "--#region" stuff is from SSMS PACK, get yours.

IF OBJECT_ID('tempdb..#tmp_VirtualRestore') IS NOT NULL DROP PROCEDURE #tmp_VirtualRestore
GO
CREATE PROCEDURE #tmp_VirtualRestore
 @FilePath VARCHAR(256)
 ,@DataFilePath VARCHAR(256) = 'D:\SQL_DATA\'
 ,@LogsFilePath VARCHAR(256) = 'L:\SQL_LOGS\'
 ,@AppendSuffix bit = 1
AS 
BEGIN
SET NOCOUNT ON 

DECLARE  
 @SQL VARCHAR(4000)

IF LEN(ISNULL(@FilePath,'')) < 5 
BEGIN
 PRINT 'Eh, Invalid FilePath?  Please review: ' + ISNULL(@FilePath,'')
 RETURN                                 
END
 
--#region Interrogate Backup file for meta info

 --//Get/Store Header info from backup file(s)
 IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header
 CREATE TABLE #header
 (
  IDX INT identity(1,1)
  , BackupName nvarchar(128)
  , BackupDescription nvarchar(255)
  , BackupType smallint
  , ExpirationDate datetime
  , Compressed tinyint
  , Position smallint
  , DeviceType tinyint
  , UserName nvarchar(128)
  , ServerName nvarchar(128)
  , DatabaseName nvarchar(128)
  , DatabaseVersion int
  , DatabaseCreationDate datetime
  , BackupSize numeric(20, 0)
  , FirstLSN numeric(25, 0)
  , LastLSN numeric(25,0)
  , CheckpointLSN numeric(25,0)
  , DatabaseBackupLSN numeric(25, 0)
  , BackupStartDate datetime
  , BackupFinishDate datetime
  , SortOrder smallint
  , CodePage smallint
  , UnicodeLocaleId int
  , UnicodeComparisonStyle int
  , CompatibilityLevel tinyint
  , SoftwareVendorId int
  , SoftwareVersionMajor int
  , SoftwareVersionMinor int
  , SoftwareVersionBuild int
  , MachineName nvarchar(128)
  , Flags int
  , BindingID uniqueidentifier
  , RecoveryForkID uniqueidentifier
  , Collation nvarchar(128)
  , FamilyGUID uniqueidentifier
  , HasBulkLoggedData bit
  , IsSnapshot bit
  , IsReadOnly bit
  , IsSingleUser bit
  , HasBackupChecksums bit
  , IsDamaged bit
  , BeginsLogChain bit
  , HasIncompleteMetaData bit
  , IsForceOffline bit
  , IsCopyOnly bit
  , FirstRecoveryForkID uniqueidentifier
  , ForkPointLSN numeric(25, 0) null
  , RecoveryModel nvarchar(60)
  , DifferentialBaseLSN numeric(25, 0) null
  , DifferentialBaseGUID uniqueidentifier
  , BackupTypeDescription nvarchar(60)
  , BackupSetGUID uniqueidentifier null
 );

 INSERT #header EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @FilePath + '''');
 
 IF OBJECT_ID('tempdb..#filelist') IS NOT NULL DROP TABLE #filelist
  CREATE TABLE #fileList
  (
   LogicalName NVARCHAR(128)
    , PhysicalName NVARCHAR(260)
    , [Type] CHAR(1)
    , FileGroupName NVARCHAR(128)
    , Size NUMERIC(20, 0)
    , MaxSize NUMERIC(20, 0)
    , FileID BIGINT
    , CreateLSN NUMERIC(25, 0)
    , DropLSN NUMERIC(25, 0)
    , UniqueID UNIQUEIDENTIFIER
    , ReadOnlyLSN NUMERIC(25, 0)
    , ReadWriteLSN NUMERIC(25, 0)
    , BackupSizeInBytes BIGINT
    , SourceBlockSize INT
    , FileGroupID INT
    , LogGroupGUID UNIQUEIDENTIFIER
    , DifferentialBaseLSN NUMERIC(25, 0)
    , DifferentialBaseGUID UNIQUEIDENTIFIER
    , IsReadOnly BIT
    , IsPresent BIT)
  
 INSERT #fileList EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @FilePath + '''');

--#endregion  

--#region Clean DataFileName

IF OBJECT_ID('tempdb..#kissfilelist') IS NOT NULL DROP TABLE #kissfilelist
  CREATE TABLE #kissfilelist
  (
  FileID BIGINT
  , LogicalName NVARCHAR(128)
    , DataFileName NVARCHAR(260)
    , [Type] CHAR(1))
INSERT INTO #kissfilelist
 SELECT 
  FileID
  ,LogicalName
  ,LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(PhysicalName),0,CHARINDEX('\', REVERSE(PhysicalName),0)))))
  ,Type
 FROM #fileList

--#endregion

IF EXISTS(SELECT DatafileName FROM #kissfilelist WHERE
 RIGHT(DataFileName,4) NOT IN ('.ldf','.mdf','.ndf'))
BEGIN
 PRINT 'ERROR, Unexpected extention in DataFileName '
 SELECT  'ERROR, Unexpected extention in DataFileName ' + DatafileName 
  FROM #kissfilelist WHERE RIGHT(DatafileName,4) NOT IN ('.ldf','.mdf','.ndf')

 RETURN
END 

 UPDATE #kissfilelist
  SET DataFileName = 
   REPLACE(REPLACE(REPLACE(DataFileName,'.ldf','.vldf'),'.mdf','.vmdf'),'.ndf','.vndf')

 SELECT @SQL = ( 
 SELECT '
--// [' + DatabaseName + '] '  + CONVERT(VARCHAR(50),GETDATE(),109) + ' 
RESTORE DATABASE [' + DatabaseName + CASE WHEN ISNULL(@AppendSuffix,0) = 1 THEN '_Virtual' ELSE '' END + '] FROM
DISK=N' + QUOTENAME(@FilePath,CHAR(39)) + '
WITH' 
 FROM #header
  FOR XML PATH('')
 )
 
 SELECT @SQL = @SQL + (
 SELECT '
 ' +
  CASE WHEN ROW_NUMBER() OVER(ORDER BY FileID ASC) > 1 THEN  ',' ELSE '' END
  + 'MOVE N' + QUOTENAME(LogicalName,CHAR(39)) + 
   ' TO N' + QUOTENAME(CASE WHEN Type ='L' THEN @LogsFilePath ELSE @DataFilePath END 
    + DataFileName
    ,CHAR(39))    
 FROM 
  #kissfilelist
 FOR XML PATH('')
 )
 
 SELECT @SQL = @SQL + (
  SELECT '
 ,NORECOVERY, STATS=10, REPLACE
 RESTORE DATABASE  [' + DatabaseName + CASE WHEN ISNULL(@AppendSuffix,0) = 1 THEN '_Virtual' ELSE '' END + '] WITH RECOVERY'

FROM #header
  FOR XML PATH('')
 )
 SELECT @SQL = REPLACE(@SQL,'&#x0D;','') 
 
 PRINT @SQL
END 
GO
SET NOCOUNT ON
DECLARE 
 @BackUpFolder VarChar(512)
 ,@CMD VarChar(1028)
 ,@FileCount INT
 ,@Counter INT
 ,@DBFileName VARCHAR(256)
 
 ,@DataFilePath VARCHAR(256)
 ,@LogsFilePath VARCHAR(256)
 ,@AppendSuffix bit

SELECT  
 @BackUpFolder = 'D:\SQL_BACKUPS\SQLa\'
 ,@DataFilePath = 'D:\SQL_DATA\'
 ,@LogsFilePath = 'L:\SQL_LOGS\'
 ,@AppendSuffix = 0
--#region Meat and Potaters
 
--//Temp table to store cmd outputs
IF object_id('tempdb..#tmpDetails') IS NOT NULL drop table #tmpDetails
 create table #tmpDetails(details varchar(4000), DateLogged DateTime Default(getDate()))

--// basic cmd line  
SELECT
 @cmd = 'dir /A-D /ON ' + @BackUpFolder

--// Capture files 
IF object_id('tempdb..#tmpFiles') IS NOT NULL drop table #tmpFiles
CREATE TABLE #tmpFiles (Files VARCHAR(500))
INSERT  INTO #tmpFiles
  EXEC xp_cmdshell @cmd

DECLARE  @tmpDate TABLE (IDX INT IDENTITY(1,1),fileDate DateTime, FileName VarChar(500))

INSERT INTO @tmpDate
   SELECT
  CONVERT(DATETIME, LEFT(files, 20))
  ,LTRIM(RIGHT(files, CHARINDEX(' ', REVERSE(files)))) 
   FROM
  #tmpFiles
   WHERE
  ISDATE(LEFT(files, 20)) = 1
SELECT @FileCount = @@ROWCOUNT, @Counter = 1

--#endregion
WHILE @Counter < @FileCount
BEGIN
 SELECT 
  @DBFileName = @BackUpFolder + FILENAME 
 FROM @tmpDate WHERE IDX = @Counter
 --//PRINT @DBFileName
 
 EXEC #tmp_VirtualRestore @DBFileName,@DataFilePath,@LogsFilePath,  @AppendSuffix
 
 SELECT @Counter = @Counter + 1
END

I just cared all over you :-P

VS2010 DB Compare fails.. Reverse Engineer my @$$

Bob Mihada, Thursday, September 29, 2011

This gave me much grief. In the end we ran a trace to find the last sql command and found this nugget of joy.

If you get hung up on:

"The Reverse Engineer operation cannot continue because you have been denied View Definition permission on at least one object in the '...' database."

Run the following, and grant accordingly.

/*
VS2010 DB Compare fails with:
"The Reverse Engineer operation cannot continue because you have been denied View Definition permission on at least one object in the '...' database."

User is in a role that has view definition on sys and dbo, but thats not enough, apparently

GOOD
HasBaselinePermissions IsDeniedObjectPermissions
1      0

BAD
HasBaselinePermissions IsDeniedObjectPermissions
0      0

To fix:
USE master
GRANT VIEW ANY DEFINITION TO [randomSQLUser]
--REVOKE VIEW ANY DEFINITION TO [randomSQLUser]
*/
USE master
EXECUTE AS LOGIN = 'randomSQLUser' ;
select * From fn_my_permissions(NULL, NULL)
SELECT
    CAST(CASE WHEN HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DEFINITION') = 1
              THEN 1
              ELSE 0
         END AS BIT) AS 'HasBaselinePermissions'
  , CAST(CASE WHEN 1 = IS_MEMBER('db_owner')
                   OR 1 = IS_SRVROLEMEMBER('sysadmin')
                   OR 0 = (
                           SELECT
                            COUNT(ALL [p].[state])
                           FROM
                            [sys].[database_permissions] [p]
                            LEFT JOIN [sys].[database_principals] [pr] ON [pr].[principal_id] = [p].[grantee_principal_id]
                           WHERE
                            ([p].[type] IN ('VW', 'CL'))
                            AND [p].[state] = 'D'
                            AND ([p].[grantee_principal_id] = DATABASE_PRINCIPAL_ID()
                                 OR IS_MEMBER([pr].[name]) = 1)
                          ) THEN 0
              ELSE 1
         END AS BIT) AS 'IsDeniedObjectPermissions'  
         
REVERT;

SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS

Bob Mihada, Wednesday, September 7, 2011

/*
 SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS
 Moving DBs, prompted me to conjure this up. 
 Situation: You moved dbs to a new server, do your best to sync
 previous account to new server, with SIDs intact, yet every other
 user that tries to connect ends up at your desk, WTM?
 
 Better safe than sorry. Autofix all orphaned account, and tell them to 
 "TRY NOW!" :-P
 
 Note: one error that came up was when a corresponding principal account
   does not exists at server level
*/

EXEC sp_MSForEachDB '
USE [?]
DECLARE @FixDude Varchar(8000)
SELECT 
 @FixDude = REPLACE((
SELECT DISTINCT 
    ''    EXEC sp_change_users_login ''''Auto_Fix'''' ,'' + QUOTENAME(name,char(39)) + ''
    ''
    FROM
    sys.database_principals
WHERE
    type = ''s''
    AND default_schema_name = ''dbo'' 
    AND name <> ''dbo'' 
FOR XML PATH('''')),''&#x0D;'','''')

IF LEN(FixDude) > 15

BEGIN
 SELECT @FixDude =''USE [?]
'' + @FixDude

 PRINT @FixDude
 EXEC (@FixDude)
END'
   
RETURN

Migrating to win2003 x64 with 32bit COM+ Application..TY Yigeulruseu

Bob Mihada, Wednesday, August 24, 2011

Cheese and Rice, I knew this was out there but didn't think I really had reach out overseas. Spent quite a few hours trying nail down an easy replicatable process to install legacy vb6 COM+ app on to new win2003 x64. First had to battle the 64 vs 32 bit hell. SYTEMWOW64 made me crossed-eyed. Got past that and got hung up trying automate the install of an pre-existing Com+ App, on a win2003x32, in the form of a app.msi + app.msi.cab. Darn thing just didn't want to install. I could re-create manually but the number of steps involved and the icky feeling I was getting forced me to press on looking for a better solution. I found snippets here and there, but nothing quite as complete as what I found here: http://nadobest.egloos.com/9589057, THANK YOU KOREA and GOOGLE TRANSLATE! I would like to give honorable mention to : http://www.carlosag.net/articles/configure-com-plus. This looked solid, but, I'm sure due to the twilight hour, I could not get it to work as I wanted. Anyway, I hack this together and GIVE FULL CREDIT to my new hero overseas, Yigeulruseu.

'--// THANK YOU KOREA: http://nadobest.egloos.com/9589057
'--// Modded to fit my needs

'************************************************* *****************************
'Registers / unregisters a COM / .NET DLL as a COM + app
'************************************************* *****************************
Option Explicit

Dim ApplicationName, ApplicationDescription, Identity, Password

ApplicationName = "MyHappyApp"
ApplicationDescription = "My vb6 dlls"
Identity = InputBox("Enter UserName: ", "[Domain\UserName]")
password = InputBox("Enter Password: ", "Password")

UnInstallApplication ApplicationName

InstallApplication ApplicationName, ApplicationDescription, 2, Identity, password, 3

InstallComponent ApplicationName, "D:\Components\HappyDAL.dll", "Data Acess Layer", 2, 4, -1, 4, 0, 0
InstallComponent ApplicationName, "D:\Components\HappyBUS.dll", "Business Objects/Logic", 2, 4, -1, 4, 0, 0
InstallComponent ApplicationName, "D:\Components\HappySecure.dll", "Security Functionality", 2, 4, -1, 4, 0, 0

'************************************************* *****************************
'Installs the Application
'************************************************* *****************************
'Activation (Activation type): 0 - The library, one-server
'AccessChecksLevel (Click the Security tab of the security level): 0 - Perform access checks only at the process level, one-process and component level access checks
'ApplicationAccessChecksEnabled (Click the Security tab of the authorization): 1-check (perform access checks for this application), 0 - eonchekeu (access checks for this application, None)
'Authentication (call level of authentication, the server type is only valid for): 1 - No 2 - connection, 3 - Call 4-packet, a 5-packet integrity, 6 - Packet Privacy
'ImpersonationLevel (the level): 1 - Anonymous, 2 - ID, 3 - the 4-delegate
'Identity: Interactive User - an interactive user, NT AUTHORITYLocalService - Local Service, NT AUTHORITYNetworkService - Network Services, then the user - ID type directly
' Reference: Application Level In all of the attribute
Sub InstallApplication (ApplicationName, ApplicationDescription, Authentication, Identity, Password, ImpersonationLevel)	
	Dim cat, collApps, app
	
	Set cat = CreateObject("COMAdmin.COMAdminCatalog")
	Set collApps = cat.GetCollection("Applications")
	collApps.Populate
	Set app = collApps.Add
	app.Value("Name") = ApplicationName
	app.Value("Description") = ApplicationDescription
	app.Value("Authentication") = 4
	app.Value("Identity") = Identity
	app.Value("Password") = Password
	'Activation-type setting (0 - library, 1-Server)
	app.Value ("Activation") = 1
	app.Value ("AccessChecksLevel") = 0
	app.Value ("ApplicationAccessChecksEnabled") = 0
	app.Value ("ImpersonationLevel") = ImpersonationLevel
	collApps.SaveChanges

End Sub
'************************************************* *****************************
'Uninstalls the Application
'************************************************* *****************************
Sub UninstallApplication (ApplicationName)
	Wscript.Echo "Unregistering the existing application ..."
	
	Dim cat, collApps, numApps, i
	
	Set cat = CreateObject ("COMAdmin.COMAdminCatalog")
	Set collApps = cat.GetCollection ("Applications")
	collApps.Populate
	numApps = collApps.Count
	
	For i = numApps - 1 To 0 Step -1
		If collApps.Item (i). Value ("Name") = ApplicationName Then
			collApps.Remove (i)
			WScript.echo "- Application" & ApplicationName & "removed!"
		End If
	Next
	collApps.SaveChanges
	
End Sub

'************************************************* *****************************
'Installs the Component
'************************************************* *****************************
'ApplicationDll: DLL path, people, including
'Transaction: 0 - Disabled, 1 - Not Supported, 2 - Supported, 3 - Required, 4 - Requires New
'TxIsolationLevel (transaction isolation level): 0 - All 1 - read uncommitted, 2-read-committed, and 3 - that you can repeat the reading, a 4-yeonsokdoem
'JIT: -1 - JIT enabled, use, 0 - JIT does not activated
'Synchonization: 0 - Disabled 1 - No 2 - Yes 3 - Required 4 - need saeteuraenjaeksyeon
' See also: component-level attribute that all
Sub InstallComponent (ApplicationName, ApplicationDLL, Description, Transaction, TxIsolationLevel, JIT, SYN, ComponentAccessChecksEnabled, ObjectPoolingEnabled)
	Dim cat, collApps, App, numApps, numComponents, i, j
	Dim components, component
	
	Set cat = CreateObject("COMAdmin.COMAdminCatalog")
	Set collApps = cat.GetCollection("Applications")
	collApps.Populate
	numApps = collApps.Count
	For i = numApps -1 To 0 Step -1
		'Wscript.Echo collApps.Item (i). Value ("Name")
		If collApps.Item (i).Value("Name") = ApplicationName Then
		Wscript.Echo App.Value("ID")
			Set App = collApps.Item(i)
			cat.InstallComponent App.Value("ID"), ApplicationDLL, "", ""			
		End If
	Next
End Sub

Sharing is caring

P.S. this wasn't the end all to getting the 32bit to work on x64. There was more but this was the best find.

NOTE TO SELF: SQL Find inactive DBs

Bob Mihada, Tuesday, August 23, 2011

/*
Find DB activity for known list of DBs

ALL CREDIT goes to:
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

I merely tweaked for my own needs

-- You can adjust the script to select from sys.databases WHERE Name in (....). But 
-- as for me I like to keep a second listing in a management DB 

  CREATE TABLE [DBs]
    (
     [UID] [int] NOT NULL
                 IDENTITY(1, 1) NOT FOR REPLICATION
   , [DBID] [int] NULL
   , [DBName] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
                              NOT NULL
   , [Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
                          NOT NULL
   , [DateCreated] [datetime]
        NOT NULL
        CONSTRAINT [DF_DBs_DateCreated] DEFAULT (GETDATE()))
  ON
    [PRIMARY]

*/
DECLARE
		@DBName VARCHAR(128) --Database name	  
	  , @SQL VARCHAR(8000)	 
	  , @DBCount INT
	  , @DBCounter INT

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

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

CREATE TABLE ##tmpDBActivity(DBName sysname,last_read DATETIME, last_write DateTime)

SELECT
		ROW_NUMBER() OVER (ORDER BY DBName ASC) IDX
	  , DBName 
	 INTO
		#tmpDBs
	 FROM
		ManageDBs.dbo.DBs
				
SELECT @DBCount = @@ROWCOUNT, @DBCounter = 1
WHILE @DBCounter <= @DBCount
	BEGIN
		SELECT @DBName = DBName FROM #tmpDBs Where IDX = @DBCounter	
		
		SELECT @SQL = '
USE ' + QUOTENAME(@DBName,'[') + '
;WITH  agg AS
(
    SELECT
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
        AND OBJECT_SCHEMA_NAME([object_id]) = ' + QUOTENAME('dbo',CHAR(39)) + '
)
INSERT INTO ##tmpDBActivity
SELECT
	DB_NAME(DB_ID()) DBName
    ,last_read = MAX(last_read)
    ,last_write = MAX(last_write)
FROM
(
    SELECT  last_user_seek, NULL  FROM  agg
    UNION  ALL
    SELECT  last_user_scan, NULL  FROM  agg
    UNION  ALL
    SELECT  last_user_lookup, NULL  FROM  agg
    UNION  ALL
    SELECT  NULL, last_user_update FROM  agg
) AS  x (last_read, last_write);'
	EXEC (@SQL)
	SELECT @DBCounter = @DBCounter+1
	
	END

SELECT     
	DBName
  , last_read
  , last_write 
  , CASE WHEN last_read IS NULL AND last_write IS NULL 
		THEN 'INACTIVE' ELSE '' END STATUS
FROM 
	##tmpDBActivity	
ORDER BY last_read ASC

DROP TABLE ##tmpDBActivity

Results:

DBName	last_read	last_write	STATUS
CheeseAndRiceCo	NULL	NULL	INACTIVE
ChickenButtLLC	NULL	NULL	INACTIVE
FranksNBeansLTD	2011-05-11 10:48:28.197	NULL
BobCo	2011-06-09 10:46:30.923	NULL
BadSeafood	2011-07-07 12:01:21.137	2011-07-06 16:39:07.253
ManageDBs	2011-08-23 19:01:46.510	2011-08-23 19:01:46.510

SQL Warm Standby Pulse, synced via LogShipping

Bob Mihada, Saturday, August 20, 2011

--// SQL Warm Standby Pulse, synced via LogShipping
--// TODO: Create job to monitor age and send email alerts
--// ACKNOWLEDGED: yeah there's other built-in ways to accomplish this. 
DECLARE @THRESHOLD_HOURS INT
	
SELECT
    @THRESHOLD_HOURS = 8 ;
WITH    CTE
          AS (
              SELECT
                ROW_NUMBER() OVER (PARTITION BY rh.destination_database_name ORDER BY rh.restore_history_id DESC) Seq
              , rh.destination_database_name DBName
              , rh.restore_date LstRestoreDate
              , REVERSE(LEFT(REVERSE(physical_device_name),
                             CHARINDEX('\', REVERSE(physical_device_name)) - 1)) LogFileName
              , bs.backup_start_date LogFileDateCreated
              , bs.last_lsn
              FROM
                msdb.dbo.restorehistory rh (NOLOCK)
                LEFT JOIN msdb.dbo.backupset bs (NOLOCK) ON bs.backup_set_id = rh.backup_set_id
                LEFT OUTER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
             )
    SELECT
        DBName
      , CASE WHEN LogFileDateCreated < DATEADD(hh, @THRESHOLD_HOURS * -1,
                                           GETDATE())
             THEN '> ' + LTRIM(STR(@THRESHOLD_HOURS)) + ' HOUR !!'
             ELSE ''
        END [ACHTUNG]
      , DATEDIFF(hh, LogFileDateCreated, GETDATE()) [AGE(HOURS)]
      , LstRestoreDate
      , LogFileName
      , LogFileDateCreated
      , last_lsn
    FROM
        CTE
    WHERE
        Seq = 1
    ORDER BY
        DBName

Tasty note to self

DarkMagic With RedGate Virtual Restore and a folder full of Backups

VS2010 DB Compare fails.. Reverse Engineer my @$$

SQL : FIX ALL POTENTIALLY ORPHANED ACCOUNTS

Migrating to win2003 x64 with 32bit COM+ Application..TY Yigeulruseu

NOTE TO SELF: SQL Find inactive DBs

SQL Warm Standby Pulse, synced via LogShipping

Twitter / bobmihada

I use to bkite too

Facebook Badge

Tags

Archive

My Blog List

Del.icio.us

B O B M I H A D A