questions regarding backing up databases

Posted on 2006-11-30
Medium Priority
Last Modified: 2008-02-01

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.
Question by:sdc248
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
  • 4
  • 2
  • 2
LVL 29

Accepted Solution

Nightman earned 500 total points
ID: 18049468
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?
LVL 69

Expert Comment

by:Scott Pletcher
ID: 18049773
>> 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).

Author Comment

ID: 18050019
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 29

Expert Comment

ID: 18052099
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?

Author Comment

ID: 18055218
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.

Author Comment

ID: 18055246
sorry, it's
.....(because of access denied from the destination server)
LVL 69

Expert Comment

by:Scott Pletcher
ID: 18055433
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.

Author Comment

ID: 18055598
Thanks a lot.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 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