June 19, 2009 at 8:45 am
ps (6/19/2009)
yes restore in norecovery mode. and you cannot backup log if your source database is not in FULL recovery mode.restore the full backup in norecovery mode and restore the log backup in recovery mode as i mentioned in the queries in last post. then query the new database to see if everything is fine.
I use your way for Backup/Restore LOG: first time is OK, from second time it fails:
BACKUP LOG successfully processed 0 pages in 0.128 seconds (0.000 MB/sec).
Server: Msg 4306, Level 16, State 1, Line 7
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.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
June 19, 2009 at 8:52 am
thats right. It'll fail 2nd time in this case.
the logic is you restore full backup on the secondary and all transaction logs in sequence in norecovery mode. In log shipping this continues untill ur primary db has crashed and you want ur secondary to pick up. in that case u put the secondary db from norecovery to recovery mode wherein it'd be accessible to users for opreration.
This was a test case so we assumed that after the restoration of 1st log file there was no more log backups were coming so we restored the log in recovery mode(which implies it wont allow restoration of any more log files).
Once u restore a log in recovery mode, or you put the database in recovery mode, you cannot restore logs further.
I hope the concept of log backups is clear!!
U can now start setting up log shipping by taking help of the URL i posted earlier or even urs..
June 19, 2009 at 11:07 am
Dear PS
Now I'm also clear:
- No need SQL Enterprise version
- Backup FULL one or more times daily, backup LOG frequency hourly
- Quite same as Oracle Data Guard, but no auto log (archive) file generate, no auto transfer log file to standby site, no auto apply log file to standby. Oracle is better
- No need to apply DB to standby site, just need store the latest FULL backup file and backup log files. If main site fails, restore the latest FULL backup and then backup log files, file by file
Is this correct?
June 20, 2009 at 1:37 am
thang_ngo_2002 (6/19/2009)
Dear PSNow I'm also clear:
- No need SQL Enterprise version
- Backup FULL one or more times daily, backup LOG frequency hourly
- Quite same as Oracle Data Guard, but no auto log (archive) file generate, no auto transfer log file to standby site, no auto apply log file to standby. Oracle is better
- No need to apply DB to standby site, just need store the latest FULL backup file and backup log files. If main site fails, restore the latest FULL backup and then backup log files, file by file
Is this correct?
In log shipping, you only need to move FULL backup once(even if u're backing up ur source database on a daily basis). If you're taking hourly log backup of the source database, you only need to copy the log files to the target db hourly and restore it on the secondary.
** as far as i remember, in oracle, only transactions are recorded in the redo file, where as in sql server every change to the database is recorded in the transaction log file (i'm not sure on oracle part, you might want to validate this). so even if you're making structural change to the database, those changes will be propagated by restoring log files only.
As far as auto log transfers is there, you can write a job that copies the log files from primary to secondary automatically and restores it on the target automatically.. No manual intervention required.
If the primary fails, the only thing u need to do manually is try to backup the tail log (changes to the log after last log backup) and restore it on secondary and put secondary into recovery mode. THis is the only manual intervention here.
I'd suggest u to go through the URLs you posted as well as I posted, it contains instruction on how to automate log shipping if u're not using enterprise edition.
If you were using enterprise edition, things would have been much easier and simpler - on click of few buttons.
June 20, 2009 at 1:40 am
thang_ngo_2002 (6/19/2009)
Dear PSNow I'm also clear:
- No need SQL Enterprise version
- Backup FULL one or more times daily, backup LOG frequency hourly
- Quite same as Oracle Data Guard, but no auto log (archive) file generate, no auto transfer log file to standby site, no auto apply log file to standby. Oracle is better
- No need to apply DB to standby site, just need store the latest FULL backup file and backup log files. If main site fails, restore the latest FULL backup and then backup log files, file by file
Is this correct?
In log shipping, you only need to move FULL backup once(even if u're backing up ur source database on a daily basis). If you're taking hourly log backup of the source database, you only need to copy the log files to the target db hourly and restore it on the secondary.
** as far as i remember, in oracle, only transactions are recorded in the redo file, where as in sql server every change to the database is recorded in the transaction log file (i'm not sure on oracle part, you might want to validate this). so even if you're making structural change to the database, those changes will be propagated by restoring log files only.
As far as auto log transfers is there, you can write a job that copies the log files from primary to secondary automatically and restores it on the target automatically.. No manual intervention required.
If the primary fails, the only thing u need to do manually is try to backup the tail log (changes to the log after last log backup) and restore it on secondary and put secondary into recovery mode. THis is the only manual intervention here.
I'd suggest u to go through the URLs you posted as well as I posted, it contains instruction on how to automate log shipping if u're not using enterprise edition.
If you were using enterprise edition, things would have been much easier and simpler - on click of few buttons.
EDIT :- Double post coz of internet issue :ermm:
June 22, 2009 at 8:44 pm
OK, you are right.
Could you please show me the way to switch Standby DB from ReadOnly to main DB after apply LOG backup files
I try to uncheck "Read Only" option in DB but it's not successful
Thanks
June 22, 2009 at 9:02 pm
thang_ngo_2002 (6/22/2009)
OK, you are right.Could you please show me the way to switch Standby DB from ReadOnly to main DB after apply LOG backup files
I try to uncheck "Read Only" option in DB but it's not successful
Thanks
to bring a database from standby mode to normal mode, you write something like this.
restore database test with recovery
Also, if you have manually setup log shipping, you may want to bring tail-log from your primary server and restore it on the secondary server to minimize data loss. and then put the database in normal read/write mode.
This URL will help you understand what is tail log backup and how do you take it.
http://msdn.microsoft.com/en-us/library/ms179314.aspx
If you're working on enterprise edition, sp_change_primary_role and sp_change_secondary_role stored procs will help you change the role of primay and secondary databases.
Refer http://msdn.microsoft.com/en-us/library/aa215392(SQL.80).aspx"> http://msdn.microsoft.com/en-us/library/aa215392(SQL.80).aspx
June 23, 2009 at 1:39 am
When I try "restore database test with recovery", I have this error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Even no user uses it and I kill all locks related to this DB
June 23, 2009 at 2:14 am
1. drop all connections by running alter database mydb set single_user with rollback immediate
2. select master database as active db and then run the query.
EDIT:- corrected query.
June 23, 2009 at 3:09 am
You're right. I've done
How to get the last LSN of current DB, how to get current LSN of file created by Backup Log?
June 23, 2009 at 3:15 am
thang_ngo_2002 (6/23/2009)
You're right. I've doneHow to get the last LSN of current DB, how to get current LSN of file created by Backup Log?
restore headeronly from disk='your backup file name'
This will give you first LSN and Last LSN that is included in the backup file
Refer http://msdn.microsoft.com/en-us/library/ms178536.aspx
June 23, 2009 at 4:28 am
Now I'm clear.
Thanks very much
June 23, 2009 at 6:48 am
We were like studying backup/restoration and we forgot the main topic!!
Did you automate log shipping? I'm sure you're not running enterprise edition.
June 23, 2009 at 7:54 pm
Yes, I've done in Standard version.
I plan to Backup Log but not apply them immediately to Standby and I use some Windows tool to synchronize these files from Primary to Standby site
June 23, 2009 at 8:27 pm
Glad you're finally done with your DR using log shipping.:-)
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply