SQL Server database backup automation using T-SQL

Posted on June 5, 2013 by uday arumilli

robot with computer and stethoscope

SQL Server database backup automation using T-SQL

I have gone through a request as a part of daily maintenance. Found a backup maintenance plan has been failed due to low disk space.

 “SQL Task     Description: Executing the query “BACKUP DATABASE CA1011_DB TO DISK = …” failed with the following error: “A nonrecoverable I/O error occurred on file “C:\Backup\ CA1011_DB \ CA1011_DB _backup_2013_06_05_010000_7196421.bak:” 112(There is not enough space on the disk.).  BACKUP DATABASE is terminating abnormally.”

 We have designed a maintenance plan that performs backup for all selected databases, once after backup a task added to cleanup the backup files. But the problem is while performing backup all previous day backups are still available on folders and those can be deleted only after backup job completed.

 So for all databases if the total backup size is 200 GB we exactly need 400GB free space on drive. To avoid these issues we have created a T-SQL script which performs backup and clean up the old backup.

 Existing Maintenance Plan:

 Step1: Perform backup for Database-1

Step2: Perform backup for Database-2
—————————————————-
—————————————————-
StepN: Perform backup for Database-N
StepN+1: Delete the previous day backup from all folders

 

New T-SQL Script:

SQL Server database backup automation using T-SQL

 Step1: Perform backup for Database-1

Step2: Deletes previous day backup for Database-1

Step3: Perform backup for Database-2

Step4: Deletes previous day backup for Database-2

—————————————————-

—————————————————-

StepN: Perform backup for Database-N

StepN+1: Deletes previous day backup for Database-N

 Below is the script to perform a backup and deletes previous days backup.

 

USE MASTER;
GO

IF EXISTS(SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = 'usp_Backup_Database' AND TYPE='P')
BEGIN
	DROP PROCEDURE [usp_Backup_Database];
END
GO
CREATE PROCEDURE usp_Backup_Database (
				@db_nameNVARCHAR(50),
				@file_pathNVARCHAR(256))
AS
BEGIN
SET NOCOUNT ON
DECLARE	@fileName	NVARCHAR(256);-- Filename for backup
DECLARE	@fileDate	VARCHAR(20);-- Used for file name
DECLARE	@DeleteDate	DATETIME = GETDATE();-- Cutoff date

-- Get date to include with the file name.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);

-- Build the file path and file name.
SET @fileName = @file_path + @db_name + '_' + @fileDate + '.BAK';

-- Backup the database.
BACKUP DATABASE @db_name TO DISK = @fileName WITH INIT;

-- Delay 10 sec
WAITFOR DELAY'000:00:10';

-- Purge old backup files from the disk.
EXEC master.sys.xp_delete_file 0,@file_path,'BAK',@DeleteDate,0;

END
GO

Now execute the stored procedure:

 Parameters:

@db_name: Database name that needs to be backup

@file_path: File path where backup needs to be stored.

 Note: File path format should be “:\…………\Backup\”

 Procedure execution:

USE MASTER;
GO
EXEC usp_Backup_Database 
			@db_name = N'Source',
			@file_path = N'C:\Bkptest\Source\';
GO

This is really helpful when we need to deal with disk space.

Just to demonstrate I have shown an example with one database that same can be loop for all user defined databases using a table variable and while loop.

 

Posted in SQL Server DBA | Tagged , , , , , , , | 1 Comment

How to Delete Duplicate Records in SQL Server

Posted on May 7, 2013 by uday arumilli

How to Delete Duplicate Records in Sql Server
How to Delete Duplicate Records in SQL Server

Usually on daily basis we usually need to perform removing duplicate records from a table. This post can help you to understand “How to Delete Duplicate Records in SQL Server”.

Here consider an example for removing duplicate records.

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE TYPE = 'U' AND NAME = 'Test')
BEGIN
	DROP TABLE Test; 
END;
GO
CREATE TABLE Test(id INT,name VARCHAR(100),age INT);
GO
INSERT INTO Test
VALUES (1,'A',34),
(1,'A',34),
(2,'B',37),
(3,'C',21),
(3,'C',21),
(3,'C',21);
GO
SELECT id,name,age FROM Test;

 

