Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Step By Step SQL Server Log Shipping

MSSQLTips author Jugal Shah By:   |   Read Comments (58)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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 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/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.

Permissions

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

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 primarly 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 Log-Shipping:

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

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.

setting up log shipping for sql server

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

right click on the database in ssms

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 Enterprise 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.

transaction log backup settings in ssms

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

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 tab

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.

intialize secondary database

Copy Files Tab

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.

specify where the log shipping copy job will copy the t-log backup files

Restore Transaction Log Tab

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

create the restore on the secondary server

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.

log shipping monitoring will notify us in case of any faulures

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.

monitoring can be done from the source server, target server or a separate SQL Server instance.

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

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.


Last Update: 2/22/2011


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.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, September 16, 2014 - 2:20:20 AM - satya Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Very helpful and clear notes.

 

Easy to understand and clear the doubts.

 

Thanks Jugal.

 


Thursday, June 12, 2014 - 2:34:38 PM - sai Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Great Article


Monday, April 21, 2014 - 10:42:50 AM - SIVA Read The Tip

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 Read The Tip

Best Explaination about Log Shipping...

Thank you so much


Tuesday, March 25, 2014 - 10:54:41 AM - Wallace Read The Tip

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 Read The Tip

helpful.... :-)


Monday, March 17, 2014 - 10:36:35 AM - satish Read The Tip

Thank you so much Jugal for the nice article.


Friday, March 14, 2014 - 2:11:16 PM - binosha Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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


Friday, November 15, 2013 - 5:10:39 AM - SQLDBA Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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

 


Tuesday, June 04, 2013 - 2:08:51 PM - Jason Read The Tip

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. 


Tuesday, June 04, 2013 - 12:52:23 PM - sunny Read The Tip

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text edi

Hi... am configuring Logshipping on SQL Server 2008r2 but i am getting the error:3201

in my primary, secondary servers  are running on same editon and i have created 2 folders with the name testing_backup, testing_copy on that 2 folders i have given the full access but still am getting the error.

 

please provide me the solution for this.

very urgent.

 

tor like NotePad before copying the code below to remove the SSMS formatting.

 


Thursday, May 23, 2013 - 3:32:42 AM - vinay Read The Tip

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 Read The Tip

 

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 Read The Tip

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 Read The Tip

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


Thursday, April 18, 2013 - 12:32:42 AM - Roopesh Kavukuntla Read The Tip

 

What an awesome illustration jugal superb


Sunday, April 14, 2013 - 3:19:31 AM - CHANDRA Read The Tip

hi

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

tell me process ...,


 


Thursday, April 04, 2013 - 7:04:43 AM - Sendhilraja Read The Tip

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


Saturday, March 09, 2013 - 3:18:10 AM - dinesh Read The Tip

How can remove loggshipping ??


Monday, February 25, 2013 - 8:48:45 AM - stephane Read The Tip

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 Read The Tip

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


Wednesday, January 23, 2013 - 4:25:51 PM - James Read The Tip

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 09, 2013 - 6:45:50 PM - Brian Read The Tip

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 08, 2013 - 10:47:56 AM - Jugal Read The Tip

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 08, 2013 - 9:48:15 AM - dinsa Read The Tip

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 02, 2013 - 8:31:18 AM - Jugal Read The Tip

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


Tuesday, January 01, 2013 - 10:44:36 PM - Dinesh Read The Tip

It running on local system only.


Tuesday, January 01, 2013 - 9:59:45 AM - Jugal Read The Tip

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


Tuesday, January 01, 2013 - 8:46:59 AM - dinesh Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Good article


Thursday, October 11, 2012 - 6:46:16 AM - Rajumella Read The Tip

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

Great Work.


Tuesday, September 25, 2012 - 1:52:25 PM - Jugal Read The Tip
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 Read The Tip

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 06, 2012 - 10:59:21 AM - Jugal Read The Tip

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


Sunday, August 05, 2012 - 10:56:47 PM - Pete Ocasio Read The Tip

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 01, 2012 - 8:53:58 AM - wannabeDBA Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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


Wednesday, May 02, 2012 - 11:12:44 AM - Jugal Read The Tip

Thanks Abdul


Tuesday, May 01, 2012 - 9:13:54 PM - Abdul Haseeb Read The Tip

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

Great Work.


Tuesday, February 22, 2011 - 7:42:57 AM - Dana Read The Tip

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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.