MERGE statement - a new tsql feature of SQL Server 2008 by serverku

It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of writing separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter.

Yes, that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation to perform. That must be ended by semicolon. Let's see the example using Merge statement.

-- Creating Database

CREATE DATABASE MergeDatabase

GO

USE MergeDatabase

GO

-- Creating tables used for merged operation

IF ( Object_id('UsingTable') > 0 )
  DROP TABLE UsingTable

GO

CREATE TABLE UsingTable
  (
     RefId INT IDENTITY(1, 1),
     name  VARCHAR(100)
  )

GO

IF ( Object_id('TargetTable') > 0 )
  DROP TABLE TargetTable

GO

CREATE TABLE TargetTable
  (
     ChildId INT,
     val     INT
  )

GO

-- Inserting records in both tables

INSERT INTO UsingTable(name)
VALUES      ('Target-1'),
            ('Target-2'),
            ('Target-3'),
            ('Target-4'),
            ('Target-5')

GO

INSERT INTO TargetTable(ChildId,val)
VALUES      (1,1),
            (2,2),
            (3,3),
            (6,6) 

GO

Let us see how Merge statement works.

1. Merge statement with WHEN MATCHED clause and updating records,>

MERGE TargetTable 
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED THEN
UPDATE set val = val + 5 ;

2. Merge statement with WHEN MATCHED clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED AND ChildId = 3 THEN
DELETE ;

3. Merge statement with WHEN NOT MATCHED BY TARGET clause and inserting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
;

4. Merge statement with WHEN NOT MATCHED BY SOURCE clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

5. All together at once,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Let's see the result set of TargetTable before and after the Merge statement used.

Before Merge statement ran,

After Merge statement ran,

6. Using OUTPUT with Merge statement,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE

OUTPUT

$action,
INSERTED.ChildId,
INSERTED.Val,
DELETED.childId,
DELETED.val
;

Hope you have already started to use Merge statement.

Copy nonclustered indexes to subscriber while generating snapshot - SQL Server Replication by serverku

Few days back, I was working to add new table in a publication in transaction replication. After adding an article in a publication and after reinitialized, I see non-clustered index was not copied at subscriber database. I tried reinitialized one more time and had a same issue. Let me generate scenario and look forward to the solution after that,

USE [PublisherDB]
GO

CREATE TABLE [dbo].[EmpList]
(
 [EmpId] [int] NOT NULL PRIMARY KEY,
 [EmpName] [varchar](100) NULL,
 [EmpCity] varchar(100) NULL,
 [EmpAddress] [varchar](200) NULL
)

CREATE NONCLUSTERED INDEX IX_EmpList_Name ON [dbo].[EmpList] ([EmpName]) 

I added above created a table in publication and reinitialized subscription with new snapshot and check indexes of that table at both publisher and subscriber side,

EXEC sp_helpindex [EmpList]

Non-clustered is not copied to subscriber after new snapshot applied. After some workaround I found a solution. Make true for the option of copy nonclustered indexes from article property,
Go to Publication property –>  Articles tab –> Article properties –> Set Properties of Highlighted Table Article or Set Properties of All Table Articles and apply changes as following,

After applying changes above and new snapshot generated after that, it copied non clustered indexes to the subscriber database. There are some other options you can find there,

All existing non-clustered indexes copied after new snapshot applied, but I created one more non-clustered index on that table and it's not copied subscriber database without generating a new snapshot. That new index copied after only new snapshot applied of that table. So I am leaving one discussion open here, why that newly created non-clustered index not copied without generating a new snapshot? Any alternative way or recommends solution?

Script to get running schedule jobs - SQL Server by serverku

A few days back, while I was working with Alerts for failed scheduled jobs and missing scheduled jobs, I was needed to exclude running jobs. So we can exactly know which jobs are actually failed excluding running jobs and sane case for missing jobs. I applied some additional in the existing script to fetch the records of failed scheduled jobs or missing jobs. Let me share a script here to get all running scheduled jobs,

USE msdb
GO

