List all SQL Server backups for the previous 7 days

The following script will list all backups for every database for the previous 7 days, including backup type:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Source

Using SSD Drives for SQL Server

I’ve  just watched a great video explaining How to Optimally Use SQL Server with SSD’s Without Burning Them Out.

Here’s some notes:

  • 1 x SSD can give 4-5x the IOPS a 15k HDD gives. (I thought it would be more than this. Maybe because the video is from SQL Server Days 2011, and is a little out of date.)
  • The file allocation unit size (cluster size) recommended for SQL Server is 64 KB; this changes things when comparing IOPS stats from SAN. Ask for “Steady State IOPS”: http://calypsotesters.com/up-to-sustained-steady-state-ssd-performance
  • 4KB pages are standard on SSD
    • Smallest read/write structure.
  • Pages are grouped into blocks (128).
    • 512KB per block.
    • Smallest erasable structure
  • SSD write performance will degrade over time as it gets full.
  • SSD read performance stays roughly the same.
  • TRIM will help slow this down:
    • Check your OS supports TRIM by running this command
    • fsutil behavior query DisableDeleteNotify (should return 0)
    • If not 0, try to set it with this command:
    • fsutil behavior set DisableDeleteNotify 0
  • DO NOT defrag a SSD.
  • SSD typically don’t handle random writes very well.
  • Always use Enterprise SSDs, as they can last 10x longer than Prosumer SSDs.
  • Useful SMART tool is SSD Toolbox: www.intel.com/support/go/ssdtoolbox/index.htm
  • DO NOT use SSDs for TempDB, and maybe not even Log.
  • Base Solution – Tiered Infrastructure:
    • Eliminate bottlenecks:
      • Think 6Gbps and higher
      • Multilink
    • Use good controllers with Wear Levelling
    • Look at IOPS and response rates in Steady State
  • Problems with TempDB:
    • TempDB involves heavy Random writes.
    • Most used database
    • Enormous amounts of reads and erases
    • Kill (standard) SSDs extremely fast!
  • Log Files
    • Consider using Enterprise SSDs only, with RAID1 (mirrored).
    • Be careful with autogrowth.
    • Log files have more sequential reads and writes; more suited to HDDs.
  • Indexes
    • This is the best candidate for using SSD.
    • Place index filegroup on SSD.
    • Rebuild options that matter for SSDs:
      • SORT_IN_TEMPDB
      • MAXDOP=1 (this keep writes sequential)
      • COMPRESSION
  • Problem drive order of failure:
    • TempDB
    • Log
    • Data
  • Data Writes:
    • Logically view this as log entries being written and then used as instructions to write data pages out to the disk by an asynchronous process.
    • Heavily random access pattern; great for SSD.
  • Conclusion:
    • SSDs are not so good for sequential writes
    • SSDs are great for Random reads; indexes and Data files.
    • IO Volume matters, not just IOPS.

Recover a Corrupt Database Using SQL Server

Here’s an informative post explaining some options when recovering a corrupt database using SQL Server: http://www.sqlservercentral.com/articles/Corruption/96117/

Don’t forget to backup the tail of the log!

Here’s the process, including a few extra tips if the original server is unavailable: http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

Find remaining time left for Backup or Restore using SQL Server

Execute the following query:

SELECT r. session_id,r .command, CONVERT(NUMERIC (6, 2),r .percent_complete)
 AS [Percent Complete],CONVERT( VARCHAR(20 ),DATEADD( ms,r .estimated_completion_time, GetDate()),20 ) AS [ETA Completion Time],
 CONVERT(NUMERIC (6, 2),r .total_elapsed_time/ 1000.0/60.0 ) AS [Elapsed Min],
 CONVERT(NUMERIC (6, 2),r .estimated_completion_time/ 1000.0/60.0 ) AS [ETA Min],
 CONVERT(NUMERIC (6, 2),r .estimated_completion_time/ 1000.0/60.0 /60.0) AS [ETA Hours],
 CONVERT(VARCHAR (100),( SELECT SUBSTRING (text, r.statement_start_offset /2,
 CASE WHEN r.statement_end_offset = - 1 THEN 1000 ELSE (r. statement_end_offset-r .statement_start_offset)/ 2 END)
 FROM sys .dm_exec_sql_text( sql_handle)))
 FROM sys .dm_exec_requests r WHERE command IN ('RESTORE DATABASE' ,'BACKUP DATABASE')

or this one:

SELECT percent_complete, start_time, status, command , estimated_completion_time as est_complete,
 (estimated_completion_time / 1000/60 /60/ 24) as Days ,
 ((estimated_completion_time / 1000/60 /60) % 24 ) as Hours,
 ((estimated_completion_time / 1000/60 ) % 60) as Mins,
 ((estimated_completion_time / 1000) % 60) as Secs,
 cpu_time,
 ((total_elapsed_time / 1000/60 ) % 60) as 'Total Elapsed Mins'
 FROM sys .dm_exec_requests
 WHERE percent_complete > 0
 ORDER BY start_time DESC

Check last database restore date/time using SQL Server

Use one of the following scripts.

The first script is quick and simple:

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = DB_NAME()
ORDER BY restore_date DESC

The second script also shows the backup file used during the restore:

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Source:

Get Current Database Names and IDs in SQL Server

-- Get Current DatabaseID
SELECT DB_ID() DatabaseID;

-- Get Current DatabaseName
SELECT DB_NAME() DatabaseName;

-- Get DatabaseName from DatabaseID
SELECT DB_NAME(4) DatabaseID;

-- Get DatabaseID from DatabaseName
SELECT DB_ID('tempdb') DatabaseName;

-- Get all DatabaseName and DBID
SELECT name,database_id
FROM sys.databases;

Sourcehttp://blog.sqlauthority.com/2011/01/13/sql-server-a-quick-note-on-db_id-and-db_name-get-current-database-id-get-current-database-name/

Moving tempdb file location for SQL Server 2008 R2

Problem

You’ve ran out of disk space on the drive where the tempdb files are located.

Solution

You need to move the tempdb files to a new location with sufficient disk space.

  1. Execute the following query:
    USE master; GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = '[new_path]\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = '[new_path]\templog.ldf');
    GO
  2. Stop and restart the instance of SQL Server.
  3. Once the service has been restarted, verify the changes using this query:
    sp_helpdb tempdb

Reference

Setting Up Windows Service Accounts for SQL Server

A useful link explaining the permissions for built-in system accounts and recommended usage for SQL Server 2008: http://technet.microsoft.com/en-us/library/ms143504(v=sql.100).aspx

More recent information on Windows Service Account and Permissions for SQL Server 2012 can be found here: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Updating VMware vCenter from 5.0 to 5.1

Scenario

You currently manage your vSphere estate using vCenter 5.0, and would like to upgrade to the latest and greatest version to date; 5.1. There’s a few gotchas along the way, so read on below for a few useful notes. [Read more…]

SQL Server full and transaction log backups completing but differential backups failing because of Veeam B&R configuration

Scenario

I recently refreshed our SQL Server Maintenance Plans, but System Centre Essentials has been intermittently reporting failures of the daily differential backups, with an error like this:

Executing the query “BACKUP DATABASE [DBNAME] TO DISK = N’…” failed with the following error: “Cannot perform a differential backup for database “DBNAME”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

The strange thing is that the logs show all Weekly Full backups have completed without error. If I run a manual Full Backup, the next Differential Backup will complete, then the following day will fail.

Solution

First, I used the following query to show more details on the backups (make sure you select the DB in SSMS first):

[Read more…]