-- Now delete the duplicate records
WITH CTE(id,name,age,Duplicates)
AS
(
SELECT id,name,age,
ROW_NUMBER() OVER (PARTITION BY id, name, age ORDER BY id) AS Duplicates
FROM Test
)
DELETE FROM CTE
WHERE Duplicates > 1
GO

 

Now check the table to make sure duplicates are being removed from table.

Posted in SQL Development, SQL Scripts | Tagged , , , , , , , , | Leave a comment

How to split a string in sql server

Posted on May 1, 2013 by uday arumilli

 UdayarumilliSplitstringHow to split a string in sql server

We have a requirement to capture the list of key words in the given list of statements. In the below example will see How to split a string in sql server

There is a table called “SearchQueries”. It’s been having a list of queries, now the requirement is to split each and every statement into individual words and needs to be stored in a table “KeyWords”.

 Now the requirement is to split and store keywords from each record as below along with the number of occurrences.

 Example:

KeyWord       Count

Describe           12

Change              2

Data                  10

Capture              1

Etc…….

Script to create table and populate with data:

USE udayarumilli;
GO
CREATE TABLE SearchQueries(ID INT IDENTITY,Query NVARCHAR(MAX));
GO
INSERT INTO SearchQueries(Query)
SELECT'How to design 2012 SSRS reports using SQL Server Data Tools'
UNION
SELECT'What are the new features added in SSRS 2012'
UNION
SELECT'Describe Change Data Capture for SSIS in SQL Server 2012'
UNION
SELECT'What are the new features added in SSIS 2012'
UNION
SELECT'SSIS 2012 Support for Variable Number of Columns in a Flat File'
UNION
SELECT'SSIS Package Format Changed and the Specs are Open Source New in SSIS 2012'
UNION
SELECT'What are the new features added in SSAS 2012';
GO

Create a table to hold the keywords:

CREATE TABLE KeyWords(ID INT IDENTITY,Word NVARCHAR(255));

Now create a stored procedure to split the given statement into individual words.

IF EXISTS(SELECT 1 FROM SYS.SYSOBJECTS WHERE TYPE='P'AND NAME='usp_split_string')
BEGIN
	DROP PROCEDURE usp_split_string;
END
GO
CREATE PROCEDURE usp_split_string(@Txt NVARCHAR(MAX))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @temp TABLE (KeyWord NVARCHAR(250));
DECLARE @data NVARCHAR(MAX),
		@delimiter NVARCHAR(10),
		@pos	INT,
		@start	INT,
		@len	INT,
		@end	INT;

SELECT	@data = @Txt,
		@delimiter=' ',
		@len=LEN('.'+@delimiter+'.')- 2,
		@end=LEN(@data)+ 1,
		@start=1,
		@pos=0;
WHILE (@pos < @end)
BEGIN
	SET @pos = CHARINDEX(@delimiter,@data,@start);
	IF (@pos= 0) SET @pos=@end;

	INSERT @temp (KeyWord)
	SELECT SUBSTRING(@data,@start,@pos-@start);
	SET @start=@pos+@len;
END
INSERT INTO KeyWords(word)
SELECT KeyWord FROM @temp;
END

The stored procedure splits the statement into individual words. For example if we input a statement “SQL Server 2012” to the stored procedure it splits the statement into words an stored into table “KeyWords”.

i.e

SQL

Server

2012

Using the same logic we will apply the logic to existing table “SearchQueries” and prepare a list of keywords from the table.

BEGIN
	DECLARE @Max INT,
			@i INT,
			@STMT VARCHAR(100),
			@Category VARCHAR(200);

	TRUNCATE TABLE KeyWords;
	SELECT @max = MAX(ID) FROM SearchQueries;
	SET @i= 1;
	WHILE(@i <= @Max)
	BEGIN
		SELECT	@stmt = Query
		FROM	SearchQueries
		WHERE	ID = @i;

		EXEC usp_split_string @stmt;
		SET	@i = @i + 1;
	END
END

Execute the above script to accomplish the task.

Check the table for the list of keywords.

Finally capture the keywords and corresponding counts.

SELECT	Word,
	Count(1)AS'Total_Count'
FROM	KeyWords
GROUP BY Word
ORDER BY Total_Count DESC;
Posted in SQL Development | Tagged , , , , , , , , , , | Leave a comment