Backup and Restore Filegroup in SQL Server

Backup and Restore Filegroup in SQL Server

Backup and Restore of database is one of the primary task of any DBA. Taking up full backup of database at regular interval and restore it whenever and wherever it is needed is very popular but it may not be possible for every database. Some production databases are too big and we can’t take full backup of database frequently and in this scenario, file / filegroup backup comes to rescue.

If you are operating database with size of few thousand GBs, It becomes impractical to take full database backup and restore full database backup as it might take few hours to complete this operation. Fortunately SQL Server 2005 and later Edition provides an answer to this challenge through piecemeal restore.

I will be using AdventureWorks2012 database with FULL recovery mode in my testing server. Adventureworks2012 database comes up with one default filegroup which is “Primary” but I will add one more File with new filegroup to demonstrate this exercise.

[sourcecode language=”sql”]
USE [master]
GO
–Add New Filegroup
ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [NewFileGroup]
GO

USE [master]
GO
–Add new data file with .NDF extension
ALTER DATABASE [AdventureWorks2012] ADD FILE
( NAME = N’AdvNewFile’, FILENAME = N’E:\SQL2012DataFiles\AdvNewFile.ndf’
, SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [NewFileGroup]
GO

USE [AdventureWorks2012]
GO
–create new table in newly created filegroup
CREATE TABLE [dbo].[EmpOnNewFileGroup](
[ID] [int] IDENTITY(1,1) NOT NULL,
[empName] [varchar](100) NULL
) ON NewFileGroup
GO
[/sourcecode]

Now, let us confirm the total number of database files / filegroup along with its path and size with simple TSQL script which was given in my earlier article too.

[sourcecode language=”sql”]
USE [AdventureWorks2012]
GO
SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
GO
[/sourcecode]

We should get two filegroup for Adventureworks2012.

Now, take backup of “Primary” filegroup with following command.

[sourcecode language=”sql”]
BACKUP DATABASE AdventureWorks2012
FILEGROUP = ‘Primary’
TO DISK = ‘E:\Temp_Testing\Adv2012P.bak’;
GO
[/sourcecode]

Inserting one record in newly created table.

[sourcecode language=”sql”]
INSERT INTO [dbo].[EmpOnNewFileGroup]
SELECT ‘Ritesh Shah’
GO
[/sourcecode]

Now, we are going to take backup of new filegroup we have created along with log backup.

[sourcecode language=”sql”]
–Back up the files in newFileGroup we have just created.
BACKUP DATABASE AdventureWorks2012
FILEGROUP = ‘NewFileGroup’
TO DISK = ‘E:\Temp_Testing\adv2012N1.bak’;
GO

BACKUP LOG AdventureWorks2012
TO DISK =’E:\Temp_testing\adv2012Log.bak’
GO
[/sourcecode]

We have crossed 50% benchmark now and we are on the half way. We are done with backup stuff but we have to make sure that we are able to restore backed up database whenever we want so I will be creating new database “Adventureworks2012New”, one the same SQL Server instance , by using the backup we have taken. Do remember that I don’t have any full backup at the moment.

[sourcecode language=”sql”]
USE [master]
GO

–restoring Primary filegroup backup with PARTIAL and NORECOVERY (offline restore)
RESTORE DATABASE [AdventureWorks2012New]
FILEGROUP= ‘Primary’
FROM DISK= ‘E:\Temp_Testing\Adv2012P.bak’
WITH PARTIAL, NORECOVERY,
MOVE N’AdventureWorks2012_Data’ TO N’E:\SQL2012DataFiles\Temp\AdventureWorks2012New_Data.mdf’,
MOVE N’AdventureWorks2012_Log’ TO N’E:\SQL2012DataFiles\Temp\AdventureWorks2012New_Log.mdf’
GO

–retoring new filegroup backup with NORECOVERY
RESTORE DATABASE [AdventureWorks2012New]
FILE = N’AdvNewFile’ FROM DISK = N’E:\Temp_Testing\adv2012N1.bak’
WITH NORECOVERY,
MOVE N’AdvNewFile’ TO N’E:\SQL2012DataFiles\Temp\AdvNewFileNew.ndf’
GO

–LOG restore with recovery
RESTORE LOG [AdventureWorks2012New]
FROM DISK =’E:\Temp_testing\adv2012Log.bak’
WITH RECOVERY
[/sourcecode]

Now, let us check whether one record in the newly created table is restored or not? I am executing SELECT query on that table from both databases, I have following result:

Filegroup

So, this is how we can restore filegroup.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Database maintenance in mirroring principal database in SQL Server

Database maintenance in mirroring principal database in SQL Server

We have had certificate mirror setup in one of my earlier article along with mirroring monitor and mirroring status by script. I have one more topic to share with my blog reader for mirroring. After mirroring you may need some regular maintenance in principal database and you have to pause/stop mirroring for time being.

You have two choices in this situation.

1.)  Remove mirroring