CREATE TABLE #RunningJobs 
( 
    job_id                UNIQUEIDENTIFIER NOT NULL,   
    last_run_date         INT              NOT NULL,   
    last_run_time         INT              NOT NULL,   
    next_run_date         INT              NOT NULL,   
    next_run_time         INT              NOT NULL,   
    next_run_schedule_id  INT              NOT NULL,   
    requested_to_run      INT              NOT NULL,   
    request_source        INT              NOT NULL,   
    request_source_id     sysname          COLLATE database_default NULL,   
    running               INT              NOT NULL,   
    current_step          INT              NOT NULL,   
    current_retry_attempt INT              NOT NULL,   
    job_state             INT              NOT NULL
)   
  
INSERT INTO  #RunningJobs  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'

SELECT sj.name, 
       rj.* 
FROM   #runningjobs rj 
       INNER JOIN dbo.sysjobs sj 
               ON ( sj.job_id = rj.job_id ) 
WHERE  rj.running = 1 

DROP TABLE #RunningJobs

Above script use dbo.xp_sqlagent_enum_jobs undocumented object from the master database. It is also useful to get all other state values of jobs like following which received from forums,

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

I am using this script in failed jobs and missing jobs list. You might be using it somewhere, Please share your comments. I will publish further posts to get failed jobs and missing jobs list.

Moving table or indexes on another filegroup - SQL Server by serverku

Because of data growth and performance issue we need to move tables or indexes to other file groups. The purpose of moving tables or indexes to improve the database performance as it reduces I/O from single drive or single filegroup. We can create another filegroup and move the tables and indexes to new created filegroups, then it will spit the I/O to two different filegroups. This way it will improve performance.

When we need to move the tables or indexes to another filegroup? We should move those table needs to move which are having more rows or heavily in size, Also need to move if those table's data are frequently updated. Here I would like to show the demo for the same. Let us see how can we move tables/indexes to another filegroup.

-- First we are creating database  
-- Main primary file on Primary filegroup 
-- Secondary files on secondary filegroup. 
-- Log files on different file group. 
CREATE DATABASE filegroupdb 
ON PRIMARY 
 ( name = filegroupdb_data, 
 filename = 'C:\FileGroupDB_Data.mdf', 
 size = 4 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ), 
