Saturday, 18 February 2012

Archive files based on number of files configuration in SQL Server - SSIS by serverku

Hope you read earlier posts to archive files which are older as per days and you liked them. If you have not read then you can go there by following where you have various scenarios with the same,
  1. Archive old database backup files using TSQL Script - SQL Server
  2. Delete files from specified folder using File System Task in SQL Server - SSIS
  3. Delete files from specified folder using Script Task in SQL Server - SSIS
  4. Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS
  5. Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS
Recently I was asked by my friends to delete the files bases on number of files, not based on days. Such a way we can keep number of files every time and rest files get archived. I will give you an example here as I have three folders and want to keep the different number of files in each folder only. Says keep 3 files in BackupFolder1, keep 2 files in BackupFolder2 and keep 1 file in BackupFolder3. So how can we achieve it? Let's move on the steps,

First we will check the files existing in folders,


We are creating a SSIS package here and will use tasks to perform the archive process,


Also the variables used in the whole task flow,


1.
You can see the flow of the tasks, so we will be here for task 1 which will have fetch the records from a table which really need to be archived. We have inserted folder paths and a number of the files which need to keep in each folder. Let's view the records in a table,


Now we will create a task1 with Execute SQL Tasks and get the list of archive folders as figured below,


It fetches the folders and archive numbers for each row and assign to a variable which is object data type. For variable assignment you need to set a ResultSet option to 'Full result set' as above. After that, you mapping variables are as following for output,


2.
We need to go through the values coming out from variable and then map it to two variables again for archive folder name and archive numbers using Foreach Loop Container,


You can see how the object variable holds the values and assigned to other variables again as per below shot,


3.
Now use Execute SQL Task again and get the list of the files which need to deleted, You need to write some SQL statement to get those files as per archive numbers. So we will look the configuration and variable value mapping and using them in the script. You need to enable xp_cmdshell from the configuration.


Same as earlier steps we need to put the SQL query in SQL Statement option and set 'Full result set' for ResultSet, You can see two arrows which will be input values of the script which going to be executed, You can collect the same script here,

CREATE TABLE #TempFiles
(
Seq int identity(1,1),
FileName varchar (500)
)

DECLARE @FullPath varchar(500), @sql varchar(500)

SET @FullPath = ?
SET @sql = 'dir /B /O-D '+ @FullPath

INSERT INTO #TempFiles (FileName)
EXEC xp_cmdshell @sql

SELECT @FullPath + FileName  as FullName
FROM #TempFiles
WHERE FileName is not null
AND Seq > ?

DROP TABLE #TempFiles


After completion of above to get the resulted values in another variable and pass them to the next step,


4.
Use Foreach Loop Container to process, delete operation for each files same steps we did earlier for Foreach Loop,



5.
Finally, use the Script Task to delete the file one by one as I did in below image. You need to pass the value of the file name as read only,


Now use the following script to perform actual tasks for deletion, Use below script in your script task,


You can copy same script from here,
--//Apply below line in #region namespaces
using System.IO;

--//Apply below code inside
public void Main()
{
string FilePath = Dts.Variables["User::FileName"].Value.ToString();
System.IO.File.Delete(FilePath);
Dts.TaskResult = (int)ScriptResults.Success;
}


After running the package files will be deleted as per archive number defined in the tables for each folder.

You can schedule this package in some frequency. Hope this help you.

Friday, 10 February 2012

Restore Full Database from multiple backup files - SQL Server by serverku

As I know database restoration is a best practice on periodically to check and verify database backup copies and issues while restoration due to any stuffs. I have written for the database backups as how can we perform a full database backup completely and split into multiple files to reduce IO and time. As we have performed full database backups into split multiple files, Same way we will perform restoration from those complete and split multiple files. First we will see to restore full database complete backup and tried for from multiple files.

#1. Using TSQL : Let us run the below script first and get the logical names for database data and log files
RESTORE FILELISTONLY FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
GO

