November 4, 2007 at 8:16 pm
I've attached a screenshot of my monitoring server ( i cant do this the right way). If you view the screenshot you will notice that the last backup file is the only file that changes from the default first_file_etc to the northwind transaction log file. I set the threshold at 17 min (im using northwind as a test database for log shipping) and after the threshold limit the monitoring server begins to have an error. Did i configured the log shipping incorrectly?? what did i miss in the setup?? need your help thanks
"-=Still Learning=-"
Lester Policarpio
November 5, 2007 at 1:48 am
Transaction log is not getting copied to the secondary server so restore is not happening. Check copy schedule job history on secondary and make sure its happen successfully. You can also check physical path of destination folder and make sure file is getting copied or not.
November 5, 2007 at 2:55 am
Ratnesh Kumar Dwivedi (11/5/2007) You can also check physical path of destination folder and make sure file is getting copied or not.
YEah your right the destination folder is not updated with the copy of the data in primary server what could be wrong the path?? the path i included is visible in the destination server. anyone have a detailed documentation of setting up log shipping???
"-=Still Learning=-"
Lester Policarpio
November 5, 2007 at 4:07 am
Path is ok access control is issue, the credential which is running SQL server Agent on secondary server should have access on primary server folder so it can copy transactional log, either you have to share this folder for everyone or create one domain user which will run SQL server agent on both server and have access on both server folders.
November 5, 2007 at 7:09 pm
Hi this is the error message in the log shipping monitor. Transaction log backups are now seen in the secondary server folder its the log shipping restore (in the secondary sqlserveragent) that is having an error
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4306: [Microsoft][ODBC SQL Server Driver][SQL Server]The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
"-=Still Learning=-"
Lester Policarpio
November 5, 2007 at 9:56 pm
Hi, Can you let me know the entire process that you have done for setting log shipping in points. i will correct the process.
1. Identify the primary and secondary server.
2. Share the folder on both server.
3. Make sure the folder is accessible by the credential running SQL server Agent.
4. Latest Database database copy should be restored either by log shipping process or by restoring the latest backup in stand by mode or no recovery mode so it can accept the additional transactional log file. This is the error that you are getting.
Just take one scenario where i have to restore full backup and some transactional log file.
Regards,
Ratnesh
November 5, 2007 at 11:10 pm
Thanks Ratnesh Kumar Dwivedi for giving time to answer my questions so here it is....
Assuming Production ServerName = A, Destination and log shipping monitor = B
1. i pick the northwind database as the test database to be log shipped
2. checked the log shipping option
3. Click NEXT until i get to the "Specify the database backup plan" and set it to 2:00 PM assuming now is 1:50 PM.
4. Choose D:\MSSQL\BACKUP as database backup directory. the BACKUP folder is shared to everyone with full control permission.
5. Choose D:\MSSQL\BACKUP also for the transaction log backup
6. In the "Specify the transaction log share" i put \\A\BACKUP
7. In the "Specify the log shipping destination I click ADD
8. These are the data inside the "add Destination Database"
a.) Server Name = B
b.) Transaction Log Destination Directory = D:\MSSQL\BACKUP
c.) I Choose "Use existing database (no initialization)" since the database is already in the destination server
d.) In the "Database Load State" I choose "Standard Mode" and checked the "Terminate users in database (Recommended)"
e.) i checked "Allow database to assume primary role"
f.) for the transaction log backup of letter e i inputed \\B\BACKUP. <-- this is also shared and permission is full in everyone.
g.) Click next until i get to the "Specify the log shipping monitor server information". In here i choose use sql server authentication and choose server B for the SQL Server i inputed no passwords
h.) Click next until i get to finish
- the log shipping is setup successfully thats what i did. Ohh and by the way in the destination server sqlserveragent i enable the "transaction log backup job for DB maintenance plan " because it is set as disabled. At first it will execute well the transaction log backup. Even the transaction log backup appeared in the destination server directory D:\MSSQL\BACKUP but after meeting the out of sync alet threshold the log shipping monitor will say that the destination server is out of sync
I hope you could follow up with the procedure i made. This is my first time using log shipping thats why im not familiar with it. Another thing is that both sqlserveragent of the production and destination are NOT using localsystem accounts (just in case theres an issue about that). both D:\MSSQL\BACKUP folders are shared (\\A\BACKUP), (\\B\BACKUP) using Server A i can access shared folder of Server B and vice versa. Many thanks to all.....
EDIT The northwind database in the destination server is in a read-only state
"-=Still Learning=-"
Lester Policarpio
November 5, 2007 at 11:34 pm
Now i understood the probleom, Database is not in Standby mode or No recovery mode.
To resolve this either you have to take a fresh backup and restore in stand by mode or create one test database and restore by logshipping process it self.
I would suggest you to create one database and setup log shipping for that it will work.
Regards,
Ratnesh
November 6, 2007 at 12:06 am
Thanks!!!! I think i have an idea about the error i encountered Do you think the process i did is right?? the only problem is that i must first restore the database in standby mode? I can do it using Enterprise Manager right??
"-=Still Learning=-"
Lester Policarpio
November 6, 2007 at 12:18 am
Yes, small correction either stand by or No Recovery mode. In stand by mode you can use database for reporting puerpose. Other security things you have to enable when familiar with this like sharing file in default folder for everyone.
Regards,
Ratnesh
November 6, 2007 at 12:31 am
The "Transaction Log Backup job for db maintenance plan" is now on error.
I am now getting this error message.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'DBA' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally.
"-=Still Learning=-"
Lester Policarpio
November 6, 2007 at 1:53 am
use master
restore database database name with recovery
go
execute this, database will come in recovery mode. I didn't understand why you are taking backup of secondary database if i am right. Primary database will be in operational mode secondary will be in stand by mode.
Regards,
Ratnesh
November 6, 2007 at 2:00 am
What i did is that i restore the database using EM choosing the option in recovery completion state "leave database read-only and able to store additional transaction logs" then the database is restored and in the state of read-only. I applied the process which i wrote in this topic then that error occured
"-=Still Learning=-"
Lester Policarpio
November 6, 2007 at 2:17 am
but database is giving error is related to backup.
delete the created database for log shipping(if created)delete the backup and transaction log backup from backup folder.
Create database Test on primary server and secondary server, take a full backup of primary server test database and restore on secondary test database in stand by mode.then set log shipping and select No, secondary databse is initialized.
regards,
Ratnesh
November 6, 2007 at 2:34 am
I made a database test in server A then make a backup of it then restore it to server B in read-only mode then apply the log shipping but after the successfull setup of the log shipping same error occurs. DO you think the problem is that the Server A cant put the transaction log backup in Server B??
"-=Still Learning=-"
Lester Policarpio
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply