Step By Step SQL Server Log Shipping

By:   |   Comments (73)   |   Related: 1 | 2 | 3 | 4 | 5 | > Log Shipping


Problem

Setting up Log Shipping for SQL Server is not that difficult, but having a step by step process is helpful if this is the first time you have setup Log Shipping. In this tip we walk through the steps to setup SQL Server Log Shipping.

Solution

Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup and restore process that allows you to create another copy of your database for failover.

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

Log Shipping Permissions

To setup a log-shipping you must have sysadmin rights on the server.

Log Shipping Minimum Requirements

  1. SQL Server 2005 or later
  2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
  3. The servers involved in log shipping should have the same case sensitivity settings.
  4. The database must use the full recovery or bulk-logged recovery model
  5. A shared folder for copying T-Log backup files
  6. SQL Server Agent Service must be configured properly

In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarily used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.

Steps to Configure SQL Server Log Shipping

Step 1

Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases 

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'jugal'
	
USE [master]
GO
ALTER DATABASE [jugal] SET RECOVERY FULL WITH NO_WAIT
GO

Step 2

On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the "Enable this as primary database in a log shipping configuration" check box.

enable this as a primary database in a log shipping configuration

Step 3

The next step is to configure and schedule a transaction log backup. Click on Backup Settings... to do this.

log shipping transaction log backups

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

log shipping transaction log backup settings

Step 4

In this step we will configure the secondary instance and database. Click on the Add... button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

add a secondary server for log shipping

When you click the Add... button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect... button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database for Log Shipping on SQL Server

In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

log shipping secondary database initialize secondary database settings

Copy Files for Log Shipping for SQL Server

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

log shipping secondary database restore copy files settings

Restore Transaction Log for SQL Server Log Shipping

Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

log shipping secondary database settings

Step 5

In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

monitor sql server instance

Click on Settings... button which will take you to the "Log Shipping Monitor Settings" screen. Click on Connect ... button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

log shipping monitor settings

Step 6

Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.

save log shipping configuration
Next Steps
  • As Log Shipping does not support automatic failover, plan for some down time and a manual failover
  • Once you failover, check for Orphan Users and fix as needed
  • For VLDBs it is recommended that you manually restore the database instead of using the wizard to create the full backup.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, October 14, 2022 - 10:18:11 AM - Greg Robidoux Back To Top (90602)
Hi Rajashekar,

Since the database is in standby mode you are not able to make any changes, you can only read from the database.

If you add the user to the primary database, log shipping should create the user on the standby database.

The downside is the login-to-user mapping might get mixed up, so it may not provide the login on the standby server access to the database since the SIDs may be different from one server to the other.

-Greg

Friday, October 14, 2022 - 10:03:59 AM - rajashekar Back To Top (90600)
Hi Guys,
Do anyone have idea about how to create user when the database is in standby mode when it is configured to logshiiping.

Thursday, September 8, 2022 - 8:30:37 AM - Jugal Back To Top (90453)
No SQL Server doesn't support the higher to lower major version backup restore. You can use the SSIS or import/export wizard for the lower version migration.

Saturday, August 20, 2022 - 10:27:39 PM - Joe mcbratnie Back To Top (90392)
Can a process like log shipping work with SQL 2019 to SQL 2017 down grade migration?

Friday, July 3, 2020 - 3:44:32 AM - Siddarth Chaturvedi Back To Top (86081)

Hi,

Log Shipping - If I shrink the data files on the primary server will it get reflected on the secondary instances as well? 

Thanks.

Friday, January 3, 2020 - 12:09:29 AM - pankaj Back To Top (83608)

Sir,

SQL 2012 Std ,log shipping were stop when connectivity were down between DC and DR , can it restarted automatic log shipping when connectivity link up?

Monday, December 2, 2019 - 8:09:35 AM - Vinod Back To Top (83249)

Hi,

I have installed SQL Server 2014 Express edition on my Desktop system and trying to Log Shipping configuration, but I am not getting that option like "Transaction Log Shipping" please guide me on this issue.

Thursday, September 26, 2019 - 9:13:53 AM - Lakshay Arora Back To Top (82576)

Hi Jugal, Great Article!!

However, wanted to know regarding the permissions which need to be given to the shared folder for copying the backup.

Do we need to create that folder in the secondary server by logging in to the server?