2.) Pause mirroring for sometime

First choice should be avoided as long as possible as after removing mirroring, we have to set up mirroring all from scratch which may consume few hours if database is big so second option is obvious choice.

I had recent requirement in one of my production server which hosted SQL Server 2008 R2. I had to move my database file to faster IO subsystem and hence I had to take my database offline and move datafile to new IO subsystem. If database is mirrored, we can’t take it offline and hence I have to pause mirroring.

Here are the steps which I have used:

1.)  Stop transaction log backup, if it is setup

2.) Set your mirror partner off by following TSQL

[sourcecode language=”sql”]
–Execute following statement in Principal database server
ALTER DATABASE YourPrincipalDatabaseName SET PARTNER OFF
GO[/sourcecode]

3.) Now take your database offline and do maintenance you wanted to do

4.) Once your maintenance is over and your database is back online, takes transaction log backup manually and restores it in mirror (Slave) database in NORECOVERY mode

5.) Set your endpoint from mirror (Slave) instance. If you don’t know information about port and other details of endpoint, get that information from sys.tcp_endpoints DMV from mirror server.

[sourcecode language=”sql”]
–execute following SELECT query in mirror instance
SELECT state_desc,type_desc,port FROM sys.tcp_endpoints;

–set database partner with principal server.
–Following command should be run in mirror server
–I am using the same "TCP" ENDPOINT I can created in mirror article earlier
ALTER DATABASE YourMirrorDatabaseName SET PARTNER =’TCP://SQLX64.EDISON:5022′
GO[/sourcecode]

6.) Execute following commands in Principal server.

[sourcecode language=”sql”]
–execute following SELECT query in principal instance
SELECT state_desc,type_desc,port FROM sys.tcp_endpoints;

–set database partner with mirror server from principal server.
–Following command should be run in principal server
–I am using the same "TCP" ENDPOINT I can created in mirror article earlier
ALTER DATABASE YourPrincipalDatabaseName SET PARTNER =’TCP://MARS.EDISON:5022′
GO[/sourcecode]

This is how I do maintenance for my principal database.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles

Find mirroring status by querying sys.database_mirroring in SQL Server

Find mirroring status by querying sys.database_mirroring in SQL Server

I had already written articles about how to set mirroring and how to monitor mirroring with “Mirroring Monitor”. Today this is time to query “sys.database_mirroring” system table which contains record for each database available in SQL Server instance with NULL value in all column which starts with “mirroring_” if mirroring is not setup for the database.

sys.database_mirroring” gives your important information about principal/mirror database/server alongwith its status whether it is synchronized or not and what safety mode mirroring is configured on.

