Create database from just an MDF file

In a situation where you may have no backup of a database, but you have the data file (the .mdf) but no log file (.ldf), the following SQL script can be used to create a database using just the .mdf data file:

CREATE DATABASE DatabaseName

ON (FILENAME = '<path to your file here>\DatabaseName_Data.mdf')

FOR ATTACH_REBUILD_LOG;

Sometimes additional steps are required, as documented here: https://www.mssqltips.com/sqlservertip/3579/how-to-attach-a-sql-server-database-without-a-transaction-log-and-with-open-transactions/

Get Backup History

Use the follwing SQL:

USE
DatabaseName

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


Taken from Pinal D:  http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/

Listing Stored Procedures used by SSRS

The following SQL will allow you to list all stored procedures used by Reporting Services reports. Run this against the ReportServer database on the SQL Server your Reporting Services installation uses:

;with xmlnamespaces

(

default

'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

)

select

       name

       ,x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType

       ,x.value('CommandText[1]','VARCHAR(50)') AS CommandText

from (

       select

              name

              , cast(cast(content AS VARBINARY(MAX))as xml) as reportXML

       from

              ReportServer.dbo.Catalog

       where

              Name not like '%.gif'

              and Name not like '%.jpg'

              and Name not like '%.jpeg'

) a

cross apply reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)

where

       x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

Note, the 4th line, it may be necessary to change "2008" to "2003", "2005", "2010" depending on the version(s) of Visual Studio / Report Builder / BIDS, SSDT you've used to create your reports.

Credit to Jacob Sebastian, I used his post as a basis here:
http://beyondrelational.com/modules/2/blogs/28/posts/10446/how-to-find-all-stored-procedures-used-by-report-server.aspx

RAID Disk Levels

Google definition: RAID (originally redundant array of inexpensive disks; now commonly redundant array of independent disks) is a data storage virtualsation technology that combines multiple disk drive components into a single logical unit for the purposes of data redundancy or performance improvement.

There are a number of common RAID array configurations:

RAID 0:

This is also known as "striping" or a stripe of disks. Data is distributed (or "striped") across a number of drives, without any copy of the data, or any parity information about the data.

Disks: Minimum 2 disks required. Disks are striped, no mirror, no parity
Good for: performance - it is very quick to read and write to
Bad for: redundancy - if a disk fails, you've lost your entire RAID array. No good for critical systems

RAID 1:

This is also known as "mirroring" or a mirror of disks. The same data is written to two disks. Should one disk fail, you still have a complete copy of the entire disk on the other mirrored disk.

Disks: Minimum 2 disks required. Disks are mirrored, no stripe, no parity
Good for: redundancy - if a disk fails, you still have a copy of the entire disk.
Bad for: useable disk space - 2 x 1TB disk drives configured as RAID 1 only gives you 1TB of useable disk space

RAID 5:

This is the best cost effective option for both performance and redundancy

Disks: Minimum 3 disks required. Disks are striped, with distributed parity
Good for: Read performance due to striped disks. Also good for redundancy - if a disk fails, the data can be recovered based on the parity information
Bad for: write speeds can be slow

RAID 10:

Also known as RAID 1 + 0, or a "stripe of mirrors", it combines both RAID 1 and RAID 0.

Disks: Minimum 4 disks required. Disks are striped, and mirrored
Good for: Performance due to striped disks. Good for redundancy due to mirrored disks. The best option for critical systems (especially databases)
Bad for: cost - the most expensive option

Resources:

PCWorld post on RAID: http://www.pcworld.com/article/194360/raid-made-easy.html
The Geek Stuff: http://www.thegeekstuff.com/2010/08/raid-levels-tutorial/
There are some additional less common RAID levels (2,3,4,6), details about these here: http://www.thegeekstuff.com/2011/11/raid2-raid3-raid4-raid6/

Transparent Data Encryption (TDE)