Also, the permissions which need to be given to this folder is the service account of sql server agent on secondary instance?

Thanks!!

Thursday, July 4, 2019 - 7:33:50 PM - micheal Back To Top (81679)

This is very helpul thanks Jugal

Wednesday, June 13, 2018 - 3:12:14 AM - Prasad B Back To Top (76204)

 Dear Support Team

We have successfully created the log shipping in our premises .But while taking offline backup as per our security requirement,log shipping was stop functioning. So for recreating log shipping I took fresh backup of Primary server ,and while restoring on DR server I suddenly notice that after log shipping the File Location of the DR server is same as primory server.as per below sql studio as below. i.e  SQL file location on DR Server is on G:\ *.mdf . but right clicking on DB on DR server , file location is showing H:\DATA (exactly same as Primary server). 

I am confused .. Please help

Monday, June 4, 2018 - 2:26:50 AM - sanjay joshi Back To Top (76108)

Hi Jugal,

We have 2 node SQL 2016 cluster, i.e. 2 nodes are in DC and 2 nodes are in DR. I have below setup:

DC:

SQLNode 1 : 10.100.3 14

SQLNode 2 : 10.100.3.15

SQL cluster IP : 10.100.3.213

SQLNode 1 replication IP : 10.100.14.61

SQLNode 2 replication IP : 10.100.14.62

SQLNode cluster replication IP : 10.100.14.63

DR:

SQLNode IP : 10.101.3.12

SQLNode Replication IP : 10.101.14.61

I have done log shipping between DC-DR and it is working fine but it is happening through SQL cluster IP not replication IP. I want that my replication will work only through replication IP and can I connect my database through Replication IP.

Please help me to do this.

Thursday, March 8, 2018 - 7:25:12 AM - Rgahava Back To Top (75374)

u r a superstar brother

 

Wednesday, July 19, 2017 - 1:32:23 PM - Mark Kana Back To Top (59700)

 

Hi Sir

I need to have the secondary database located far away, so it takes more than 10 hours to mobe the first backup copy over the internet. My question: when do i create the Log Shipping on the primary server, Is it before i start the full backup or after i move it to the far away destination and finish the restore of it?

Thank you

Mark

Thursday, July 6, 2017 - 5:26:13 AM - dhinesh kumar s Back To Top (58950)

Its more informative. thanks dude.

Wednesday, September 21, 2016 - 4:46:35 AM - ravindra babu Back To Top (43374)

 

 

Thnak you verymuch.

 

Thursday, August 18, 2016 - 5:59:15 AM - Srinivas Back To Top (43138)

Hi Sir,

Can we use Log shipping between 2012 and 2014/2016 (Primary server 2012 and secondary server 2014/2016)?

Tuesday, September 16, 2014 - 2:20:20 AM - satya Back To Top (34540)

Dear sir,i am configure logshipping in primary and secondary servers are same mechine  for practice purpose.how the backup and copy files created?

two times bckup file is created ?

 

plz tell me and clearly explain.

 

 

Thnak you verymuch.

Thursday, August 28, 2014 - 5:25:50 PM - Vlastimil Back To Top (34326)

Dear Jugal, first of all thank you for youe helpful post.

 

Secondly, could you please advise me on following ?

 

I can easily set up logshipping for first db on instance, however with every other DB I receive an error:  PRIMARYfile1.mdf can not be overwritten. It is being used by *firstly created DB name*  - what am I doing eworng ?

 

Thank you

 

Vl.

Monday, July 28, 2014 - 1:26:42 AM - Gayathri Back To Top (33900)

Hi,

Thank you vary much for clear explaining... still I have doubt.

followed the above steps, but am getting the below error. pls help me to solve this issue asap.

"cannot open backup device"". operating system error67(The network name cannot be found). RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server.Error:3201)  "

 

Thanks in advance...

 

Wednesday, July 16, 2014 - 4:30:38 AM - Vikrant Kedari Back To Top (32729)

Very helpful and clear notes.

 

Easy to understand and clear the doubts.

 

Thanks Jugal.

 

Thursday, June 12, 2014 - 2:34:38 PM - sai Back To Top (32218)

Hi 

i've small doubt in logshipping concept inner how to run . i don't want backup,copy,restore jobs....

and

what is the difference b/w jobs&maintenceplans?

..................................would please help

 

 

regards

---------------

sai

