• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

questions regarding backing up databases

Hi:

A few questions to make sure I am not missing anything in terms of backing up my company's databases. (We only have one instance.)

I have a maintenance plan that performs full backup on all user databases every night and another that performs full backup on all system databases every Sunday. My qeustions are:

1. If there is a disk failure, would I be able to recover the user databases using only last night's backup files? Would I need backup files for system databases to do that? If so, would it be a problem since the system backup is from last Sunday?

2. Our business allows losing one day's data/changes. Do I need to backup transaction logs? To prevent transaction logs from getting too big, can I just set up a maintenance plan that backs up transaction logs for ALL (user and system) databases and save it in one file? Which leads to my 3rd question...

3. The Back Up Database Task wizard gives us choices either (a) to backup databases across one or more files or (b) to create a backup file for every database. What differences would it make? If I choose (a) and save backup for all databases in one file, would I be able to restore only one of those databases? Is there any way I can save backup for each database with a different file and have those files overwritten by new backups? (b) doesn't provide option for overwritting.

Please help. Thanks.
0
sdc248
Asked:
sdc248
  • 4
  • 2
  • 2
1 Solution
 
NightmanCTOCommented:
1. Yes, you can recover from last nights backups. If the system databases have not changed (no new logins, no new databases) then they could also be recovered. As a matter of best practice, backup the system databases daily (they are usually very small, so there should be very little impact here).

2. I would recommend backing up the transaction log. and running in full recovery mode. Assuming that the backup chain is unbroken, backing up the log will allow you to recover right up until the last transaction log backup. If the .ldf is also recoverable, you could get the tail of the log and recover right up until the point of failure! With no data loss.

Backing up the log will also zero the used disk space in the log for transactions that have been committed, meaning that you will almost always have free space in the log file. this will prevent the log from growing out of hand.

3. Back up to a single file per database. The maintenance plan wizard should have an option for this, including placing each in a seperate folder, AND automate the deletion of older backup files.

What version of SQL Server are you running?
0
 
Scott PletcherSenior DBACommented:
>> As a matter of best practice, backup the system databases daily <<

Yes, **at least** daily.  Since some changes in the db get reflected in master, we backup our system dbs several times a day on most prod servers.


>> Our business allows losing one day's data/changes <<

You should try to make that a worst-case scenario, not your standard way of recovering.


Make *sure* the backup is on a different drive|RAID than the data files.  Otherwise a drive|RAID failure wipes out *everything*.  If you only have two drives|RAIDs available, put data and backups on separate ones, with the active logs on the backup drive (if at all possible).
0
 
sdc248Author Commented:
Thanks for the replys.

1. OK, I am going to backup system databases daily. But just to make sure I fully understand what's going on, if I have updated backups for users databases but not system databases, I could still recover user databases up to the point of time they are backedup? How about system databases? In this case, do I just use the system databases that would be provided by the new instance I am going to newly install or I could use the "Sunday" backup that way at least I have up to Sunday's logins etc?
2. noted.
3. How do I automate the deletion of the older backup files?

We run SQL Server 2005.

I have tried to have the backup files stored directly on the shared folder of another server we have but failed. There's no option on the back up wizard to do that. Would appreciate it if you could let me know how to do it.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
NightmanCTOCommented:
1. Yes, you can recover the user databases. However, logins, jobs, etc will be missing. New SQL logins, while they may have permissions on the user databse, would still need to be recreated at a system leve. I strongly recommend that you simply back everything up. ALSO, test your recover strategy on another server to make sure that you are familiar with it and that it works. An untested backup strategy is only worht the paper that it is printed on.

3. Create a maintenance Cleanup Task  in the maintenance plan - most of the options are self explanatory.

Regarding the shared files, I'm not exactly sure what you mean. Can you elaborate as to what you are doing, and what the problem is?
0
 
sdc248Author Commented:
Thank you Nightman. I've set up all the backup/cleanup tasks to run.

Regarding location of the backup files as brought up by ScottPletcher:
My databases files are on D drive of the server. If I am going to save the backups to a different drive, the only choice is C drive but it'is too small. So I tried to have maintenance plan save the backups to another server, which has a drive that is shared on the network and is mapped as "S" drive on the local server. The Back Up Database Task doesn't let me choose a mapped drive (I only see D: and C:). I remembered I tried T-SQL but failed (because of access denied from the file SQL?). Your tips/suggestions would be appreciated.
0
 
sdc248Author Commented:
sorry, it's
.....(because of access denied from the destination server)
0
 
Scott PletcherSenior DBACommented:
Make the original .BAK to the local D: drive, then copy it to another drive.

Personally I would be *very* nervous about having the data file and backup files exclusively on the same drive, w/o another copy somewhere else.
0
 
sdc248Author Commented:
Thanks a lot.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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