DarkMagic With RedGate Virtual Restore and a folder full of Backups

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,'
','') 
 
 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 @$$

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

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

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

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

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

SQL install woes, service failed to start, retry???

Scenario: New server in colo, 500 miles away. Network Admin inadvertently installed SQL 2005 developer edition. Hands me the keys.

Upon discovering the sql version, I informed him of the issue and began uninstalling, with dismay.

Last time something like this happened we had to wipe HD and reinstall the OS and then intall correct SQL version. But, this time I found the nugget that escaped me prior.

Towards the tail end of the new install, MSSQL x64 Enterprise, I got a popup saying service failed to start, retry or cancel. Battle then begins. Unfortunately this install was put on back burner till we hours of night, due to productions issue during the day. So it took me 3 night to finally resolve. Picking through the logs I found, (sqlservr!DmpGetClientExport) and googled and found: My savior

I did as advised, overwrite sqlserver.exe and SQLOS.dll, from another sql install, pulled from another server already running,  and clicked retry.

So random, yet that WORKED. What a headache, but thank you Satay, for posting. Orz

~enjoy

SQL install woes, service failed to start, retry???

Scenario: New server in colo, 500 miles away. Network Admin inadvertently installed SQL 2005 developer edition. Hands me the keys.

Upon discovering the sql version, I informed him of the issue and began uninstalling, with dismay.

Last time something like this happened we had to wipe HD and reinstall the OS and then intall correct SQL version. But, this time I found the nugget that escaped me prior.

Towards the tail end of the new install, MSSQL x64 Enterprise, I got a popup saying service failed to start, retry or cancel. Battle then begins. Unfortunately this install was put on back burner till we hours of night, due to productions issue during the day. So it took me 3 night to finally resolve. Picking through the logs I found, (sqlservr!DmpGetClientExport) and googled and found: My savior

I did as advised, overwrite sqlserver.exe and SQLOS.dll, from another sql install, pulled from another server already running,  and clicked retry.

So random, yet that WORKED. What a headache, but thank you Satay, for posting. Orz

~enjoy

More notes to self: network bucket too small and a funny quote

The bucket effect: When bucket gets full, everything is dumped out and bucket begins refilling, where the bucket is the network connection/transmission. In effect you are able to reach the destination, but before the round-trip can occur, all communications appear to get severed. In this scenario, if other short requests(small packets) are getting through, like a web request for data, yet longer requests such as file transfers, SSH/RDP sessions, are dropping out, look for recent network. Don't assume something like a firewall or managed switch couldn't possibly be the culprit, just because you, yourself or team, didn't initiate a change request(Active/Ative? WTM?). Meh. Also don't assume everyone has already checked on these or any other obvious things. Force an acknowledgment by the group.

Funny quote during vendor site-visit:

"Before we make any changes we'll need to take a dump."

I almost lost it :-P. Vendor was actually referring to a snapshot/backup of a database before acting upon it, in case a rollback is required.

note to self: SQL log shipping stats/graphs

Results of query coupled with google spreadsheets w/ timeline graph applied, equate to pretty data worth sharing.

--// View last log backups per half hour, total size(MB), with number of logs
SELECT TOP 500
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DT30
  , SUM(backup_size / 1024 / 1024) SizeMB
  , COUNT(DISTINCT media_set_id) TrnCount
FROM
    msdb..backupset (NOLOCK)
WHERE
    type = 'L'
GROUP BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0)
ORDER BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DESC

Note: can easily work for Data backups too, just change "type = 'L'" to "type = 'D'"

~Enjoy

note to self: SQL Disk summary

Script to show current Disk space free, used, by drive. And then a summary break down of DB files, folders and space used.

SET NOCOUNT ON
EXEC master.dbo.xp_cmdshell 'wmic LOGICALDISK LIST BRIEF';