[sourcecode language=”sql”]
SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
mirroring_state_desc,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_role_desc,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring
GO
[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Check mirroring status from mirroring monitor in SQL Server

Check mirroring status from mirroring monitor in SQL Server

Mirroring is one of the best and easy to maintain high availability feature as long as SQL Server version is 2008 R2 or earlier. I wrote article about how to set certificate mirroring in SQL Server sometime back. Once you setup mirroring, you have to keep your eyes open about the status of mirroring whether it is “Synchronized” or still in “Synchronizing” mode, status is on or off etc.

SQL Server providing one powerful tool to keep watch on mirroring status, named “Mirroring Monitor” which you can ope from pop-up menu come from right click on Principal/Mirror database. Have a look at following screen capture for further information.

Once you click on “Launch Database Mirroring Monitor”, you have to add your server in the monitor and it will look like following screen capture:

This monitor will provide you with important information like send rate, restore rate, commit overhead etc along-with whether you principal and mirror and completely synchronized or still synchronizing.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Forcefully remove replication publisher, subscriber and distributor in SQL Server

Forcefully remove replication publisher, subscriber and distributor in SQL Server

I still remember one of my earlier post where I have mentioned how to remove replication and how to remove objects from replication. This is perfect solution for general use but sometime it happens that distributor database/server went crashed or distributor become unavailable or suspected and there is no chance to have that server/database back, we have to forcefully remove replication as regular or general practice wouldn’t work in that case.

When we remove publisher and/or subscriber, it updates distributor database with the latest information but in case we don’t have access of distributor database even we want to remove publisher/subscriber, we have to use “@ignore_distributor” option.

Here is the script to remove subscriber forcefully.

[sourcecode language=”sql”]

–Select your publication database

USE Adventureworks2012

GO

DECLARE @publication AS sysname;

DECLARE @subscriber AS sysname;

–enter your publication name

SET @publication = N’AdventureWorksPub’;

–enter subscriber name

SET @subscriber = N’AdventureWorksSub’;

USE [AdventureWorks2012]

EXEC sp_dropsubscription

@publication = @publication,

@article = N’all’,

@subscriber = @subscriber

,@ignore_distributor=1;

GO

[/sourcecode]

Here is the script to remove publisher forcefully.

[sourcecode language=”sql”]

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

–set your publication database here

SET @publicationDB = N’AdventureWorks2012′;

–set your publication name here

SET @publication = N’AdventureWorksPub’;

— Remove a transactional publication.

USE [AdventureWorks2012]

EXEC sp_droppublication

@publication = @publication

,@ignore_distributor=1;

— Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

@dbname = @publicationDB,

@optname = N’publish’,

@value = N’false’;

GO

[/sourcecode]

If Distributor database available and you wanted to remove it forcefully, have a look at following script:

[sourcecode language=”sql”]

–execute following command on distributor server

USE master

GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Monitor user login connected to SQL Server

Monitor user login connected to SQL Server

 

SA and Windows Administrator login should be disabled in SQL Server due to security reason. I am not going to discuss those reasons in this article as there are many pro/cons and rules/regulations of some industry associated with it. Once we start debate on this, it is never ending so there is no meaning to raise this issue in this article.

 

I, personally, believe that SA and Administrator account either should be disabled or should not be used until real emergency comes when we MUST have to use it.

 

I have my monitoring application developed in Asp.Net which keeps an eye on every aspects of SQL Server I need. One of them is to check whether any developer or application is using SA or Administrator account. I use following SQL Server view which keeps an eye on this.

[sourcecode language=”sql”]

CREATE VIEW View_GetLoginDetails

AS

SELECT TOP 5

Host_Name as HostName,

login_name AS LoginName,

count(*) as Connections,

CASE

WHEN login_name = ‘sa’ OR login_name LIKE ‘%administrator%’ THEN ‘red’

ELSE NULL

END AS BackColor

FROM

sys.dm_exec_sessions

GROUP BY

Host_Name, login_name

ORDER BY

BackColor DESC, Connections DESC

[/sourcecode]

Few other links for DBA to check for different important things:

  • Capture Schema Change in SQL Server to keep up history (Click Here)
  • Capture long running query in SQL Server (Click Here)
  • Analyze IO disk pressure in SQL Server (Click Here)
  • Transactions/Sec and Batch Requests/Sec performance counter in SQL Server (Click Here)
  • Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions (Click here)
  • Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find locked table name with duration and count in SQL Server (Click Here)
  • Find Blocking in SQL Server and use it to send an alert (Click Here)

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Transactions/Sec and Batch Requests/Sec performance counter in SQL Server

PerformanceCounterPerformance counter is one of the biggest weapon system administrator and Database administrator can have. I have already written few things about performance counter in past article mentioned below:

  • Performance Counter in SQL Server (Click Here)
  • Performance Counter in Microsoft Windows Server 2008 (Click Here)
  • Repl. Pending Xacts performance counter and high value in SQL Server (Click Here)

As long as SQL Server concern, “Transactions/Sec” and “Batch Requests/Sec” are two of my very favorite performance counter and I used to keep my close eye on this in each server I manage. The main reason to keep eye on both these performance counter is to get an idea about how much work my SQL Server can perform per seconds. As a DBA it is mandatory to know this so that you can plan out capacity of server accurately.

“Transaction/Sec” tracks down all DML statements like Insert/Update/Delete along with SELECT statement executed between transaction whereas “Batch Requests/Sec” counter gives information about each batch sends to SQL Server no matter whether it is between transaction or not so, in my opinion, “Batch Requests/Sec” can gives you more idea about throughput being performed on server.

Sometime I also want to see which is the peak time for my database and it requires to have data captured for complete 24 hours for few weeks or even month so that we can aggregate data we have and find out peak period of day, peak day of week and so on.

Let us create environment to capture all these information by following the steps given:

Note: Please test script given here in your development/beta environment first.

Step 1:

We have to create one table in database which keeps all the captured data for our counters we have created.

[sourcecode language=”sql”]
–Step1
CREATE SCHEMA MONITOR
GO

CREATE TABLE [Monitor].[PerformanceCounterHistory](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](256) NULL,
[CounterType] VARCHAR(100),
[CounterName] VARCHAR(100),
[CounterValue] [bigint] NULL,
[CreatedDate] [datetime] NULL
)
GO
ALTER TABLE [Monitor].[PerformanceCounterHistory] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO
[/sourcecode]

