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

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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

827 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