Quick and easy way to automate SQL Express database backups

August 6, 2015 Leave a comment

One of free SQL Server Express limitations is the lack of SQL Server Agent and, consequently, Maintenance Plans as such.

The easy way to automate regular database tasks, such as database backup would be to use internal windows scheduler and just a simple “ms dos”-like batch file.

First, the backup stored procedure has to be created in [master] database.


CREATE PROCEDURE Sp_dbbackup_user_and_system_databases

AS

BEGIN

DECLARE @db_bkp_location NVARCHAR(50)

DECLARE @today NVARCHAR(10)

DECLARE @bkpsql NVARCHAR(500)

DECLARE @db_name NVARCHAR(50)

DECLARE bkpcur CURSOR FOR

SELECT NAME

FROM   sys.databases

WHERE  NAME NOT IN ( ‘tempdb’ )

ORDER  BY NAME

SET @db_bkp_location = ‘E:\DBBackup’

SET @today = CONVERT(VARCHAR, Getdate(), 112)

SET @today = Substring(@today, 1, 4) + ‘_’

+ Substring(@today, 5, 2) + ‘_’

+ Substring(@today, 7, 2)

SET @bkpsql = 

SET @db_name = 

OPEN bkpcur

FETCH bkpcur INTO @db_name

WHILE @@fetch_status = 0

BEGIN

SET @bkpsql = ‘BACKUP DATABASE ‘ + @db_name + ‘ TO  DISK = ‘

+ ”” + @db_bkp_location + ‘\’ + @db_name + ‘_’ +

@today

+ ‘.bak’ + ””

+ ‘ WITH NOFORMAT, INIT,  NAME = ‘ + ””

+ @db_name + ‘-‘ + @today + ‘-Full Database Backup’

+ ”” + ‘, SKIP, NOREWIND, NOUNLOAD’;

–print @bkpsql

EXECUTE Sp_executesql

@bkpsql

FETCH bkpcur INTO @db_name

END

CLOSE bkpcur

DEALLOCATE bkpcur

END

Now the batch file

@echo off

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC master..sp_DBBackup_User_and_System_Databases”

Now what’s left is just to schedule it with standard windows scheduler.

Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks -> Add Scheduled Tasks, plug in the batch file name, pick up the recurrence you want and it’s good to go. Like it was one of those Maintenance Plan tasks or just plain SQL Agent jobs we’re all used to.

Drop / Recreate FKs

November 7, 2014 Leave a comment

Original at: http://www.codeproject.com/Tips/740561/SQL-Script-to-Drop-and-Re-create-All-Foreign-Keys

;WITH RefColumns AS

    (
SELECT
C.referenced_object_id AS [object_id],
C.parent_object_id,
STUFF((
SELECT ', ' + QUOTENAME(B.name)
FROM sys.foreign_key_columns A
JOIN sys.columns B ON B.[object_id] = A.referenced_object_id
AND B.column_id = A.referenced_column_id
WHERE C.parent_object_id = A.parent_object_id
AND C.referenced_object_id = A.referenced_object_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.foreign_key_columns C
GROUP BY C.referenced_object_id, C.parent_object_id
)
,
ParentColumns AS
(
SELECT
C.parent_object_id AS [object_id],
C.referenced_object_id,
STUFF((
SELECT ', ' + QUOTENAME(B.name)
FROM sys.foreign_key_columns A
JOIN sys.columns B ON B.[object_id] = A.parent_object_id
AND B.column_id = A.parent_column_id
WHERE C.parent_object_id = A.parent_object_id
AND C.referenced_object_id = A.referenced_object_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.foreign_key_columns C
GROUP BY C.parent_object_id, C.referenced_object_id
)
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name)                                                        AS [DropFKScript],
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +
'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +
'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' +
QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) +
CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) +
CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)                                                                            AS [CreateFKScript]
FROM sys.foreign_keys FK
JOIN sys.tables PT ON PT.[object_id] = FK.parent_object_id
JOIN ParentColumns AS PC ON PC.[object_id] = FK.parent_object_id
AND PC.referenced_object_id = FK.referenced_object_id
JOIN sys.tables RT ON RT.[object_id] = FK.referenced_object_id
JOIN RefColumns AS RC ON RC.[object_id] = FK.referenced_object_id
AND RC.parent_object_id = FK.parent_object_id
WHERE PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
AND
RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
--and (RT.name like 'Award%' or RT.name like 'MasterBreeder%' or RT.name = 'AverageRegistrationGroup')
AND RT.name LIKE 'Customer%'
ORDER BY PT.name

Damm Algorithm T-SQL Implementation

November 7, 2014 Leave a comment

Recently I ended up having to actually write my own function returning the Damm algorithm check digit in T-SQL. I was sure I would find some Damm algorithm T-SQL code snippet on the net, but no, – algo implementation code examples can be found in literally any main stream languages but T-SQL. Here comes its T-SQL version now.

Note 1:

I used input/output Old/New Number as a string to make it work with alpha-numerics of a certain format, – hence I have some padding and prefixing and postfixing of the New Number as well as fixed Old Number format, which is, again, a varchar. This can easily be changed to numerics only, with no prefixes, postfixes or any of that stuff, if needed – simply take it off and change the input/output data types

Note 2:

I used this wiki for the reference of how this algo works: http://en.wikipedia.org/wiki/Damm_algorithm

