Primary Key Script

The below script generates primary key across all the tables in the database.

;with CTE as (select distinct COLUMN_NAME,ORDINAL_POSITION,TABLE_NAME,b.name,c.index_id,
b.[object_id] as ObjectID,b.parent_object_id as [TableObjectID]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE A Inner Join sys.key_constraints B on
A.TABLE_NAME=Object_name(B.parent_object_id) and SCHEMA_NAME(SCHEMA_ID)=TABLE_SCHEMA
and A.Constraint_Name=B.Name
left outer join sys.indexes C on C.object_id=B.parent_object_id and is_primary_key=1)
,CTE2 as (select distinct T2.[objectid],T2.TableObjectID,T2.index_id,
STUFF((Select ','+Column_Name
from CTE T1
where T1.[objectid]=T2.[Objectid] and T1.[Tableobjectid]=T2.[TableObjectid]
and T1.[index_id]=T2.[index_id] order by ORDINAL_POSITION asc
FOR XML PATH('')),1,1,'') as [keycolumns] from CTE T2 )

Select distinct Table_Schema+'.'+Table_Name as [TableName],CONSTRAINT_NAME,'ALTER TABLE '+Table_Name +' ADD CONSTRAINT '+Name+ ' PRIMARY KEY '+
case when index_id=1 then ' CLUSTERED '
when index_id>1 then ' NONCLUSTERED '
when index_id=0 then '' End
+'('+keycolumns +');' as [Create Script]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE A Inner Join sys.key_constraints B on
A.TABLE_NAME=Object_name(B.parent_object_id) and A.CONSTRAINT_NAME=B.Name
Inner join CTE2 C on C.ObjectID=B.object_id and C.TableObjectID=B.parent_object_id

Backing up and Restoring single SSIS package

I was responding to a question on MSDN forums and part of the question was about how to backup the SSIS packages after they are deployed onto SQL Server. This is not related to SQL 2012 where SSIS packages can be deployed into it’s own SSISDB database as project model. This question was related to SQL Server 2008R2.

When SSIS packages are deployed, they are stored in MSDB database. So, MSDB database has to be restored if you need to recover the ssis packages. Of course, you should also have to put the ssis packages in a source control and have backups of it.

But an interesting point was raised – “is it possible to restore a single SSIS package”. The concerns mentioned were having to do full restore of msdb database.

I thought this is a real concern because having to restore msdb to recover a ssis package could lead loss of other important information such as backup\restore history etc. So, the question was – Is it possible to recover a single SSIS package without having to restore complete MSDB database?

So, to test this, I created a simple ssis package and deployed to sql server.
The below query returns the ssis packages deployed on the server.
Select * from msdb.dbo.sysssispackages

Now, I copied the data from msdb.dbo.sysssispackages into another table
Select * into newssispackages from msdb.dbo.sysssispackages

Now, I made another change to the SSIS package and redeployed. So, to recover the prior version of ssis package, first,I deleted the SSIS package from table

Delete from msdb.dbo.sysssispackages where name ='TestJob'
and then inserted the corresponding row from another table into dbo.sysssispackages.

Insert into dbo.sysssispackages
Select * from newssispackages where name ='TestJob'

This restored the prior version of SSIS into msdb. So, we can take table backup(Select * into) and revert back to previous versions as explained above.

I am not sure whether this works in every scenario and would be curious to know on scenarios where this won’t work.

Transaction Log Backups

Question:Does the transaction log backup contain the data from the last log backup or from the oldest active transaction?
Answer:Transaction Log backup contains log data from the last log backup.That’s why when we need to restore all the log backups in sequential order.Let’s verify using the below script.
--Session 1
Create database Dblog
go

--make sure database is in fully recovery
use DBLog
go
create table TableLog(sno int primary key identity(1,1),sname varchar(20),scity varchar(8000))
Go
begin tran
insert into TableLog values('sqlwhsiper',replicate('a',8000))

--do not commit yet.
--Commit
--sesssion 2

Backup database dblog to disk ='E:\Backup\dblog_FB.bak' with init

Backup log dblog to disk ='E:\Backup\dblog_log1.bak' with init

use DbLog
go
insert into TableLog values('sqlwhisper',replicate('a',8000))
go 10000

Backup log dblog to disk ='E:\Backup\dblog_log2.bak' with init

--Session 1. Commit the open transaction in session 1.
Commit