Step 2:

Create one Stored Procedure which will insert data into “[Monitor].[PerformanceCounterHistory]” table we have created above.

[sourcecode language=”sql”]
–Step2
CREATE PROC [Monitor].[SPInsertPerfStats]
AS

INSERT INTO [Monitor].[PerformanceCounterHistory]
(
ServerName
,CounterType
,CounterName
,CounterValue
)
Select
@@ServerName
,instance_name
,counter_name
,cntr_value
FROM
sys.dm_os_performance_counters
WHERE
(counter_name = ‘Transactions/sec’ and instance_name = ‘_Total’)
OR counter_name like ‘%Batch Requests/sec%’
GO
[/sourcecode]

Step 3:

Keep calling Stored Procedure “Monitor.SPInsertPerfStats” at certain time period. I generally prefer to call this SP at every 30 seconds, if you are sure about your business peak timing, you can call this SP in that time period only otherwise keep it for complete 24 hours cycle. You can use SSIS package and schedule that package in Windows Schedule task or you can call this SP directly from SQL Server Job Agent.

Step 4:

Since we are calling this SP at every 30 seconds interval, we have to aggregate data for every seconds and store the aggregated data in another table. Do aggregate data every morning for previous day so that we can generate different kind of report from this aggregation itself.

[sourcecode language=”sql”]
–Step4
CREATE TABLE [Monitor].[PerfStatAggr](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[CounterType] [nvarchar](256) NULL,
[ServerName] [nvarchar](256) NULL,
[CounterName] VARCHAR(100),
[CounterValue] [bigint] NULL,
[CreatedDate] [datetime] NULL,
[AggrValue] [numeric](18, 3) NULL
)
GO

CREATE PROCEDURE [Monitor].[SPInsertPerfStatAggr]
/*EXEC [Monitor].[SPInsertPerfStatAggr]
@Day = 0,  –1 is for previous day
@StartHour = 4, –start capturing of data from 4 AM previous day
@EndHour = 23 –capture data of previous day till 11 */
(
–generally I used to aggregate data of previous day always
–so kept logic of day. if you pass 1, it will aggregate data of previous day only
@Day int,
–I am interested to capture business hours data
–in our environment, we get maximum traffic in application From 8AM to 6PM, so I kept
–8 & 18. you can even aggregate data from 1 to 24, as per your requirement
@StartHour int, –8
@EndHour int –18
)
AS
BEGIN

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SET NOCOUNT ON