filegroup secfilegroup 
 ( name = filegroupdb_data_1, 
 filename = 'C:\FileGroupDB_Data_1.ndf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 
log ON 
 ( name = filegroupdb_log, 
 filename = 'C:\FileGroupDB_Log.ldf', 
 size = 2 mb, 
 maxsize = 5 mb, 
 filegrowth = 1 mb ) 

GO 

After creating database we will create a new table on Primary filegroup. If you not mentioned filegroup name, then it will be created on default filegroup. Let us create it.

USE filegroupdb 
GO 

-- Creating table on Primary filegroup 
IF( Object_id('tblFileGroup', 'u') > 0 ) 
  DROP TABLE tblfilegroup 
GO 

CREATE TABLE tblfilegroup 
  ( 
     objectid   INT NOT NULL PRIMARY KEY, 
     objectname VARCHAR(100), 
     createdate DATETIME 
  ) 
ON [PRIMARY] 

GO

The table is created and now we look the property of the table where it is created,

SP_HELP tblFileGroup

Now we are coming to the point which is tables/indexes actually will be moved. There are two ways through we can move the tables and indexes.

#1. By moving clustered index
Using this way we just need to move clustered index and tables and all indexes will be moved to different filegroup.

How can with #1 way?

-- Defining dropping constraint with move
ALTER TABLE tblFileGroup
DROP CONSTRAINT PK__tblFileG__9A619291029E5EB6 WITH (MOVE TO SecFileGroup)
GO

-- Adding constraint
ALTER TABLE tblFileGroup 
ADD CONSTRAINT PK__tblFileG__9A619291029E5EB6 PRIMARY KEY(ObjectId)
GO

Let us check again the properties of tables as successfully moved or not.

SP_HELP tblFileGroup

#2. Recreate a clustered index with drop existing
We just need to recreate a table's clustered index with drop existing clustered index on that.

How can with #2 way?

CREATE UNIQUE CLUSTERED INDEX PK__tblFileG__9A619291029E5EB6
ON tblFileGroup(ObjectId)
   WITH DROP_EXISTING
ON SecFileGroup

GO

You also worked with moving tables/indexes to another location, Share your ideas here!

How to increase the number of characters retrieved from the server for XML data - SQL Server by serverku

You might work with xml and you may aware of title mentioned in this post. Today when I was working to collect information from one table which having xml column, I was trying to open xml data result set from query analyzer with new query window. For some of the xml data result set it to raise an error below while opening with new window,

Unable to show XML. The following error happened:  There is an unclosed literal string. 

This is just an error I faced for some xml data which are lengthy in size. But error is showing solution too “One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options”. Let follow toward it and go ahead the step suggested, please see below snapshot which is showing the option where we can change the suggested setting,

Tools –> Options

Nothing to say other than the screen shot above. I know this is common post, but I shared it with you because I never faced this issue earlier and also did not aware about this setting. Let us share here if have this in your mind earlier.

Skip distributor agent error in sql server transactional replication by serverku

A while before few weeks, I discussed about an error of replication “The row was not found at the Subscriber when applying the replicated command”. We had a trick to get discrepancies for error table and resolved the issue. Let me put another method (Actually patch) to come out from same error which is very interesting. Before moving this method, Please read some my posts related to replication which you may like,

1. Replication components are not installed on this server-Error while adding subscriber in replication
2. Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Now going to move ahead, here I am talking about the solution for the above highlighted error while is “Skip error in sql server transactional replication, How to? ”.

Raise a problem
We will consider the same objects created and same scenario for a transactional replication in this post. Let us check the data of ready made tables,

SELECT * 
FROM   test.dbo.sample1 (nolock) 

SELECT * 
FROM   test1.dbo.sample1 (nolock) 

We have the same records in both tables at the publisher and subscriber side. Now it is time to create discrepancies using following script,

-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
SELECT 4, 
       'test4' 

Monitoring replication after the above script ran,

And viewing table’s data again from the publisher and subscriber after an error,

You can see nothing happened at subscriber because of an error occurred for one missing row.

Solution
We are about to skip this error where all replication commands stuck, So sp_setsubscriptionxactseqno system stored procedure help us which is used to troubleshooting to specify the log sequence number (LSN) of the next transaction to be applied by the Distribution Agent at the Subscriber. So let us first get sequence number of an error which we have from the replication monitor. We can also use following script to get all transactional replication errors and executed in distributor server in a distributed database,

USE distribution 
GO

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '<Publisher>' 
SET @PublicationDB = 'test' 
SET @SubscriberServer = '<Subscriber>' 
SET @SubscriberDB = 'test1' 
SET @PublicationName = 'testpub' 

EXEC Sp_helpsubscriptionerrors 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  @SubscriberServer, 
  @SubscriberDB 

GO 

(Click on image to enlarge)
Please get a top sequence number of an error from where all transaction commands stuck and run following script on the subscriber side,

USE test1 
GO

DECLARE @PublisherServer VARCHAR(50), 
        @PublicationDB   VARCHAR(50), 
        @PublicationName VARCHAR(50) 

SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test' 
SET @PublicationName = 'testpub' 

EXEC Sp_setsubscriptionxactseqno 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  0x00000022000000C1000400000000 

GO

Finally running above script in the subscriber database, this error skipped and all remaining and pending commands were applied, which we can see the data of both tables from the publisher and subscriber database,

This is just my experience which I am sharing with you. It is recommended to find a route of this error and solve it. Did you receive such error and you skipped any? Please share your thoughts!

Merge statement with TOP clause - SQL Server by serverku

A week ago, I posted for Insert, Update and Delete statement with TOP clause and Merge statement as an individual post. If you haven't read those posts, then read it before to move next. In this post I used TOP clause with DML operations and Merge statement, but both are individual posts. Let me put these two posts together here and create new one.

What is it?
It is nothing but the form of two individual posts and it is Merge statement with TOP clause.   I never used Merge statement and the TOP clause at once. Let me create the required objects in this demo or we can pick from an earlier post,

-- Creating tables used for merge operation 
IF ( Object_id('UsingTable') > 0 ) 
  DROP TABLE usingtable 

CREATE TABLE usingtable 
  ( 
     refid INT IDENTITY(1, 1), 
     name  VARCHAR(100) 
  ) 
GO

IF ( Object_id('TargetTable') > 0 ) 
  DROP TABLE targettable 

CREATE TABLE targettable 
  ( 
     childid INT, 
     val     INT 
  ) 
GO

-- Inserting records in both tables 
INSERT INTO usingtable 
            (name) 
VALUES      ('Target-1'), 
            ('Target-2'), 
            ('Target-3'), 
            ('Target-4'), 
            ('Target-5') 

GO 

INSERT INTO targettable 
            (childid, 
             val) 
VALUES      (1,1), 
            (2,2), 
            (3,3), 
            (6,6) 
GO 

Now we will run merge statement with TOP clause and also view Target table’s data before and after script run,

SELECT * 
FROM   targettable 

MERGE TOP (2) targettable 
using usingtable 
ON ( refid = childid ) 
WHEN matched AND childid = 3 THEN 
  DELETE 
WHEN matched THEN 
  UPDATE SET val = val + 5 
WHEN NOT matched BY target THEN 
  INSERT(childid, 
         val) 
  VALUES(4, 
         4) 
WHEN NOT matched BY source THEN 
  DELETE; 

SELECT * 
FROM   targettable 

GO

Merge with TOP clause

Merge without TOP clause

You can see from both images, with Merge statement with TOP clause updated only 2 rows and remaining insert and delete operation not happened which happened with Merge statement without TOP clause. Did you used both at once?

Could not find the Distributor or the distribution database for the local server - Error while posts a tracer token in Replication by serverku

A few days back, I spoke about the manual failover of mirroring and also explained one issue and workaround too. Continuing with the same failover, I want to express one more issue here. This issue is not very critical but it somehow to create an issue while collecting some information for report or any other purpose. Let me elaborate everything here, why and how this error raised.
You all know about system stored procedure sys.sp_posttracertoken which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics, which we can schedule on some frequency to post tracer tokens. Tracer tokens can be inserted with Replication monitor also,

You can find the tsql code for same below which must be run against the publisher database,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id 

Error
But after a failover when I tried the same tsql code in the switched publisher database I received an error,

"Could not find the Distributor or the distribution database for the local server.
The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."

Solution
This script was running fine in the original publisher database before failover. During the investigation as per error message I found sp_helpdistributor was returning NULL values in publisher database. sp_helpdistpublisher also not showing publisher in Distributor server or a server where distribution database belongs to. That means we have to do two things,

1. Configure distributor at publisher.
2. Configure publisher at distributor.

So moving ahead towards the solution and apply below solution,
1. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,

Use master
GO

EXEC sp_adddistributor  
           @distributor=  '<Distributor>' , -- Put your distributor server name here
           @password= 'testpwd' -- password of distributor_admin 

2. sp_adddistpublisher configures a publisher in distributor server, which executed at the distributor side in the master database,

USE master
GO

EXEC sp_adddistpublisher 
  @publisher= '<Publisher>'  -- Put publisher servername here
 ,@distribution_db= 'distribution'  -- Distribution database name
 ,@security_mode= 1 
 ,@login= 'sa' 
 ,@password= 'testpwd' 

After this workaround I was able to ran this script successfully at the publisher and scheduled for  every 5 minutes, so I can use it for replication latency alert and reports too.  Are you using sys.Sp_posttracertoken system stored procedure? Share your feedback here.

Purge old data from dbo.sysmail_mailitems system table in msdb database - SQL Server by serverku

As a DBA, disk space is an important factor for daily monitoring and I encountered one issue of same for disk space. I found it based on disk space statistics report from all servers. I investigated disk space usage of all databases and found msdb system database went beyond 38 GB around. You can find the script at database size information. After database, it is needed to know where actually it was eating, which tables? I found one system table ‘dbo. sysmail_mailitems’ consumed high disk space and created space issue. Please read the post to get table size statistics for a particular database.

To make a disk space I removed some old data from dbo. sysmail_mailitems system table using a script. We can also delete data directly from this system table, but I found system stored procedure to purge old data from this table from here. Let me share with you,

1. sysmail_delete_mailitems_sp :  Permanently deletes e-mail messages from the Database Mail internal tables.
2. sysmail_delete_log_sp : Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.

And the script using these system procedures is following,

USE msdb; 
GO

DECLARE @DeleteBeforeDate DATETIME 

-- Purge data older than 30 days
SELECT @DeleteBeforeDate = Dateadd(d, -30, Getdate()) 

EXEC sysmail_delete_mailitems_sp 
  @sent_before = @DeleteBeforeDate 

EXEC sysmail_delete_log_sp 
  @logged_before = @DeleteBeforeDate 

To purge data manually, we can make it atomize with scheduled job. I do not know about setting which removed data automatically from this system table. Do you know about the same? This issue I experienced first time and I want to know about your experience if you can share here. What will be your comment to this post?

Merge statement and identity insert - SQL Server by serverku

Today I asked by my friend for merge statement and identity insert, how to insert identity column data using merge statement? I posted for the merge statement without identity insert. Please read that post first before move ahead. So I would like to publish my friend’s question and answer too. It’s nothing but simple as identity insert for single table without merge. Let me generate objects required for the demo,

IF ( Object_id('EmpList1', 'U') > 0 ) 
  DROP TABLE emplist1 

IF ( Object_id('EmpList2', 'U') > 0 ) 
  DROP TABLE emplist2 

CREATE TABLE emplist1 
  ( 
     seq1     INT NOT NULL IDENTITY(1, 1), 
     empid1   INT NOT NULL PRIMARY KEY, 
     empname1 VARCHAR(50) 
  ) 

CREATE TABLE emplist2 
  ( 
     seq2     INT NOT NULL IDENTITY(1, 1), 
     empid2   INT NOT NULL PRIMARY KEY, 
     empname2 VARCHAR(50) 
  ) 

INSERT INTO emplist1 
VALUES      (1001, 
             'Emp1001') 

INSERT INTO emplist2 
VALUES      (1001, 
             'Emp2001') 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

DELETE FROM emplist2 
WHERE  seq2 = 2 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

SELECT * 
FROM   emplist1 

SELECT * 
FROM   emplist2 

You can see in the script and the image where we have to update and insert record in table emplist1 from emplist2, where record with seq1 will be updated and record with seq3 will be inserted with an identity. So emplist1 will become a target and emplist2 will become a source for this operation. Let me put a script here for same,

SET IDENTITY_INSERT emplist1 ON 

MERGE emplist1 
USING emplist2 
ON ( empid1 = empid2 ) 
WHEN matched THEN 
  UPDATE SET empname1 = empname2 
WHEN NOT matched BY target THEN 
  INSERT(seq1, 
         empid1, 
         empname1) 
  VALUES(seq2, 
         empid2, 
         empname2) 
WHEN NOT matched BY source THEN 
  DELETE; 

SET IDENTITY_INSERT emplist1 OFF 

You can see I used IDENTITY_INSERT on top and an identity column in the code while inserting records. Now checking records after the end,

I know you know about this, but I shared this post because I never used merge statement and identity insert at once. Have you ever used?

Apply discrepancies at destination using SSIS - tablediff Utility in SQL Server by serverku

The last time we saw tablediff Utility basis and tablediff utility for multiple tables using SSIS. As you know using sssis package it generated discrepancies log files for database changes which we have to apply on destination servers\databases to make them seem. Now this post is extended for the same in which we will take care to automatically apply the discrepancies for each file generated at destination.
I am writing here for the next portion of previous post using ssis package for multiple tables and changes to be generated. Let create remaining part and add to existing package.

In the previous post we have visited first three steps and here I added last three steps (4 to 6). For the steps (1 to 3) please visit this post. Let’s continue with the remaining steps,

Step 4 : For each loop container
Retrieving each log files for the process,

Here we have taken one more variable to capture full log file path in variable named “Filename” and the following process will do the same,

Step 5 : Execute SQL Task
Taking destination database connection and using file connection,

To apply multiple file changes to destination, taken expression for FileConnection,

Step 6 : File System Task
Moving files to other location after process, so does not repeat next time,

After completing and running all the steps we will have the destination database to same as source , let us run first three steps (1 to 3) after changes apply and verify if found any other discrepancies,

You can see after this run, we have a message by tablediff utility is “Source table and destination table are identical” and it won't generate any discrepancies log files more. I want to know you are using, these steps to apply changes to destination using ssis? Waiting for your comments!

Copy database backup files using XCOPY command by serverku

Recently, when I was working database backups copy to external drives or whatever destination, I tried it with new solution using XCOPY command. Before that solution I was using XP_CMDSHELL command in SQL Server to copy database backup file to an external drive, also we should avoid using XP_CMDSHELL for security concern. So out of SQL server script\query and another tool, the solution came with XCOPY command which copy latest files or as per date specified, so later database backup files or the files created on or after the date specified as input. You can visit XCOPY command here.
Lets first share me the command which I am using to copy latest database backup files. Here I am using some variables in the code, but let’s share me script first, then I will explain the variables.

SET dwMONTH=%DATE:~4,2%
SET dwDAY=%DATE:~7,2%
SET dwYEAR=%DATE:~10,4%
SET dwDate=%dwMONTH%-%dwDAY%-%dwYEAR%

SET source=C:\DatabaseBackup
SET destination=\\externaldrive\DatabaseBackup\
SET extension=*.bak

XCOPY /Y %source%\%extension% %destination% /s /i /D:%dwDate% 

You can see batch file created with the above code and the variables used inside and how they are integrated with final XCOPY command. After getting to run it will copy database backup files to an external drive or whatever destination using source, Destination, file extension (.bak\.trn here) and the date on or after the backup files created. Let’s implement and run it..
This code in batch files, copy all the files created Today with specified file type from source folders including sub folders to destination. So lets run batch and see what’s inside. Let's try with sample example and local drives,

Apart from XP_CMDSHELL command using in SQL Server, which methods you are using the database backup files to external drives? Please share here. I will post a further topic with a different method for the same. Hope you enjoyed it.

SQL Server tablediff utility for multiple tables using SSIS by serverku

As we saw the last post for the basic concept of tablediff utility. We learned one example, using static table, now I would like to go it with SISS package and also using more tables comparison. So, lets start it with some demo objects and created as follows. Here I am creating two tables with different databases and same SQL server instance.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.SourceObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

INSERT dbo.SourceObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 2,'test4'
GO


USE DestDB
GO

CREATE TABLE dbo.DestObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.DestObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

INSERT dbo.DestObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 3,'test4'
GO


SELECT 
 'Sourcedb.dbo.SourceObj1' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj1
SELECT 
 'Destdb.dbo.DestObj1' as Objectname,
 * 
FROM Destdb.dbo.DestObj1
SELECT 
 'Sourcedb.dbo.SourceObj2' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj2
SELECT 
 'Destdb.dbo.DestObj2' as ObjectName,
 * 
FROM Destdb.dbo.DestObj2
GO

Let see the same data inserted,

Now I will create a ssis package with passing dynamic table name and other require details. But before that I need to populate one table with same details which we need require as argument to be passed in a batch file,

USE Maintenance
GO

CREATE TABLE DatasyncDetails
(
 Id int identity(1,1),
 SourceDatabase varchar(50),
 DestDatabase varchar(50),
 SourceSchema varchar(20),
 DestSchema varchar(50),
 SourceTable varchar(50),
 DestTable varchar(20),
)


INSERT INTO DatasyncDetails
(
SourceDatabase, 
DestDatabase, 
SourceSchema, 
DestSchema, 
SourceTable, 
DestTable
)

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj1',
 'DestObj1'
 
UNION ALL

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj2',
 'DestObj2'

SELECT 
 SourceDatabase, 
 SourceSchema, 
 SourceTable, 
 DestDatabase, 
 DestSchema, 
 DestTable
FROM Maintenance.dbo.DatasyncDetails
GO

Let's check the data populated which we have to process for discrepancies,

All required demo objects created to prepare for ssis package,

and the variables used ,

I have presented two whole snaps with details of whole flow, Now we will the all the steps one by one.

Step 1 : Execute SQL Task

Step 2 : For Each Loop Container

Step 3 :  Execute Process Task

Here we will create a batch file and call this task where arguments pass from parameter we mapped in an earlier task. You can see the content of batch file and the argument used inside,

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
–sourceserver [PARESH\MSSQLSERVER2012]
-sourcedatabase [%1]
-sourceschema [%2]
-sourcetable [%3]
-sourceuser [dba]
-sourcepassword [dba@1234]
-destinationserver [PARESH\MSSQLSERVER2012]
-destinationdatabase [%4]
-destinationschema [%5]
-destinationtable [%6]
-destinationuser [dba]
-destinationpassword [dba@1234]
-et Difference
-f C:\DiffOutput\%7

Now I will use this batch file in execute process task and the use the arguments passed by For Each Loop container.

Expression of Arguments :

@[User::SourceDB]  +" "+  @[User::SourceSchema] + " " +  @[User::SourceTable] +" "+  @[User::DestDB]  +" "+  @[User::DestSchema] + " " +  @[User::DestTable] +" "+ @[User::SourceTable]

Finally done with all the steps and will have to run the package and will review the resulted SQL script log for discrepancies. So let's run it and review the out files.
Running…

Output files,

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj1]
UPDATE [DBO].[DestObj1] SET [CreatedDate]='2012-10-06 11:39:10.620' WHERE [id] = 1
INSERT INTO [DBO].[DestObj1] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.620',2,'test2')
DELETE FROM [DBO].[DestObj1] WHERE [id] = 3

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj2]
UPDATE [DBO].[DestObj2] SET [CreatedDate]='2012-10-06 11:39:10.627' WHERE [id] = 1
INSERT INTO [DBO].[DestObj2] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.627',2,'test4')
DELETE FROM [DBO].[DestObj2] WHERE [id] = 3