Thursday, May 22, 2014 - 8:04:26 PM - Tamrat Back To Top (30898)

you make log shiping configuration very easy  with excellent explanation, definition. this show you are expert on the field

Please include prons and cons. wher is other topics like backup ,mirroring ,replication etc

 

Thank so much

Tuesday, April 29, 2014 - 10:48:26 AM - Hans de Kok Back To Top (30543)

Great Posting and very clear! Thank you so much. However, I still have a question for you:

 

Once the logshipping is initiated with the restore of the BAK-file to the secondary server/database, I start to backup and restore the TRN files to that secondary server. Do I need to transfer a full backup to the secondary server everytime a full backup has been created on the primary server? I think I do otherwise it will get out of synch. Or am I mistaken?

 

Many thanks and keep up the good work!

Sunday, April 27, 2014 - 7:25:55 AM - vishal singh Back To Top (30529)

Great Article

Monday, April 21, 2014 - 10:42:50 AM - SIVA Back To Top (30321)

Hi,

 

I am getting the below error in logshipping

Date4/21/2014 8:11:21 PM

LogJob History (LSBackup_hcl)

 

Step ID1

ServerGUPTA-PC

Job NameLSBackup_hcl

Step NameLog shipping backup log job step.

Duration00:00:02

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

 

Message

2014-04-21 20:11:23.87*** Error: Backup failed for Server 'GUPTA-PC'. (Microsoft.SqlServer.SmoExtended) ***

2014-04-21 20:11:23.88*** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***

2014-04-21 20:11:23.88*** Error: Cannot open backup device '\\backup\backs\hcl_20140421144123.trn'. Operating system error 53(The network path was not found.).

BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

2014-04-21 20:11:23.92----- END OF TRANSACTION LOG BACKUP   -----

 

Exit Status: 1 (Error)

I have given shared folder in Desktop but it getting the above error

Friday, April 18, 2014 - 7:26:06 AM - MOHIT YADAV Back To Top (30101)

Best Explaination about Log Shipping...

Thank you so much

Tuesday, March 25, 2014 - 10:54:41 AM - Wallace Back To Top (29869)

How do I check "case sensitivity settings"? 

Also, do you think there would be a problem if primary server is SQL 2008 standard 64 bit and secondary server is SQL 2008 R2 64 bit?  I currently have a job restoring from the primary to the secondary in full recovery mode.   The job reports success.  I'm just having problems restoring my logs because they are out of sequence.  I tried to set up basically a rigged up version of log shipping before I started researching it.  I think I need to scratch what I have and do log shipping.  Will there be any problems when changing my production server to "Enable this as the primary server in a log shipping situation"?  Any restarts or down time required?

Great Article!

 

Thanx!

Tuesday, March 18, 2014 - 8:20:07 AM - shahbaz Back To Top (29794)

helpful.... :-)

Monday, March 17, 2014 - 10:36:35 AM - satish Back To Top (29783)

Thank you so much Jugal for the nice article.

Friday, March 14, 2014 - 2:11:16 PM - binosha Back To Top (29768)

Configure Log Shipping for a database which has at least 5 tables.Configure Log Shipping for a database which has at least 5 tables.
Demonstrate features of Log shipping
- With 0 delay log restoring
- With 2 hrs delay of log restoring

 

Can i know how i do this ?

Wednesday, February 26, 2014 - 3:29:04 PM - Accidental DBA Back To Top (29593)

I set up log shipping on a SQL 2008R2 standard installation with 2 instanaces. It seems to be running fine with no errors, but I don't understand what the message: Deleting old log backup files. Primary Database means. 

This message is in the history of the transaction log restore to the secondary server. I have the primary transaction logs backed up to a shared folder and the SQL Agent account has modify permissons, but I don't see any files being deleted. What old backup files are being deleted?

Thanks in advance.

Sunday, February 16, 2014 - 3:08:20 AM - srikanth Back To Top (29467)

Thankyou so much for clear explation. Verything looks good and perfect. Can you please add the screen shots of the primary server and the secondary server DB's how does they look like after the log shipping is been configured

Monday, December 30, 2013 - 3:02:03 PM - Eric Wilder Back To Top (27914)

1.  What is anyone's experience with log shipping to a remote DR site?

Friday, November 15, 2013 - 5:10:39 AM - SQLDBA Back To Top (27507)

Hi,