IF OBJECT_ID('tempDB..##tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles
CREATE TABLE ##tmpDatabaseFiles
    (
 [DBName] [sys].[sysname]
   ,  [file_id] [int]
   , [file_guid] [uniqueidentifier]
   , [type] [tinyint]
   , [type_desc] [nvarchar](60)
   , [data_space_id] [int]
   , [name] [sys].[sysname]
   , [physical_name] [nvarchar](260)
   , [state] [tinyint]
   , [state_desc] [nvarchar](60)
   , [size] [int]
   , [max_size] [int]
   , [growth] [int]
   , [is_media_read_only] [bit]
   , [is_read_only] [bit]
   , [is_sparse] [bit]
   , [is_percent_growth] [bit]
   , [is_name_reserved] [bit]
   , [create_lsn] [numeric](25, 0)
   , [drop_lsn] [numeric](25, 0)
   , [read_only_lsn] [numeric](25, 0)
   , [read_write_lsn] [numeric](25, 0)
   , [differential_base_lsn] [numeric](25, 0)
   , [differential_base_guid] [uniqueidentifier]
   , [differential_base_time] [datetime]
   , [redo_start_lsn] [numeric](25, 0)
   , [redo_start_fork_guid] [uniqueidentifier]
   , [redo_target_lsn] [numeric](25, 0)
   , [redo_target_fork_guid] [uniqueidentifier]
   , [backup_lsn] [numeric](25, 0)
   , [CreationDate] [datetime])
 
 EXECUTE sp_msforeachdb 'INSERT INTO ##tmpDatabaseFiles SELECT ''[?]'', *, GETDATE() FROM [?].sys.database_files'
  
SELECT COUNT(DISTINCT dbname) NonSystemDBCount from ##tmpDatabaseFiles WHERE dbname NOT IN ('[master]','[model]','[tempdb]', '[msdb]')

 ;WITH CTE AS (
 SELECT
 DISTINCT
   name
 , LEFT(physical_name, LEN(physical_name)-CHARINDEX ('\', REVERSE(physical_name)) ) Folder
 ,(size * 8)/1024.0 size
 ,type_desc
  FROM ##tmpDatabaseFiles 
)
SELECT 
 LEFT(Folder,60) Folder
 ,LEFT(LTRIM(STR(COUNT(Folder))),5) Files
 ,LEFT(LTRIM(STR(SUM(size)/1024.0)),10) [GB TotalSize]
 ,LEFT(type_desc,10) FileType
FROM CTE
GROUP BY Folder,type_desc
ORDER BY Folder

IF OBJECT_ID('tempDB..#tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles

Set compatibility to SQL 2005

--// Note To Self

--// Set compatibility to SQL 2005
SELECT
    ' ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE
  exec sp_dbcmptlevel ' + name + ', 90'
FROM
    sys.databases
WHERE
    compatibility_level < 90

Trying to make time to get at root performance issues with SQL

Poor DB design coupled with poor queries, dynamic sql even can lead to crippling performance issue.  Things aren't "crippling", for me at the moment,  but theres still a lot that can be done. So where to start?

Well we need something to work with first. So go get some, PerfStatsScripts,  SQLDiag. Then feed it to SQL NEXUS. That should eventually lead to managing your indexes, whether missing, suggested or redundant. Another nugget, comes from K.Tripp Orz, sp_helpIndex. Visibility into what your tables have on them now. Hopefully your get into a grove where things will start to just fall into place. Said query is sucking life out of SQL. Its referencing, Table A and Table B. Review suggestions and view what there now. Make a  decision and review the results. Rinse wash repeat. I don't expect to end up with a clear glass, but hopefully this would have scrapped the scum off the surface and removed all the big particles and hair, eww.

Ready...Break!

Quick perf toolkit:
http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx
~ Kimberly Tripp (so you know its legit)

SQL Nexus Tool + SQLDiag(already included) + SQL Server 2005 Performance Statistics Script

Commercial approach

Idera SQL Doctor

~Throw me a bone so I may chase something worth chasing

-----

Goober: WHY IS IT DOING A TABLE SCAN? No matter what I do it just keeps doing a table scan? Update Stats? Restart SQL?

Bob: Eh, what index are you expecting to see used?

Goober: ...

Goober: .......

Goober: I hate you!!!

Goober: There was no index. I need to go talk to some people.

Bob: Super. Take care now, bye-bye then.