Transparent Data Encryption (or TDE) is a means for encrypting your entire database, transparently. It was introduced from SQL Server 2008. It is transparent to the applications, as SQL Server does the encryption / decryption of data on the fly as required, there is no additional configuration required for your applications to make use of a database encrypted using TDE. Once TDE is set up, the data will be encrypted on the disk, and any backups of the data will be encrypted also, and you'll be unable to restore the data without the encyption key.

Setting up TDE:
There are a few steps to set up TDE for your SQL Server database(s):

1. Create a Master Key (on the master database)
2. Create a Certificate with the Master Key (on the master database, and back this certificate up)
3. Create Database Encryption Key (on the database you want to use TDE on)
4. Turn on encryption on the required database
5. Repeat steps 3 & 4 for any other databases you require TDE for

SQL for these steps (taken from MSDN):

use master;

go

create master key encryption by password = '<UseStrongPasswordHere>';

go

create certificate MyServerCert

with

       subject = 'My DEK Certificate';

go

use AdventureWorks2012;

go

create database encryption key

with algorithm = aes_128

encryption by server certificate MyServerCert;

go

alter database AdventureWorks2012 set encryption on;

go

Moving a TDE Encrypted database:

If a database using TDE is required to be restored to another server, the following steps must be followed:

1. Via Windows Explorer, copy TDE encrypted database, backup of server key and certificate to destination server
2. Create database master key on destination server
3. Recreate server certificate on destination server using backup from origin server
4. Restore / attach TDE encypted database

Resources:

Recover database without database encryption key:
http://dba.stackexchange.com/questions/34753/can-i-recover-a-tde-certificate-by-restoring-the-master-database

MSDN - set up / move TDE encrypted database: https://msdn.microsoft.com/en-gb/library/ff773063.aspx

SQLIO & SQLIOSIM

SQLIO is a lightweight command line application from Microsoft, to test your disk I/O and get some performance benchmarking figures.

Resources:
Brent Ozar blog post about SQLIO: http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
Download SQLIO from Microsoft: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=20163

SQLIOSIM is not a performance benchmarking tool, but more of a stress testing tool, due to it's use of random patterns. Unlike SQLIO, SQLIOSIM has a graphical user interface. SQLIOSIM will test what kind of I/O you'll get from a database based on where you'll put your data and log files, auto grow configuration, whether or not you're using sparse files etc

Resources:
Download SQLIOSIM from Microsoft: https://support.microsoft.com/en-us/kb/231619

Dynamic Management Views (DMVs)

Information about some of the DMVs availabile in SQL Server, and their function

Sessions

sys.dm_exec_sessions
Information about successful and unsuccessful logins

sys.dm_exec_connections
Provides information about connections established to the Database Engine instance

sys.dm_exec_requests
Information about each request executing on the SQL instance

sys.dm_exec_cursors
Information about cursors open

Audits

sys.dm_audit_actions
Information about every audit action that can be reported in the audit log as well as every audit action group that you are able to configure as part of SQL Server Audit

sys.dm_server_audit_status

Information about the current state of auditing

sys.dm_audit_class_type_map

Information about the class_type field in the audit output

fn_get_audit_file

Information from an audit output file that has already been generated by a server audit

Events

sys.server_event_sessions 

Lists all the event session definitions configured for the Database Engine instance

sys.server_event_session_actions

View actions on each event on an event session

sys.server_event_session_events 

View each event in an event session

sys.server_event_session_fields 

View each customizable column set on events and targets

sys.server_event_session_targets

View each event target for a specific event session

I/O

sys.dm_io_pending_io_requests

Provides information on unfulfilled I/O requests

sys.dm_io_backup_tapes 

Information on tape devices and mounts requests for backups

sys.dm_io_cluster_shared_drives

Information on shared drives if the host server is a member of a failover cluster

sys.dm_io_virtual_file_stats

Information on I/O statistics for data and log files

Deadlocks

sys.dm_tran_locks

Information on active locks

sys.dm_os_waiting_tasks

Information on tasks waiting on resources

sys.dm_exec_requests

Requests that are executing within SQL Server