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

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

Great Work.

Thanks Abdul

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

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

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.

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

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

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?

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

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?

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

Great Work.

Good article

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.

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.

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) ***

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

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

It running on local system only.

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

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.

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

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

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!

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

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

How can remove loggshipping ??

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

hi

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

tell me process ...,

What an awesome illustration jugal superb

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

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,

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

1.      what is LSN?

2.      What is Tuf?

1.      Diff b/w readonly & Standalone?

2.      when we use standalone in LogShipping?

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

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.