DECLARE @StartTime DATETIME , @EndTime DATETIME

SET @StartTime =  DATEADD(hh,@StartHour,DATEDIFF(DD,0,GETDATE()- @Day))

SET @EndTime =  DATEADD(hh,@EndHour,DATEDIFF(DD,0,GETDATE()- @Day))

;WITH ctePerfStat
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ServerName,CounterName ORDER BY CreatedDate) as Rn, *
FROM [Monitor].[PerformanceCounterHistory] PerfCount1 (nolock)
WHERE (CreatedDate between @StartTime and @EndTime)
)
INSERT INTO [Monitor].[PerfStatAggr]
(
ServerName,
CounterName,
CounterValue,
CreatedDate,
–following are the aggregated fields
–I am more interested in that only
AggrValue
)
SELECT
C1.ServerName,
C1.CounterName,
cast(c2.CounterValue as numeric(18,3)) as CounterValue,
C2.CreatedDate,
cast((c2.CounterValue – c1.CounterValue)as numeric(18,3))/DATEDIFF(SS,C1.CreatedDate,C2.CreatedDate) as AggrValue
FROM
ctePerfStat C2
inner join ctePerfStat C1
ON (C1.ServerName = C2.ServerName  AND C1.CounterName =C2.CounterName AND C1.rn-1=c2.Rn )
END
[/sourcecode]

Step 5:

Keep collecting and aggregating data for a week or so to capture the transaction pattern for your database/server. You can return the result of all these capture by following Stored Procedure.

[sourcecode language=”sql”]
–Step5
CREATE PROCEDURE [Monitor].[SPGetPerfState]
/*Declare @FromDate1 datetime
Declare @ToDate1 datetime
set @ToDate1 = DATEADD(DD,0,DATEDIFF(DD,0,getdate()))
Set @FromDate1 = DATEADD(DD,0,DATEDIFF(DD,0,getdate()))
Exec [Monitor].[SPGetPerfState] @Unit = ‘day’, @FromDate = @FromDate1, @ToDate = @ToDate1*/
–it depends of the report you require,
–if you pass day, it will return the data between From/To Date
–by getting MAX day wise
@Unit VARCHAR(10), –@Unit = ‘day’, — ‘Minute,Hour,Day,Month,Year’
@FromDate DATETIME,
@ToDate DATETIME
AS
BEGIN
DECLARE
@SQL nvarchar(4000)

SET @SQL = ‘
SELECT
CounterName,
DATEADD(DD,0,DATEDIFF(DD,0,CreatedDate)) as CreatedDate,
DATEPART(‘+@Unit+’,CreatedDate) as Unit,
CEILING(MAX(AggrValue)) as AggrValue
FROM [Monitor].[PerfStatAggr] as I with (Nolock)
where CreatedDate between ”’+cast(@FromDate as varchar)+”’ and ”’+cast(@ToDate as varchar)+”’
group by CounterName,DATEADD(DD,0,DATEDIFF(DD,0,CreatedDate)),datepart(‘+@Unit+’,CreatedDate)
order by Unit

–print @SQL
EXEC sp_executesql @SQL
END
GO

—-uncomment following SP to see the aggregated result
—-ofcourse, you have to change unit as per your need.
—-you can use Minute,Hour,Day,Month,Year etc.
—-and change the from/to date too
–EXEC [Monitor].[SPGetPerfState]
–@Unit=’HOUR’,
–@FromDate=’2012-12-05 06:00:00′,
–@ToDate=’2012-12-05 23:00:00′
[/sourcecode]

So, this is how you have to capture your transaction pattern for few days. There are few different ways to capture transactions but whenever there is a question of transaction pressure on the server where I have only SQL Server installed, this is my favorite way. We will talk about different way (from windows OS) of capturing transactions information sometime later, this is all for now…..

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Add stored procedure in transactional replication by script in SQL Server

