[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Backup and Restore on SQL Server

Below is my SQL backup and recovery plan, is it correct ? Is there any similar scenario for reference

Backup SQL Server
1. Full SQL database backup at night
2. SQL Log backup in lunch time - with option "Truncate the transaction log"

Restore database on the same server when database corrupt
1. Backup SQL Log backup - with option "Back up the tail of the log, and leave database in the restoring state"
2. Restore the full SQL database backup at night
      Recovery state : RESTORE with NORECOVERY
      uncheck "Take tail-log backup before restore
3. Restore the SQL log backup in lunch time.
4. Restore the SQL tail log in #1, with RECOVERY.

Restore database on the different server
1. Backup SQL Log backup on the local server
2. Restore the full SQL database backup at night  on the other server
      Recovery state : RESTORE with NORECOVERY
      uncheck "Take tail-log backup before restore
3. Restore the SQL log backup in lunch time. on the other server
4. Restore the SQL tail log in #1, with RECOVERY on the other server
0
AXISHK
Asked:
AXISHK
2 Solutions
 
Jim P.Commented:
Ok. Depening on the numv=ber of transaction in a day, I would do a log backup backup every 1-2 n hours. That way the log doesn't grow too big.

Also rarely do you need to rorry bbor a tran log restor after the first 24 hours, Do the user want to lose a whole day;w wroth day's transactions?
0
 
AXISHKAuthor Commented:
So, Do the above steps correct ? Current backup is full backup at night and transaction log backup at lunch.

Supposing I need to restore the database at 5:00PM evening,

1. backup the transaction log for the current database.
2. Restore the database in NONRECOVER state
3. Restore the transaction log in lunch time
4. Restore the transaction log in #1 and make database in RECOVERY state.

Any comment ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't forget that with only 1 transaction log backup by day you'll have the tlog file growing during 24h and for what I saw in another question from you, that's an issue that you don't want to have.
For reducing transaction log size you need to make tlog backups more often, depending on the business demands.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
AXISHKAuthor Commented:
Noted. Are the steps in my 1st thread correct ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, but you need a STOP AT statement so you can define the point of time that you want to recover the database. If you don't define the point of time it will restore all database so you might keep the issue with the database.
0
 
AXISHKAuthor Commented:
For "STOP AT" , is it the argument when restoring the Transaction log ?

I see an article mentioning about tail log backup and restore. Is it just the same as the normal transaction log backup and restore or I need to handle it in a different way ?


Tks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You only need a tail backup if the last transaction log backup doesn't cover the time until you want to restore. Even it's a little bit different you handle it like a normal tlog backup.
0
 
AXISHKAuthor Commented:
To clarify my understanding, tail log backup can only be applied on the production database if the server can still be connected. ie. backup the last log change and apply it to another server. In case the whole server is crashed, I only use my stored transaction copy to restore the DB in other server, correct ?

One last question, The database and log are on different physical drive, (C:) and (D:) respectively.

Supposing that C is totally crashed, can I still backup the log file in this case as the database is not available ? How can I manually move the log file to another server and then apply the logs to get a complete database copy.

Great Tks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you can't connect to a server then you won't be able to do nothing so in these occasions you need to restore from the backups that you have in another location (server, tapes, ...).

Supposing that C is totally crashed, can I still backup the log file in this case as the database is not available ? How can I manually move the log file to another server and then apply the logs to get a complete database copy.
This is a case for using a tail log backup (data files unavailable but log files are) but you don't need to move the log file. What you do in this scenario is perform a tail log backup and restore the backup in another server with MOVE option where you set the new locations for the database files.
0
 
AXISHKAuthor Commented:
"perform a tail log backup and restore the backup in another server with MOVE option"  - where should I add the 'MOVE' option ?

Is it the command that posted in my previous question ? Is that the command to backup the tail log when database is not accessible ?

BACKUP LOG DatabaseName
   TO DISK = 'C:\ DatabaseName.TRN'
   WITH NO_TRUNCATE;
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The MOVE option should be performed in the RESTORE command, not BACKUP.
0
 
ZberteocCommented:
I recommed you do this:

1. 1 weekly full backup
2. 1 Daily differential backup
3. Transaction Log backup every 10-15 min, or if you don't need that often, and I don't see why, Brent Ozar recommends every minute(!), then you can do it once an hour.

With transaction log backup  the often you do it the less data you will loose in a database/server failure and the less size it will grow. You NEVER truncate the log file because it will grow right back as it needs anyway, which affects performance.

If you do it in a a professional and neat way I recommend you the Ola Hallegren maintenance solution where there is a backup part:

https://ola.hallengren.com/sql-server-backup.html

I recommend you to look into integrity check and especially index optimization, which is the best you can find.
0
 
AXISHKAuthor Commented:
Tks
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now