I have tried Monitor server instance but it is not showing for same log shipping job it show for other job details.

Please let me know solution?

 

Regards,

SQLDBA

Thursday, November 14, 2013 - 5:11:10 AM - Ravi Back To Top (27494)

Hi Jagul, Thanks for such a nice article. I have a small doubt here, hope you can suggest me a solution.

I have the same set up in my environment for 2005 version. My question is, Is there any way to trace out or find which option is selected in the Initialize Secondary Database step in the available 3 options (Generate full backup of primary, restore an existing backup and secondary database is initialized)? Hope I am clear with my question.

Thursday, September 26, 2013 - 9:09:43 PM - Buddhika Back To Top (26964)

Hi,

I have this requirement. I have two office in two geographical locations. I want to run the same database in both offices, but they needed to be synced to see. Basically the idea is if there is a new record on Location A, I need to have that same record replicated to the location B also. And visa versa. What is the best way I can do for this.

Thursday, August 29, 2013 - 9:11:53 AM - chandra mouli Back To Top (26526)

i did log shippingsuccesfully.... but  restore job not running ...showing the administravi revoking error

 

Tuesday, June 4, 2013 - 2:08:51 PM - Jason Back To Top (25286)

Hi and thanks for the great article.

 

I work in a unique scenario.  We have a production SQL 2008 Server on one network within it's own active directory forrest.  We use a SQL Service Account to manage the SQL Database.  A few blocks away, we have a back up facility on it's own network with it's own active directory forrest.  We also use a SQL Service Account to manage the SQL Database.  Both SQL Service Accounts are named the same, and have the same password.

Is it possible to log ship from our production network to our back up facility network even though the servers are not in the same domain/forrest?  Also, what network ports would need to be opened on the inbound firewall to allow for log shipping.  The network are separated by 2 firewalls, and are on separate VLAN's. 

Thursday, May 23, 2013 - 3:32:42 AM - vinay Back To Top (25092)

1.      what is LSN?

2.      What is Tuf?

1.      Diff b/w readonly & Standalone?

2.      when we use standalone in LogShipping?

Saturday, May 18, 2013 - 10:44:04 PM - Mahmood Back To Top (24033)

 

Hi,

 

I have a question related to monitoring of Log shipping, our requirement is to trigger an alert to notify DBA if the latency of the log shipping exceeds 60 minutes.

 

I am not sure where should I configure that.

 

The queries are

1. If I set a threshold value of 60 minutes in the primary server, by going into secondary settings of the log shipping database. 

 

2. How frequently I need to schedule the restore job which I configured in the 1st step.

 

Thanks,

Mahmood

Friday, May 10, 2013 - 12:31:09 PM - Rajesh Back To Top (23881)

Hi Jugal, Thanks for your article. However i have few doubts on this topic, i really don't know how reasonable they are

Below is what my current log shipping setup.

Primary Server Name: PrimaryServer

Primary Server DB: PrimaryDB

Drives on Primary Server: C,D,E & F

PrimaryDB files location: .mdf(D) & .ldf(E)

Seconday Server Name: SecondayServer

Seconday Server DB: SecondaryDB

Drives on Seconday Server: C,D & E

SecondaryDB files location: .mdf(D) & .ldf(E)

Question1:

If suppose i created a secondary data file(.ndf) for primary database and kept it in F drive on Primary server.In this case, will the secondary data file(.ndf) be created over there in secondary database located on Secondary server? If so, where it will be created i mean in which drive it will be created?

Question2:

As you said,

If you choose "Disconnect User Option" - users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. 
 
If you do not choose "Disconnect User Option" In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.
 
From the second stmt, if suppose users stay connected forever on secondary DB for reporting purpose, then the copy files in copy directory on secondary servers will be committed for a specific threshold hours(let say 72 hours). Next restoration will not be possible though we have t-log backups from past 72 hours, since definitely we will get LSN mismatch error while SQL Agent job attempt to restore the t-logs.
How far the second stmt is correct? What is the use of TUF(Transact Undo File)?

Thursday, April 18, 2013 - 2:51:08 AM - chandrasekhar Back To Top (23411)

Thanks a lot.. Its crystal clear in configuring logshipping....

Thursday, April 18, 2013 - 12:32:42 AM - Roopesh Kavukuntla Back To Top (23409)

 

What an awesome illustration jugal superb