CREATE FUNCTION dbo.CheckDigitDammAlgo (@vOldNumber NVARCHAR(6))
RETURNS NVARCHAR(15)
AS
BEGIN
DECLARE
@cPaddingLength tinyint = 6,    --constant padding length
@cPadding NCHAR(6) = '000000',    --padding constrant
@cPrefix  NCHAR(3) = 'ZX_',        --prefix constrant
@cPostfix NCHAR(4) = '_712',        --postfix constrant
@vNewNumber NVARCHAR(20);        --New Number (output)
DECLARE @vDigit tinyint;                --each of six Old Number digits
DECLARE @vDigitIndex tinyint = 1;        --Old Number digit position (1 through 6)
DECLARE @vFlagDone bit = 0;                --the outer while-loop ending flag
DECLARE  @vOldInterim tinyint
,@vNewInterim tinyint
,@vColumnIndex NVARCHAR(10)
,
@vRowIndex tinyint
,@vTableEntry tinyint;
DECLARE @vArray TABLE (
interim tinyint
,zero tinyint
,one tinyint
,two tinyint
,three tinyint
,four tinyint
,five tinyint
,six tinyint
,seven tinyint
,eight tinyint
,nine tinyint
);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (0, 0, 3, 1, 7, 5, 9, 8, 6, 4, 2);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (1, 7, 0, 9, 2, 1, 5, 4, 8, 6, 3);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (2, 4, 2, 0, 6, 8, 7, 1, 3, 5, 9);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (3, 1, 7, 5, 0, 9, 8, 3, 4, 2, 6);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (4, 6, 1, 2, 3, 0, 4, 5, 9, 7, 8);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (5, 3, 6, 7, 4, 2, 0, 9, 5, 8, 1);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (6, 5, 8, 6, 9, 7, 2, 0, 1, 3, 4);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (7, 8, 9, 4, 5, 3, 6, 2, 0, 1, 7);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (8, 9, 4, 3, 8, 6, 1, 7, 2, 0, 5);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (9, 2, 5, 8, 1, 4, 3, 6, 7, 9, 0);
IF ISNUMERIC(@vOldNumber) = 0
BEGIN
RETURN
NULL;
END

--pad @vOldNumber with zeroes to six characters
SET @vOldNumber = RIGHT(@cPadding + RTRIM(@vOldNumber), @cPaddingLength)
WHILE @vFlagDone = 0
BEGIN
SET
@vOldInterim = 0 --first OldInterim is always 0 for any digit
WHILE @vDigitIndex <= @cPaddingLength
BEGIN
SET
@vDigit = CONVERT(tinyint, SUBSTRING(@vOldNumber, @vDigitIndex, 1));
SET @vColumnIndex = CASE @vDigit
WHEN 0 THEN 'zero'
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
END;
SET @vRowIndex = @vOldInterim;
SELECT  @vTableEntry = CASE @vColumnIndex
WHEN 'zero' THEN zero
WHEN 'one' THEN one
WHEN 'two' THEN two
WHEN 'three' THEN three
WHEN 'four' THEN four
WHEN 'five' THEN five
WHEN 'six' THEN six
WHEN 'seven' THEN seven
WHEN 'eight' THEN eight
WHEN 'nine' THEN nine
END
FROM    
@vArray
WHERE   interim = @vRowIndex;
SET @vOldInterim = @vTableEntry;
SET @vDigitIndex = @vDigitIndex + 1
END
SET
@vFlagDone = 1
END
SET
@vNewNumber = @cPrefix + @vOldNumber + CONVERT(VARCHAR, @vTableEntry) + @cPostfix;
RETURN @vNewNumber
END

SELECT dbo.CheckDigitDammAlgo ('1')
SELECT dbo.CheckDigitDammAlgo ('2')
SELECT dbo.CheckDigitDammAlgo ('3')
SELECT dbo.CheckDigitDammAlgo ('4')
SELECT dbo.CheckDigitDammAlgo ('5')
SELECT dbo.CheckDigitDammAlgo ('6')
SELECT dbo.CheckDigitDammAlgo ('7')
SELECT dbo.CheckDigitDammAlgo ('8')
SELECT dbo.CheckDigitDammAlgo ('9')
SELECT dbo.CheckDigitDammAlgo ('10')

Viewing index fragmentation

November 7, 2013 Leave a comment

All tables in a given database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),null,null,null,null );

Single given table in a database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),OBJECT_ID('[table_name]'),null,null,'DETAILED' );

I/O pressure

November 7, 2013 Leave a comment
-- Read
  SELECT --TOP 20 
	qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',
    qs.execution_count AS 'Execution Count',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',  
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes,  
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid 
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_physical_reads DESC

-- Write
   SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
    qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',  
    qs.execution_count AS 'Execution Count', 
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_logical_writes DESC

 

CPU Pressure

November 7, 2013 Leave a comment
SELECT TOP 50 
      SUM(qs.total_worker_time) AS total_cpu_time, 
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements, 
      qs.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

Most “expensive” queries

November 7, 2013 Leave a comment
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(qt.TEXT)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2)+1),
 qs.execution_count,
 qs.total_logical_reads, qs.last_logical_reads,
 qs.total_logical_writes, qs.last_logical_writes,
 qs.total_worker_time,
 qs.last_worker_time,
 qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
 qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
 qs.last_execution_time,
 qp.query_plan
 FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 ORDER BY qs.total_logical_reads DESC