After collection and putting logical file names in script below, it will restore the database from complete backup.
RESTORE DATABASE ReportServerComplteCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy_log.ldf'
GO


As above, we can restore database from multiple files as following,
RESTORE DATABASE ReportServerSplitCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy_log.ldf'
GO


#2. From Management Studio : We can also perform restoration from SSMS by adding those multiple backups as per shot.


You can see over here after adding those files it will show as a single backup set.


Hope this help you.

Saturday, 4 February 2012

DDL Trigger and DDL Auditing in SQL Server 2005 by serverku

Hope this help you. For very sensitive and secure databases, it is required to track all the activities and changes done with the database objects. And it should be very secure as well, so nobody can play with database objects. Even we should have all the history and tracking for what are database object structure changes occurred, what, when and who did it?

For the tracking all these stuffs we need to have a better process to change them and it should be done by a responsible person. Bur sometime the issues may be occur with database objects changes, at that time if we have all the enough information then we can correct and revert it back as original. Today I would like to introduce to handle all the stuffs, and it is Database DDL Triggers. DDL Triggers can be specified at database and server level, but for the database object tracking we need it on the database. Let us see an example.
-- Creating audit database
CREATE DATABASE AuditDatabase
GO

USE AuditDatabase
GO

-- Creating table, which capture all the objects structure changes
CREATE TABLE ObjectTracking
(
TrackingId bigint identity(1,1),
TrackingDate datetime NOT NULL DEFAULT GETDATE(),
DatabaseName varchar(500),
EventType varchar(500),
ObjectName varchar(500),
ObjectType varchar(500),
LoginName varchar(500),
HostName varchar(500),
SqlCommand nvarchar(max)
)

GO
Now to audit the database objects, DDL operation or structure changes we have to create a DDL trigger at database level.
-- Creating triggers, which will be fire on every objects actions on the database of that instances.
CREATE TRIGGER [AuditObjects]
ON DATABASE
FOR
-- DDL_DATABASE_LEVEL_EVENTS
-- Defining all the DDL database events on which we need track
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS
BEGIN
SET NOCOUNT ON

DECLARE @data XML
DECLARE @HostName varchar(500)
SET @data = EVENTDATA()
set @HostName = HOST_NAME()

-- Inserting tracking information in audit table
INSERT INTO ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'),
@HostName,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
)
END
GO
Now we have created tables and DDL triggers to capture the object movement. Now we have turn to test it.
Checking :
-- Creating , Altering and Destroying some of the various objects
-- Creating table event
CREATE TABLE AuditTest (AuditId int, Auditname char(10))
GO

-- Altering table event
ALTER TABLE AuditTest
ADD  AuditDate datetime NULL
GO

-- Creating Indexes events
CREATE CLUSTERED INDEX  ix_AuditId on AuditTest(AuditId)
GO

CREATE NONCLUSTERED INDEX  ix_AuditDate on AuditTest(AuditDate)
GO

-- Dropping index events
DROP INDEX  ix_AuditDate on AuditTest
GO

-- Creating view events
CREATE VIEW AuditView
AS
SELECT * FROM AUDITTEST
GO

-- Creating procedure events
CREATE PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AUDITVIEW
END
GO

-- Altering procedure events
ALTER PROCEDURE AuditProc
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM AuditTest
END
GO

-- Dropping view and table events
DROP VIEW AuditView
GO

DROP TABLE AuditTest
GO

Now audit test over, we have to review the history in audit table now,
SELECT
TrackingId,
TrackingDate,
DatabaseName,
EventType,
ObjectName,
ObjectType,
LoginName,
--HostName,
'Paresh-PC' as HostName,
SqlCommand
FROM ObjectTracking
ORDER BY TrackingDate
GO



Conclusion : We have some other new features for DDL Auditing in SQL Server 2008. But before SQL Server 2008 we can use this method for auditing.

Are you using this or using something else? Make your comments.