Sunday, April 14, 2013 - 3:19:31 AM - CHANDRA Back To Top (23344)

hi

If you want to change the log shipping schedule time, how can we do?

tell me process ...,


 

Thursday, April 4, 2013 - 7:04:43 AM - Sendhilraja Back To Top (23154)

Thanks,,,,,Very clear to understand the ariticle .......

Saturday, March 9, 2013 - 3:18:10 AM - dinesh Back To Top (22678)

How can remove loggshipping ??

Monday, February 25, 2013 - 8:48:45 AM - stephane Back To Top (22398)

Hello, 

 

your problem is probably due to the use of a workgroup configuration instead of AD.

in case of workgroup configuration you have to create on the 2 servers the same windows user with the same password (for example: winsqlUsr). Then you have to modify on the 2 servers the users of MSSQL service and MSSQL service agent to have them run under this user (winsqlUsr).

then check on the 2 servers your share and windows permission on the shared directory to have this user winsqlUsr have full access...

this should solve your issue.

best regards

Stephane

 

Wednesday, January 30, 2013 - 4:57:07 AM - Pradeep Back To Top (21787)

*** Can you tell me log shipping is possible in a single server ***

Wednesday, January 23, 2013 - 4:25:51 PM - James Back To Top (21661)

I've followed the steps exactly, I keep getting this error:

 

Cannot open backup device '\\primaryserver\DatabaseBackup\Tracker.bak'. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)

I'm positive that the filepath is correct.

I've ensured that the SQL Server Agent service is running under the domain user.

I've ensured that the SQL Server service is also running under the domain user.

The domain user that is running the process has full read write access on both servers, and all shares.

I have logged into the secondary server with the domain account that is running all the services and it can access the share listed above just fine.

 

I'm at a loss here, nothing left for me to troubleshoot; any help would be appreciated!

Wednesday, January 9, 2013 - 6:45:50 PM - Brian Back To Top (21357)

Great article!

 

Question: In step-4 where it says "Click on the Connect… button to connect to the secondary server"; my understanding is that this step is being initiated on the primary server to connect to the secondary SQL Server; in my case I am working in a SharePoint 2010 farm environment and the log shippiong is being set up between two different data center locations and there is no direct connection between the database servers but only between the web front end servers (only the web front end servers have external IP addresses).  How can I connect to the secondary SQL Server instance in one data center from the primary SQL server located in another data center? Do we need to assign external public IP addresses to each of the SQL Server instances in the twp data centers so they can see one another?

 

Thank you in advance for your response,

Brian

 

Tuesday, January 8, 2013 - 10:47:56 AM - Jugal Back To Top (21319)

You can make the folder share & give access to SQL Server Serivce account on that folder. You can give the path as \\ComputerName\Foldername

Tuesday, January 8, 2013 - 9:48:15 AM - dinsa Back To Top (21317)

step no. 3, how to give a path. Just say I a laptop no network folder. My backup is saveed on c: and path is C:\sqlbackup\tes.bak

Would you plz help me to fill both 'Network path to backup folder' and 'If backup folder is located on primary server....' place with example.

Wednesday, January 2, 2013 - 8:31:18 AM - Jugal Back To Top (21228)

Please use AD acccount & give it permission on shared folder.

Tuesday, January 1, 2013 - 10:44:36 PM - Dinesh Back To Top (21222)

It running on local system only.

Tuesday, January 1, 2013 - 9:59:45 AM - Jugal Back To Top (21218)

Hi Dinesh, Please confirm if your services are running under local system account or Windows AD account?

Tuesday, January 1, 2013 - 8:46:59 AM - dinesh Back To Top (21217)