We can use more tables to find differences between them as we did. Here you can use server name, username & password as an argument and make it fully dynamic, but if those SQL servers can be connect from there. In the next post I will add some additional task to apply differences automatically at destination servers\databases. Hope you will like and share it.

SQL Server tablediff Utility by serverku

Earlier, when I was working with task to sync data for two tables between two databases, I got the chance to use the tablediff.exe utility provided by SQL server. It used to compare data for two tables which have similar columns and data type structure. After comparing it generates transact SQL script log for discrepancies.

We can use with command line or with a batch file. Let us see how can we use with command line. This utility is found in “C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe” path or wherever the SQL server installed. tablediff.exe used to compare table data in same servers\databases or different servers\databases. The syntax to use it as following.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
    –sourceserver [SourceServer]
    -sourcedatabase [SourceDatabase]
    -sourceschema [SourceSchema]
    -sourcetable [SourceTable]
    -sourceuser [SourceUser]
    -sourcepassword [SourcePassword]
    -destinationserver [DestinationServer]
    -destinationdatabase [DestinationDatabase]
    -destinationschema [DestinationSchema]
    -destinationtable [DestinationTable]
    -destinationuser [DestinationUser]
    -destinationpassword [DestinationPassword]
    -et Difference
    -f [FullFilePath]

