Questions on howto properly setup for backup and restorign
-
Hello everyone!
I am pretty new to SQL here. Been using it for about 3 months now.
I have some questions on how to properly setup my SQL server to be backed up properly so I can restore as needed. Let me lay down some ground work.
We have one SQL server with one database, our primary database. We are using Veritas BackupExec 11D for our backup solution (SQL option).
We have LT-03 backup tape, with gives us 400gb backup and 800gb when compressed. our Database, with logs, is about 55gigs. We have plenty of network bandwidth to have speedy backups.
here is my question and please bare with me, I am really new and learning here.
Right now, we have setup backup exec to backup the databse FULL each night. This is ok for us because we have the space on our tapes. My question comes with regards to the transaction logs.
In the veritas agent, I have the option of backing up the logs and either truncating them, or not truncating them. The question is, im not sure which way to go.
Anyone want to lend some suggestions?
Such as, should I keep the backup recover method as FULL or should I switch to SIMPLE?
Secondly, since I can backup the database FULL each now, what would be the best way to backup the t-logs? Should I backup up the t-logs on say Friday and truncate them that same night and then just backup the logs Monday-Thursday and NOT truncate them? That would mean that I would need to keep Friday-Thursday in case of a restore?
Little fuzzy and nervous here. All help is greatly appreciated.
Thanks,
Jas
-
How important is your data?
How much does it change?
If you do full backups once a night (midnight) and your system crashes at noon, can you afford to lose 12 hours of data? If it crashes at 1159PM can you afford to lose almost 24 hours of data?
TLog backups allow you to do a restore to a specific point-in-time.
-SQLBill
-
Hi, thanks for your reply. Here are some answers and more info.
Our data is very important. holds all of our customer data.
Change is daily, but not a high number of transactions. What I mean is, lots of data change on a daily basis is very very minimal.
Probably could afford to lose 12 hours of data at the most. more than that might be stretching it.
Looking at the "files" on the server, they are this big:
Data file: 15gigs
T-log file: 36gigs
As I further dive into this server, the transaction logs have never been truncated. I was trying to setup a optimal solution for this server.
Thank you for your help.
Jason
-
It is advisable to run the backup to disk and then backup the backup file to the tape.
This way incase you need the backup it is ready on the disk to restore otherwise it will tape more time to restore from tape backup.
Run the tlog backup every 30/60 minutes depends on your company policy...how much data company can efford to loose...
Disk backup always faster than tape...
MohammedU
Microsoft SQL Server MVP -
Thanks. Very interesting.
let me ask this: Since the transaction logs have never been truncated, which course of actions hould I perform?
Should I backup the logs then truncate them?
After that, just backup the logs?
How often should t-logs be truncated?
-
Jason,
Since your database is using Full recovery model and the tlog is 36GB, I'd say you either need to start doing log backups as well as full backups or you need to switch the recovery model to Simple. Again, which option you choose depends on your company's tolerance for downtime and data loss.
If you choose to start backing up the logs, truncate the log after a full backup, then schedule a log backup job. The inactive portion of the tlog will automatically be truncated after it's backed up, so you don't have to manually truncate it after the initial time.
So you don't have a lot of data changes during a workday? I'd still schedule log backups every hour or two. They won't be very big. Like Mohammed, I prefer to keep a week's worth of backups on disk for fast recovery.
Greg
Greg
-
Thanks Greg. I really apprecaite.
Would you recommend that I continue to use the Veritas agent for backing up the database and transaction logs? I came across a couple of interesting threads where people recommended NOT using Veritas, but instead use the builtin backup, back it up to a disk, then push it to tape.
Secondly, I think right now, from what I gather from my manager, we will want to backup t-logs every 6 hours. They find that is acceptable right now.
With that, what would be a recommended setup to go about my backups?
Lastly, after I backup the logs (a full backup), I should truncate it correct? After I truncate it and schedule log backups, they will automatically be truncated after that?
Thanks very much everyone. I appreciate it.
-Jason
-
Jason,
I don't have any experience with Veritas, so I don't feel qualified to recommend whether you should use it or not. As I said in my previous post, keeping backups on disk generally reduces the time it takes to restore a backup. In my shop, we use native SQL Server backups to disk and then copy to tape at night.
Regardless of whether you backup to disk or tape, you would schedule your log backup to occur every six hours. Let's say your full backup runs at 8:00 PM. Your log backup would run at 2:00 AM, 8:00 AM, and 2:00 PM. Remember that you must have an unbroken sequence of log backups after a full backup to be able to restore to a point in time. See "Transaction Log Backups" in BooksOnLine.
You don't actually have to truncate the log before beginning the log backups, but 36 GB would make for a hell of a big backup file resulting from the the first log backup. So, yeah, I'd say truncate it manually after you've got the log backups scheduled.
Greg
Greg
-
An earlier post said to truncate the transaction log after running a full backup, I'd run the full backup immediately after truncating the transaction log. This will then allow the the full backup to be the start of full backup/transaction log backup train.
-
Read this article...
http://www.devx.com/gethelpon/10MinuteSolution/16507
MohammedU
Microsoft SQL Server MVP -
Thanks everyone. I appreciate the input. I can't thank you enough.
Just a quick question: When you truncate a transaction log, does it actually shrink or make the actually .ldf file smaller? Right now, im looking at a 37gig transaction file.
I wasn't sure and just wanted to ask.
THanks,
Jason
-
If Auto-Shrink is on, it may. It is better if auto-shrink is not on, and you can schedule a process to shrink the file during a time of less database activity.
-
Thanks Greg. I do appreciate it.
Just to follow up and make sure I fully understand a few things:
Setting up the DB to be fully backed up every 6 hours is pretty straightforward. That is the direction I am going to head.
In regards to "an unbroken sequence of log backups after a full backup to be able to restore" this is something I was thinking about. What does that mean exactly? I guess, what I am trying to figure out is how long I need to hold onto my 'tapes' in case I need to restore.
I am not sure, but is this correct: I ran a full backup at 2pm, t-log are backed up hourly. I have a "problem" at 6pm. In order to properly restore, I would need the fullbackup from 2PM and all the t-logs backed up after that?
A simple question here: You mentioned: "See "Transaction Log Backups" in BooksOnLine." That on this website? Couldn't find it.
In regards to truncating, after I run a FULL backup of the DB and a backup of the T-logs, manually truncate it then? My backups will continue as scheduled (6 hours for the DB and 1 hour for T-logs).
Thanks guys. I can't thank everyone for helping me.
Cheers,
Jason
-
I am not sure, but is this correct: I ran a full backup at 2pm, t-log are backed up hourly. I have a "problem" at 6pm. In order to properly restore, I would need the fullbackup from 2PM and all the t-logs backed up after that?
Yes.
A simple question here: You mentioned: "See "Transaction Log Backups" in BooksOnLine." That on this website? Couldn't find it.
It is not a website... BooksOnline installed when you install SQL server/tools on your machine. You can find them at Start/programs/Microsoft SQL Server/Books Online...
In regards to truncating, after I run a FULL backup of the DB and a backup of the T-logs, manually truncate it then? My backups will continue as scheduled (6 hours for the DB and 1 hour for T-logs).
You don't need to truncate the log manually, SQL will truncate it... if truncate the log manually you can't restore tlog backup after that point incase you need to restore.
MohammedU
Microsoft SQL Server MVP -
Perfect. That helps me out a lot. Thank you very much.
I wanted to manually run some backups this morning and push them to a tape.
I can do this in EM.
Questions:
On the "Options" tab, there are some check boxes:
"Verify Backup upon completion"
"Remove inactive entires from transaction log"
"Check Media set name and backup set expiration"
Do I need to worry about any of these? By default, there are check boxes in the 2nd and 3rd options. That ok?
When I am doing a transactional log backup, do I do it the same way as I do with the db backup?
Thanks,
Jason
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply