SQL Server disaster recovery

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

Can I do that through GUI, or need to go the the command base ?

you can do it in the GUI:
http://technet.microsoft.com/en-us/library/ms177429.aspx

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

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

Sure, will take a look on this.

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

Tks

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

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

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.

No, this option is available when backing up the transaction log on the source database (see attached), Tks
SQL-Backup.png

Tks