bijualex
asked on
SQL 2005 Log Shipping
Hi
I am trying to setup log shipping on my test server. This is what I did.
I created a test database on primary server (SQL 2005) with full recovery model
Took a full backup of this database and restored on secondary server (SQL 2005)
On primary server, selected the database and enabled log shipping using wizard. I left most of the options to default. Selected the "Network path to Backup folder" as a shared folder on secondary server (shared folder with write permission to everyone). Same folder is mentioned as "Destination folder for copied files". Selected "No, the secondary database is initialized".
After this two jobs are created in primary server and three jobs created in secondary server. The two jobs in primary server and two jobs in secondary server executed without any errors. But the job LSRestore_PrimaryServer_Te stDB on secondary server fails with the below messages
Date 08/11/2010 11:15:01 AM
Log Job History (LSRestore_PrimaryServer_T estDB)
Step ID 1
Server SecondaryServer
Job Name LSRestore_PrimaryServer_Te stDB
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CHSAPTEC\Administrator. The step failed.
Any ideas?
Thanks.
I am trying to setup log shipping on my test server. This is what I did.
I created a test database on primary server (SQL 2005) with full recovery model
Took a full backup of this database and restored on secondary server (SQL 2005)
On primary server, selected the database and enabled log shipping using wizard. I left most of the options to default. Selected the "Network path to Backup folder" as a shared folder on secondary server (shared folder with write permission to everyone). Same folder is mentioned as "Destination folder for copied files". Selected "No, the secondary database is initialized".
After this two jobs are created in primary server and three jobs created in secondary server. The two jobs in primary server and two jobs in secondary server executed without any errors. But the job LSRestore_PrimaryServer_Te
Date 08/11/2010 11:15:01 AM
Log Job History (LSRestore_PrimaryServer_T
Step ID 1
Server SecondaryServer
Job Name LSRestore_PrimaryServer_Te
Step Name Log shipping restore log job step.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CHSAPTEC\Administrator. The step failed.
Any ideas?
Thanks.
On the restore tab, under database state when restoring backups, choose the no recovery mode.
On Books Online, type in
How to: Enable Log Shipping (SQL Server Management Studio).
It gives a step by step approach on how to configure it correctly.
On Books Online, type in
How to: Enable Log Shipping (SQL Server Management Studio).
It gives a step by step approach on how to configure it correctly.
ASKER
Thank you very much for your feedback. No it was not restored with no recovery. I restored it again with "no recovery " option and now the database on the sec server is showing TestDB (Restoring...). Then I started the log shipping wizard again, but the time when I choose sec server, I get the below error.
Cannot open database "TestDB" requested by the login. The login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4060)
The credentials are given correctly, may be because the database is in non-operational mode? How to select the sec server here?
Cannot open database "TestDB" requested by the login. The login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4060)
The credentials are given correctly, may be because the database is in non-operational mode? How to select the sec server here?
Hi,
So it's restoring correctly now ?
You just can't connect to any of the databases on the secondary server to query the data is it ?
So it's restoring correctly now ?
You just can't connect to any of the databases on the secondary server to query the data is it ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I created an empty database in the sec server and then selected "Generate a full back and restore to secondary" option and it worked :)
did you leave the database non-operational (restore with norecovery) ?
You have to do this so the transaction logs can be restored.