Add stored procedure in transactional replication by script in SQL Server

I have already written many articles to cover different area of replication. Few days back I have written how to add articles (Table, View, SP etc.) into replication from GUI and Add Table in transactional Replication by script but today I am going to explain how can we insert stored procedure by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

DECLARE @ProcName VARCHAR(25)
DECLARE @SchemaName VARCHAR(25)
DECLARE @PublisherName VARCHAR(25)

SET @ProcName=’uspUpdateEmployeeHireInfo’
SET @SchemaName=’HumanResources’
SET @PublisherName=’AdventureWorksPublisher’

EXEC sp_addarticle
@publication = @PublisherName,
@article = @ProcName,
@source_owner = @SchemaName,
@source_object = @ProcName,
@type = N’proc schema only’,
@description = N”,
@creation_script = N”,
@pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001,
@destination_table = @ProcName,
@destination_owner = @SchemaName,
@status = 16,
@force_invalidate_snapshot=1
GO[/sourcecode]

Some more replication related articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Add table in transactional replication from script in SQL Server

Add table in transactional replication from script in SQL Server

I have already written many articles to cover different area of replication. Few days back I have written how to add articles (Table, View, SP etc.) into replication from GUI but today I am going to explain how can we insert table by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

DECLARE @TableName VARCHAR(25)
DECLARE @SchemaName VARCHAR(25)
DECLARE @PublisherName VARCHAR(25)

SET @TableName=’Department’
SET @SchemaName=’HumanResources’
SET @PublisherName=’AdventureWorksPublisher’

exec sp_addarticle
@publication = @PublisherName,
@article = @TableName,
@source_owner = @SchemaName,
@source_object = @TableName,
@type = N’logbased’,
@description = null,
@creation_script = null,
@pre_creation_cmd = N’drop’,
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N’manual’,
@destination_table = @TableName,
@destination_owner = @SchemaName,
@force_invalidate_snapshot=1
GO[/sourcecode]

Some more replication related articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Change SMTP server for all email profiles in SQL Server

Change SMTP server for all email profiles in SQL Server

It happens many times that we set SMTP server for database email profile in SQL Server instance and over the time we change email server and hence we need to change SMTP server in email profile also. We can change it manually from GUI of SSMS but what if you have multiple SQL Server instance and each SQL Server Instance is having multiple profiles? There is a chance we might miss profile while doing it manually so I would prefer to create script which can be run under each SQL Server Instance separately and update each profile under one SQL Server instance. After changing SMTP profile, we can’t keep it as it is before testing it properly so I have divided TSQL in two parts. First script will change SMTP server and second part of the script will shoot an email from each profile automatically so that we can assure our self that each profile working properly. I have used the same script for second part which I have had in “Check all database email profile in SQL Server by using sysmail_profile” article so do visit earlier article, if you haven’t read it.

Note: SMTP server given here are for demonstration purpose only, you have to use your own SMTP credentials there.

[sourcecode language=”sql”]USE msdb
GO

–Part 1: Update SMTP profile from smtp to smtp1
UPDATE
sysmail_server
SET
servername = ‘smtp1.google.com’
where
servername=’smtp.google.com’

–Part 2: looping through profiles to shoot an email
DECLARE
@Total int
,@cnt int
,@ProfileName varchar(100)
,@Server varchar(500)
,@Sub varchar(4000)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)
SET @cnt = 1
SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total) BEGIN SET @ProfileName = (SELECT name FROM sysmail_profile WHERE profile_id = @cnt) SET @Sub = @Server + ' - Profile: - ' + @ProfileName +' - ' + cast(@cnt as varchar) + ' of ' + cast(@Total as varchar) IF (@ProfileName is not null) BEGIN PRINT @ProfileName PRINT @Sub EXEC msdb.dbo.sp_send_dbmail @recipients = 'Test@gmail.com' , @subject = @Sub , @body = @Sub , @profile_name = @ProfileName , @body_format = 'HTML' ; END SET @cnt = @cnt + 1 END --Manually confirm "ServerName" is updated or not select * from sysmail_server[/sourcecode] If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.