Solved

Backup and Restore on SQL Server

Posted on 2014-09-16
13
297 Views
Last Modified: 2014-09-17
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
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 40327275
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
 

Author Comment

by:AXISHK
ID: 40327317
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327378
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:AXISHK
ID: 40327502
Noted. Are the steps in my 1st thread correct ?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327511
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
 

Author Comment

by:AXISHK
ID: 40327516
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327520
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
 

Author Comment

by:AXISHK
ID: 40327568
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40327572
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
 

Author Comment

by:AXISHK
ID: 40327597
"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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327602
The MOVE option should be performed in the RESTORE command, not BACKUP.
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 40327815
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
 

Author Closing Comment

by:AXISHK
ID: 40329458
Tks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question