Backup log dblog to disk ='E:\Backup\dblog_log3.bak' with init

--Read the transaction log backup .The second log backup does not contain any reference to the active transaction in the first backup file
SELECT *
FROM
fn_dump_dblog (
NULL, NULL, N'DISK', 1, N'E:\Backup\dblog_log2.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where [operation] like '%INSERT%'
GO

--Read the transaction log backup 3.The third log backup contains any reference to the active transaction in the first backup file saying it was commited.
SELECT *
FROM
fn_dump_dblog (
NULL, NULL, N'DISK', 1, N'E:\Backup\dblog_log3.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where [operation] like '%INSERT%'
GO

--clean up
use master
go
drop database dblog

Migrating Log Shipping Database

Log Shipping is one of the HADR solution available in SQL Server and it works in a simple way – take log backup on the primary database and restore it on the secondary database. There can be more than one secondary databases. All this can be scheduled using SQL server agent jobs and even if the feature is not available is directly, it should be fairly easy to set one up in a traditional way.

I recently had to move one of our log shipping primary database to a different database server. Below are the steps, I followed the below steps to eliminate the need to reconfigure the log shipping from scratch.

Scenario :
Database – TESTLOGSHIP
Database Server : Mercury – Primary Log shipping ;
Database Server : Venus – Secondary Log shipping
There is no witness server configured.

Now, I need to migrate ‘TestLogship’ database to new database server ‘EARTH’ and configure it as log shipping primary.

1.Take full backup on the mercury server for ‘testlogship ‘ database.

2.Restore the full backup on the Earth database server in recovering mode.

3.Run select * from msdb.dbo.restorehistory where destination_database_name=’testlogship’ on the venus database server, to find which was the last log backup applied.

4.Make sure none of the log shipping jobs(backup\copy\restore) are running and disable them.

5.If there are any log backups taken but not applied on to Venus database server – apply them. This can be tracked by checking backupset table on Mercury and comparing it with restorehistory table on Venus.

6.Check if there are any log backups taken after the full backup(step1) was taken and restore them on database server Earth in ‘recovering’ mode.

7. Make sure both the Earth and Venus, database servers have same log backups applied. You can verify this by checking ‘select * from msdb.dbo.restorehistory where destination_database_name=’testlogship”.

8.Restore the database on earth with recovery.

9. Right click on the database ‘testlogship’ on the EARTH server and –tasks — ship transaction logs and configure log shipping, like you normal would but choose ‘secondary database is initialized option’ and everything else would obvious, if you have configured log shipping before.

NOTE: One easy way to do this is instead of restoring all the log backups, you can stop the log shipping jobs and take differential backup on the primary ‘Mercury’ and restore the differential backups on both ‘Venus and ‘Earth’ and then continue from step 8 above.

How much time does Restore VerifyOnly take?

While,it is important to verify the database backups by doing a complete restore,it might not be possible to do that in every scenario. So, in such cases, the next best thing is to do “Restore VerifyOnly”. Restore VerifyOnly does not actually restore the database but will validate the check sum, if enabled on the backup and restore some of the data page headers and will try to make sure the backup is readable and restorable as much as possible but cannot be guaranteed.

For some of our backup jobs, we have “Restore VerifyOnly” set up in the same job step as the backup. This means that SQL Agent job step runs till the database backup is taken and restore verify process has completed. Now, if we want to have a break down on how much time was spent to backup the database and verify the backup individually, we need to subtract the database backup time from the job run time to get the time taken to perform restore verify. All the backup information is available in MSDB database in backupset table. We can use msdb.dbo.backupset table to calculate the total time the backup has taken.

The backup job in this case is Full Backup job that runs once a day. So, it is easier to join the database backup with sql agent job info on the date column.

declare @databasename varchar(200)
set @databasename =
mydatabase
declare @JobName varchar(200)
set @JobName=
mybackupjobname

Select A.database_name as [DatabaseName],B.Name as [JobName],B.run_date as [Job_RunDate],B.Run_time as [Job_RunTime],
B.[JobRunTimeINMins] as [TotalJobRunTime_In_Mins],BackupSize_In_GB,[BackupTime_In_Mins],[BackupType],backup_start_date,backup_finish_date,
(JobRunTimeINMins -[BackupTime_In_Mins]) as RestoreVerify_Time_In_Mins
from
(select top 100 percent database_name,cast(backup_size/1024/1024/1024 as decimal(8,3)) as BackupSize_In_GB,Case WHEN type='D' then 'FULL' WHEN type='L' then 'Log' When type='I' then 'Diferential' End as [BackupType],
backup_start_date,backup_finish_date, datediff(minute,backup_start_date,backup_finish_date) as [BackupTime_In_Mins]
from msdb.dbo.backupset where database_name= @databasename and type='D' and Backup_start_date >=dateadd(month,-6,getdate())
order by backup_set_id desc) A INNER JOIN
(select top 100 percent B.Name,A.run_date,A.Run_time,
(cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),1,2)*3600 as int)+
Cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),3,2)*60 as int)+
Cast(SUBSTRING(right('000000'+cast(run_duration as varchar(6)),6),5,2) as int))/60 as [JobRunTimeINMins]
from msdb.dbo.sysjobhistory A INNER JOIN msdb.dbo.sysjobs B on A.job_id=B.job_id
where step_id=0 and B.name=@JobName
order by instance_ID desc) B on B.run_date=convert(varchar(8),A.backup_start_date,112)
order by run_date desc