System.Data.SqlClient.SqlError: Cannot open backup device '\\DINESH\ames.BAK'. Operating system error 67(The network name cannot be found.

please let me, i had given network path everyone permision with full right.is there anything missing ?5c

Saturday, December 22, 2012 - 8:18:40 PM - Ogan Back To Top (21095)

I'm setting up log shipping for 2 server that are not in domain. I've gotten to get the initial Backup and restore to the second server succesfull and the second DB goes into Stanby/Read only mode. However, the rest is not working. Btw, it took not only to change the SQL server Agent to a local user, but also changing SQL Server user to a local user as well. For some reason NT Service/MSSQLSERVER user would not be able to read the directory since it was a completely different directory. I may try to put the log directory inside of the data or log directory created by SQL Server. 

anyhow, all 3 jobs on the secondary server are failing.

Thank you in advance for your guidence

-------------------

LSAlert - with the following error.
Log        Job History (LSAlert_Monitoring_Log_ccw-stg-sql2.vpc.ccw)
Step ID        1
Server        CCW-STG-SQL2
Job Name        LSAlert_Monitoring_Log_ccw-stg-sql2.vpc.ccw
Step Name        Log shipping alert job step.
Duration        00:00:00
Sql Severity    16
Sql Message ID    14421
Operator Emailed   
Operator Net sent   
Operator Paged   
Retries Attempted    0

Message
Executed as user: CCW-STG-SQL2\sqluser. The log shipping secondary database AMAZONA-S2LKL4F.test has restore threshold of 45 minutes and is out of sync. No restore was performed for 4165 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.

LSCopy - with the following error.

------------------

 

Log        Job History (LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM)

Step ID        1
Server        CCW-STG-SQL2
Job Name        LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM
Step Name        Log shipping copy job step.
Duration        00:00:31
Sql Severity    0
Sql Message ID    0
Operator Emailed   
Operator Net sent   
Operator Paged   
Retries Attempted    0

Message
Executed as user: CCW-STG-SQL2\sqluser. The step failed.

Log        Job History (LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM)

Step ID        1
Server        CCW-STG-SQL2
Job Name        LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM
Step Name        Log shipping copy job step.
Duration        00:00:31
Sql Severity    0
Sql Message ID    0
Operator Emailed   
Operator Net sent   
Operator Paged   
Retries Attempted    0

Message
2012-12-22 20:00:31.15    *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2012-12-22 20:00:31.15    *** Error: The specified agent_id A9CB02A9-B17F-4D55-AFB4-3954B13D4242 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2012-12-22 20:00:31.17    ----- END OF TRANSACTION LOG COPY     -----

Exit Status: 1 (Error)

 

Log        Job History (LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM)

Step ID        1
Server        CCW-STG-SQL2
Job Name        LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM
Step Name        Log shipping copy job step.
Duration        00:00:31
Sql Severity    0
Sql Message ID    0
Operator Emailed   
Operator Net sent   
Operator Paged   
Retries Attempted    0

Message
2012-12-22 20:00:31.03    *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2012-12-22 20:00:31.03    *** Error: The specified agent_id A9CB02A9-B17F-4D55-AFB4-3954B13D4242 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

Log        Job History (LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM)

Step ID        1
Server        CCW-STG-SQL2
Job Name        LSCopy_ccw-stg-sql1.vpc.ccw_CCW_DM
Step Name        Log shipping copy job step.
Duration        00:00:31
Sql Severity    0
Sql Message ID    0
Operator Emailed   
Operator Net sent   
Operator Paged   
Retries Attempted    0

Message
2012-12-22 20:00:30.98    *** Error: Could not retrieve copy settings for secondary ID 'a9cb02a9-b17f-4d55-afb4-3954b13d4242'.(Microsoft.SqlServer.Management.LogShipping) ***
2012-12-22 20:00:30.98    *** Error: The specified agent_id A9CB02A9-B17F-4D55-AFB4-3954B13D4242 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

-----------------

LSRestore with the following error

Log        Job History (LSRestore_ccw-stg-sql1.vpc.ccw_CCW_DM)

Step ID        1
Server        CCW-STG-SQL2
Job Name        LSRestore_ccw-stg-sql1.vpc.ccw_CCW_DM
Step Name        Log shipping restore log job step.
Duration        00:00:31
Sql Severity    0
Sql Message ID    0
Operator Emailed   
Operator Net sent   
Operator Paged   
Retries Attempted    0

Message
2012-12-22 20:00:30.95    *** Error: Could not retrieve restore settings.(Microsoft.SqlServer.Management.LogShipping) ***
2012-12-22 20:00:30.95    *** Error: The specified agent_id A9CB02A9-B17F-4D55-AFB4-3954B13D4242 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

Thursday, December 20, 2012 - 1:48:27 AM - Raveendra Back To Top (21045)

Q)I follow all steps but I have the following error message

"Cannot open backup device \\server1\DB_logshipping\db.bak operating system error  1326(failed to retrieve text for this error : Reason 1815)

