Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2005-04-27 : 12:36:54
|
| Hello all. Could some of you give me advice on what you would do in this situation?At work we are currently doing full backups each night. The backups are written to a backup storage server. That server then allows other servers to pull copies of the backups down so they can mount over their databases. These other servers have databases for testing, development, and demo purposes. All they do is mount that backup file over the existing database.We are thinking about going to differential backups. How does all this work with differentials? Will our backup server still be able to mount the differential backup onto the testing and development databases even though those databases haven't had a full backup that is in synch with the production database? Also, what method do you recommend for storing and sorting these backups? Do you have separate folders for each day's backup, or do you name them in such a way that a person could easily read the file name and figure out which day that backup applied to?Thanks!Aj |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-27 : 13:21:13
|
| If you create a maint plan it will add the backup Date/time into the name for you.I keep two backup sets one on one server that is the current that gets over-written each time. On the other server I keep a weeks worth of backups. In addition to this I hard copy to dvd once a week.This way I have a base copy and then redunincys.JimUsers <> Logic |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-27 : 13:31:05
|
| (MS's standard Maint Plan<spit!> won't do you a Differential backup, but)Your "pull copies of the backups down so they can mount over their databases" process would need to be prepared to pull the appropriate Differential backup, and its immediately preceeding Full backup, and restore them as a pair (Full first, then Diff)If someone takes a "quickie full backup" [e.g. prior to doing some operation which they would want to be able to recovery easily from, and outside the normal backup regime] then this MUST be centrally available, otherwise subsequent Differential Backups are useless.But if you are looking to reduce the disk-space for online-retained backups then this seems like a good plan.(We get around the "Must be retained" hiccup by having an SProc that does a backup - give it a parameter for 1=Full, 2=Diff and 3=Transaction and then people needing to do an out-of-normal-sequence backup can launch one within the scope of a centrally managed, rotated, backup management stratagy)Kristen |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-04-28 : 11:17:51
|
| Thanks Jim and Kristen for the advice. We do have some maintenance plans that execute to push the backups to their proper folder. Am I understanding you correctly Kristen that if a user went to Enterprise Manager and did a backup, that will register itself as the last full backup done. Whereas, if a stored procedure executes a backup, that won't count as the last full backup? Aj |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-04-28 : 11:38:56
|
quote:
We are thinking about going to differential backups. How does all this work with differentials? Will our backup server still be able to mount the differential backup onto the testing and development databases even though those databases haven't had a full backup that is in synch with the production database?
No, it wouldn't. You need the full backup too.I'm not sure if the differential backups would improve your situation. What are you wanting to get out of them? If your intention is only to restore the differential, then you cannot do this, you must first restore your most recent full backup. I don't believe that there would be any difference in having a user take a backup or a stored procedure.The benefit of the differentials is to allow you to take backups more frequently (because they will be smaller, assuming that only a percentage of your data set changes), it doesn't replace your other system.-------Moo. :) |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-04-28 : 13:58:42
|
| I think that disk space is the main concern. Our company is doing a major server consolidation/overhaul and our primary production database server is going to be hosting a lot more databases. For simplicities sake, it would be nice to have one backup server for the production server to push files to. We figured that a differential backup system would help save overall disk space. Right now we push full backups to the backup server. The backup server keeps the last three days worth of backups. Weekly/monthly/yearly backups are pushed to tapes and stored offsite.Based on what I am hearing, it sounds like the differential backups would be a pain to maintain. Disk space is cheap, so maybe we can convince them to slap another 250 gigs into our backup server.Aj |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-30 : 10:13:54
|
| Whatever method is used to make a backup its name etc. will be recorded in the MSDB database - and Enterprise Manager will offer it as the most recent file to restore from.What I typically find is that people make one-off backups for a variety of reasons (copy to DEV, "just in case" to restore from before a risky process, etc.). In these instances the user is also liable to move the file to another location, or go "Phew! That worked, I'd better remove that temporary file ..." and this will prevent subsequent Differential backups from being restored.If you ALSO keep all transaction files since the previous full backup you will be able to restore forwards using those."it sounds like the differential backups would be a pain to maintain"I wouldn't agree with that. We use differential backups extensively to allow us to keep decent backup histories online (on the assumption that it WILL take a while to find the right tape, restore it, and then restore the DB from that backup file).Maybe the risk that I point out above is not likely in your organisation. We've told our folk to use an SProc to make a backup (which puts it in a central folder, and in the "clear down" history list, etc.) to prevent any possibility of an ensuing problem.Kristen |
|
|
|
|
|
|
|
|
Best practices