SQL Server disaster recovery

I have a production and a backup server. SQL Daily backup is run on the production server and then copy to the backup server. Afterwards, the file is restored into the backup server.

In case the production server has probelm, I should able to copy the log file and then restore on the backup server to fully recover the missing transaction for the day. However, I can't find this option in MS SQL Management Studio. Should I carry this in GUI ? How can I archieve it ?

Tks
AXISHKAsked:
Who is Participating?
 
Koen Van WielinkConnect With a Mentor Business Intelligence SpecialistCommented:
Pretty sure you can just use the standard option of truncating the log. Just make sure you have the log shipping started on the source database before you take the backup otherwise you'll have a gap between the full backup and the start of the log backups.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot just copy the .ldf (log) file and work with that one.

the correct process is this:
* full backup (db.bck) + regular transaction log backups (db.trn)
* full restore without recovery (using the db.bck), the db remains in "recovery" status (RESTORE DATABASE ... WITH NORECOVERY)
* apply each and every transaction log backup (db.trn) as they come in, keeping the db in recovery status (RESTORE LOG ... WITH NORECOVERY).


when you want then to put the backup server "life", you take the last log file backup, and apply it WITH RECOVERY instead, or, if you don't have any more, you do:
RESTORE DATABASE dbname WITH RECOVERY

see also:
http://technet.microsoft.com/en-us/library/ms187495.aspx
0
 
AXISHKAuthor Commented:
Can I do that through GUI, or need to go the the command base ?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
AXISHKAuthor Commented:
Sorry, still can't identify the setting ? Where can I select "regular transaction log backups" and "apply each and every transaction log backup (db.trn) as they come in" through the GUI ? Can you guide me through screen dump ?

Tks
0
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
You would probably want to use MS SQL's log shipping feature to achieve Guy's method explained above. It starts with the restore of a backup in norecovery mode, after which the production server "ships" the log backups to the backup server at regular intervals, which are then restored automatically. How often you want to ship a log depends on your needs (how much data loss is acceptable in case of a disaster, how much network bandwidth do I have available, etc).
You can check here for more information and how to set it up:

http://technet.microsoft.com/en-us/library/bb895393(v=SQL.105).aspx
0
 
AXISHKAuthor Commented:
Sure, will take a look on this.

But can we backup and restore trx log through GUI  ??

Tks
0
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
The log shipping method does this on your behalf. Once it's set up, you don't have to worry about the backup and restore, only the first setup has to be done this way. It can be done through the GUI, but with limited settings. It's better to do the initial restore of the database and logs through the Query analyser instead.
I had to do this myself not too long ago without any previous experience, and it's not too hard. Here's what I did:

- Start the log shipipng on the primary server database as described in the steps. This will start to create logs.
- The following day, take your nightly backup and restore it on the secondary server with norecovery.
- On the primary server, run the attached script called "create log restore commands". This script generates a restore command for each of the log backups created AFTER the last full backup, which is what you need to get your secondary database up to date. Make sure to replace yourDBname with your actual database name, leaving the single quotes.
- Go to the secondary server where your backup is restored and run the log restore commands generated in the previous step. This shoudl restore all the logs from the primary server to the secondary server.
- Enable the log shipping to the secondary server as described in the link I sent earlier.
- Keep track of the log shipping for a while through the log shipping monitor. You can get there by right clicking on the server name in the management studio, then going to reports - standard reports - transaction log shipping status.

It might take some time for the database to be fully in sync depending on the size of your log backups and backup/restore intervals. In some cases it took the better part of a day for me, initially thinking I made a mistake but the following day discovering that the logs were being restored successfully.
Good luck, it's not as hard as it looks.
Create-log-restore-commands.sql
0
 
AXISHKAuthor Commented:
When backing up the transaction log, there is an option "Back up the tail of the log, and leave the database in the restoring state  "
Actually, how does it affect the source and target database ? During the recovery on the target, I can select "RESTORE with NORECOVERY" and the it will also leave in norecovery state. What's that option for ? Tks
0
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Not quite sure to be honest. I didn't manually back up the transaction log. I started the log shipping on the primary first, then restored a full database backup from the night after. At that point you can use the periodic log backups created by the log shipping to bring the target server up to date. The script I attached can be used to create the relevant restore commands.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>When backing up the transaction log, there is an option "Back up the tail of the log, and leave the database in the restoring state  "

I think you confused some of the options.
when you do a FULL backup, you can include the tail of the log.
however, this has nothing to do with the db being in restoring state... you can only have that option while RESTOREing, not while BACKUPing ...
0
 
AXISHKAuthor Commented:
No, this option is available when backing up the transaction log on the source database (see attached), Tks
SQL-Backup.png
0
 
AXISHKAuthor Commented:
Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.