RESTORE FILELIST is terminating abnormally (Microsoft SQL server , Error : 3201)"

Note : the log shipping used between sql server 2008 and sql server 2008 R2

 

A)Hi Hassan  you have to give permissions on Shared folder.The below are the permissions for shared folder.

1)Network

2)Network Service.

The above pemissions are the solution for ur problem.

Regards,

Raveendra.

Tuesday, October 23, 2012 - 3:19:28 AM - Yasir Baig Back To Top (20046)

Good article 

but when i'm applying the same steps as you mentioned above but on the second step of "Restoring Backup to secondary database[db name]"  status Error  Message: Canot open backup device "\\hscvm\Replication\dbname.bak". Operating System Error 5 (Access Denied). RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error:3201)

 

can u help me in thie regards.

 

Thursday, October 18, 2012 - 6:49:20 AM - Prabhakar lam Back To Top (19971)

Good article

Thursday, October 11, 2012 - 6:46:16 AM - Rajumella Back To Top (19873)

That is a verygood and more cleared explanation I have ever found.

Great Work.

Tuesday, September 25, 2012 - 1:52:25 PM - Jugal Back To Top (19675)
f you choose "Disconnect User Option" - users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. 
 
f you do not choose "Disconnect User Option" In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.
 
Yes in SQL Server 2012 as well. 

Tuesday, September 25, 2012 - 9:45:03 AM - Kapil Back To Top (19674)

Hi Jugal, this is a very good summary. Thanks. A question, in SQL Server 2008, I am told that when applying the logs to the target/secondary database, users have to be kicked off/disconneted. Is this true in SQL 2012 also? By the screen shoots, it looks like we could choose not to disconnect users.

Have you tried/confirmed this at all?

Monday, August 6, 2012 - 10:59:21 AM - Jugal Back To Top (18933)

@WannabeDBA, Can you please paste the error message? SQL Server Service Accounts  reuires the Read\Change permission.

Sunday, August 5, 2012 - 10:56:47 PM - Pete Ocasio Back To Top (18923)

I setup a log shipping configuration without setting up a monitoring server.

I have since changed my mind about this and will like to setup the monitoring server which will be the monitor server.  Will setting up the monitoring server break the current log shipping configuration by setting it up at this late date?  Will it be better to setup the alerts as a separate job(s) that will look across servers without setting up a monitoring server?

 

Wednesday, August 1, 2012 - 8:53:58 AM - wannabeDBA Back To Top (18870)

hi could add what rights the services of both primary and secondary need for the file shares..

 

I keep getting into problems.. Is it the SQL Server service or the Agent for the primary that needs permissions to the folder that its backing up to? and same for the secondary reading the folders... I thought they both only ever needed read/write.. but i got errors on permissions doing it that way..

 

 

Friday, July 13, 2012 - 10:17:52 AM - Jhansi Back To Top (18500)

Log shipping for sql has started from version 2000 of SQL Server so the 1st point in the minimum requirement can be changed as sql server 2000 version or later.. but still same versions of primary and secondary is required while performing log shipping...

 

Thursday, June 21, 2012 - 12:48:38 PM - Jugal Back To Top (18159)

Can you check for the permision of the SQL Agent Service account on backup share and let us know... Agent Service account must have the read/write permission on the backup share.

Thursday, June 21, 2012 - 8:32:00 AM - Hassan Back To Top (18150)

I follow all steps but I have the following error message

"Cannot open backup device \\server1\DB_logshipping\db.bak operating system error  1326(failed to retrieve text for this error : Reason 1815)

RESTORE FILELIST is terminating abnormally (Microsoft SQL server , Error : 3201)"

Note : the log shipping used between sql server 2008 and sql server 2008 R2

 

Monday, May 28, 2012 - 3:14:30 AM - shivaji Back To Top (17679)

Excellent.Thanks for your clear explanation.................Great job

Wednesday, May 2, 2012 - 11:12:44 AM - Jugal Back To Top (17233)

Thanks Abdul

Tuesday, May 1, 2012 - 9:13:54 PM - Abdul Haseeb Back To Top (17227)

That is a good and more cleared explanation I have ever found.

Great Work.

Tuesday, February 22, 2011 - 7:42:57 AM - Dana Back To Top (12998)

Thank you for the explinations.  I have a document with similar screen shots that I developed for internal use, now I have the background.














get free sql tips
agree to terms