questions regarding backing up databases

Posted on 2006-11-30
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
  • 4
  • 2
  • 2
LVL 29

Accepted Solution

Nightman earned 125 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

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.
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?
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now