SQL Server Audits

SQL Server has built in capability to audit certain user actions both on server level and database level. This is a useful feature as it helps to log user actions such dropping objects,changing permissions on the objects\database etc without having to build a customize auditing solution.
--Query to find the events logged by Server Audit.
select a.Audit_ID as [ServerAuditID],A.Name as [ServerAuditName],B.Name as [ServerAuditSpecificationName],C.Audit_action_id as [Audit_ActionID],C.Audit_action_Name as [Audit_ActionName],D.action_id as [Sub_ActionID],D.Name as [Sub_ActionName],E.database_name,statement,object_name
from sys.server_audits A
INNER JOIN sys.server_audit_specifications B on A.audit_guid=B.audit_guid
INNER JOIN sys.server_audit_specification_details C on B.server_specification_id=C.server_specification_id
INNER JOIN sys.dm_audit_actions D on D.covering_action_name collate SQL_Latin1_General_CP1_CI_AS=C.Audit_Action_Name
INNER JOIN fn_get_audit_file('C:\Users\sqlwhisper\Documents\SQL\ServerAudit_12663303-53EF-4FFD-BBC5CCC0AA4EE477_0_130711508902400000.sqlaudit',default,default) E
on E.action_id=D.action_id

SSIS Packages in SQL 2012/14

From SQL 2012 onwards, ssis packages can be deployed in project model. This requires creating a catalog and enabling ‘CLR’ integration. Once the packages are deployed in project model, they can be browsed in the ‘Integration Services Catalog’ in the management studio. Below code retrieves the packages along with the folder and packages present in the integration services catalog.

select A.name as [FolderName],B.name as[ProjectName],C.name as [PackageName]
from [catalog].[folders] A
INNER JOIN [catalog].[projects] B ON A.folder_id=B.folder_id
INNER JOIN [catalog].[packages] C on C.project_id=B.project_id

Query to retrieve column information using catalog views

The below query helps to retrieve information on columns in a table:

Select A.Name,B.Name,D.name,A.max_length,A.precision,
A.is_identity,A.is_nullable,E.[definition]
from sys.columns A inner join sys.tables B on A.object_id=B.object_id
inner join sys.schemas C on C.schema_id=B.schema_id
inner join sys.types D on D.user_type_id=A.user_type_id
left outer join sys.default_constraints E on E.parent_column_id=A.column_id and A.object_id=E.parent_object_id
where C.name='<<SchemaName>>' and B.name='<<TableName>>'

sp_columns system stored procedure can also be used to get the information.

System Stored Procedures

There are several catalog views that can be used to query database related info. We also have system stored procedures to get same basic information.

Some of the commonly used one’s are:
1.sp_helpdb
2.sp_help
3.sp_helpfile
4.sp_databases
5.sp_tables: list the table names
sp_tables @table_owner='HumanResources',@table_type="'Table','view'"
6.sp_columns: lists the column names and types and other columns details
sp_columns @table_owner='Person',@table_name='Person'
7.sp_pkeys: lists the primary key of the table
sp_pkeys @table_owner='Person',@table_name='Person'
8.sp_fkeys: lists all the tables where the key is referred.
sp_fkeys @pktable_name='Employee',@pktable_owner='HumanResources'