View Database Backup History

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
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


-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name

-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
A.[Server],
A.database_name,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A

LEFT JOIN

(
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,
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 msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name

Free Space on Drives with T-SQL

EXEC master.sys.xp_fixeddrives

Presentation Links: SQL Server Performance Tuning (Quest)

http://blogs.msdn.com/b/buckwoody/archive/2010/07/21/presentation-links-sql-server-performance-tuning-quest.aspx

Grant create table and SP for schema

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CSODMGMT\SQLREWR')
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go


----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

set @string ='GRANT EXECUTE ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string



FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name
GO

sp_msforeachdb

sp_msforeachdb
'
if ''?'' like ''ces_%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'

Find Currently Running Query

-- ALL Sessions
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

-- SPECIFIC SESSION DETAIL
dbcc inputbuffer(59)

Implementing the OUTPUT Clause in SQL Server 2008

http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/?utm_source=simpletalk&utm_medium=email-main&utm_content=OutputClause-20100726&utm_campaign=SQL

Batch File Commands

From the old days, sometimes batch file becomes very handy

http://academic.evergreen.edu/projects/biophysics/technotes/program/batch.htm

sp_MSforeachtable

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount order by rowcnt desc
select * from #rowcount
drop table #rowcount

Transparent Data Encryption in SQL Server 2008

http://www.sql-server-performance.com/articles/dba/Transparent_Data_Encryption_p1.aspx

http://www.mssqltips.com/tip.asp?tip=1507

http://msdn.microsoft.com/en-us/library/bb677274.aspx


You encounter SQL Server error 17308 after you enable TDE on one of your databases in SQL Server