SQL - Unable to open SSMS

We had a call in for a third party to access a SQL server - the people who set the server up had not long left the Company and didn't leave any passwords for us.

Logging onto the server was fine. Opening up SSMS was the problem. No Permissions.

One of my colleagues found this useful nugget of information for SQL 2008 and above (I don't know how far above - the article said 2008 and above and the edition we have is SQL 2008 R2).

Using PSEXEC (Sysinternals) with the following syntax and an elevated CMD prompt you gain access to SSMS

# PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\<path-to-Ssms.exe>\Ssms.exe"

(Path will vary depending on version!)

PsExec will run interactively (-i) using the system account (-s).

Kudos obviously goes to the creator of the original link:
http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

SQL - T-SQL How long does the DB backup take

Found this useful query with regards looking for how long the SQL backup takes. Baselining the backups to spot when you started having problems.

USE <DBNAME>
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

SQL - T-SQL for last Full and Log Backup

I wanted a script to be able to list when the last backup occured on the SQL Databases that we had to check first thing as part of morning checks.

I found a number of scripts but this one met my needs:

SELECT a.name AS [Database Name],a.recovery_model_Desc AS [Recovery Model],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'D' and a.name=b.database_name) AS [Full Backup],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'L' and a.name=b.database_name) AS [Log Backup]
FROM master.sys.databases a

As you can see from the screen shot below of a brand spanking new SQL Express install the output also gives the Recovery Model and well as Full and Log backups.