Solved

questions regarding backing up databases

Posted on 2006-11-30
8
241 Views
Last Modified: 2008-02-01
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
Comment
Question by:sdc248
  • 4
  • 2
  • 2
8 Comments
 
LVL 29

Accepted Solution

by:
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?
0
 
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).
0
 

Author Comment

by:sdc248
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:Nightman
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?
0
 

Author Comment

by:sdc248
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.
0
 

Author Comment

by:sdc248
ID: 18055246
sorry, it's
.....(because of access denied from the destination server)
0
 
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.
0
 

Author Comment

by:sdc248
ID: 18055598
Thanks a lot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 48
T-SQL: "HAVING CASE" Clause 1 25
Whats wrong in this query - Select * from tableA,tableA 11 31
Syntax for query to update table 2 12
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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