You can find other arguments and more specification here.   Let's go through with small testing, creating Domo objects and use in the example.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

USE DestDB
GO

CREATE TABLE dbo.DestObj
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

SELECT * 
FROM Sourcedb.dbo.SourceObj
SELECT * 
FROM Destdb.dbo.DestObj
GO

Now, turn on tablediff.exe and batch created with the following code.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
-sourceserver [DemoServer] 
-sourcedatabase [SourceDB] 
-sourceschema [dbo] 
-sourcetable [SourceObj] 
-sourceuser [sa] 
-sourcepassword [test@1234] 
-destinationserver [DemoServer] 
-destinationdatabase [DestDB]
-destinationschema [dbo]  
-destinationtable [DestObj] 
-destinationuser [sa] 
-destinationpassword [test@1234] 
-et Difference 
-f C:\DiffOutput

After creating a batch file with the above code and run it and the output resulted named “DiffOutput.sql” in C: drive which is in the form of the SQL which can be executed in SQL server. Following are the changes as described in the above image.

-- Host: DemoServer 
-- Database: [DestDB] 
-- Table: [dbo].[DestObj] 
UPDATE [dbo].[DestObj] SET [CreatedDate]='2012-09-29 04:11:34.820' WHERE [id] = 1 
INSERT INTO [dbo].[DestObj] ([CreatedDate],[id],[name]) VALUES ('2012-09-29 04:11:34.820',2,'test2') 
DELETE FROM [dbo].[DestObj] WHERE [id] = 3

You can see the changes generated, we have to apply to destination databases, so data will properly sync from source to destination. I will post the next topic to use tablediff.exe in size, and it will be with multiple tables. Please share your thought here to use in any other way.