November 15, 2011 at 10:07 pm
A good amount of the time the database is at very low usage. 2 weather station station updates every 30 min. The database can go for days with just running tinny queries and sometimes it gets hammered an hour. Weekly and sometimes bi weekly huge amounts of data gets dumped into the data base.
I'm wondering if I’m missing anything when it comes to backing up my database?
The reason I’m asking is last week primary server blue screened and would not restart. I moved the spare server to the office, updated the database, change it's IP, and everything worked. But this go me thinking on if there’s a better way.
My network is split between my house and office 620' apart with fiber.
In the office...
SQL server (twin 2) 2008 R2 with 6.7 gigs of data and expected grows around 80 megs a year.
In the house...
Storage server is running 8 1.5Tb drives in raid 6 (10.23Tb).
SQL server (twin 1). Used for testing backups and changes.
Our current backup plan is to send everything to the storage server
Transaction Log file every 30 min
Full backup nightly
System Database backup by weekly and when changes are made
Os backup by weekly and when changes are made
Weekly start up test server and test / practice restoring data from a backup
November 16, 2011 at 12:36 am
Our current backup plan is to send everything to the storage server
Transaction Log file every 30 min -- Checked
Full backup nightly -- Checked
System Database backup by weekly and when changes are made -- Checked
Os backup by weekly and when changes are made -- Checked
Weekly start up test server and test / practice restoring data from a backup -- Checked, Very Good
November 16, 2011 at 8:59 am
Would Suggest
Transaction Log Backup - every 15 min or 30 min.
Differential backup -Every night
FUll backup - once in a week .
system backup - once in week or you can do byweekly also .
November 16, 2011 at 9:15 am
Transaction Log Backup - every 15 min or 30 min.
Differential backup -Every night
FUll backup - once in a week .
system backup - once in week or you can do byweekly also .
It really depends on how critical your data is. At our company we can't afford to be down long so we do it this way:
Transaction Log Backup (Log Shipping) - Every 15 min.
Differential backup -Every 6 hrs (12 hrs for non-critical DB's)
Full backup - Daily during off-peak hours
System backup - Daily (with differentials similar to above)
CheckDB - SQL Agent job running every other day during off-peak hours
Lots of other little maintenance tasks - Dynamic Update stats, indexing, etc.
We also have log shipping set up to another server in a different datacenter, in the hopefully very unlikely event of total datacenter loss at our primary facitllity, we can quickly switch to our log shipped server.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 16, 2011 at 9:19 am
The requirement:
6.7 gigs of data and expected grows around 80 megs a year
November 16, 2011 at 9:30 am
I read that DEV, but was responding to your current backup plan and logicinside22's backup plan suggestions.
I'm wondering if I’m missing anything when it comes to backing up my database?
The reason I’m asking is last week primary server blue screened and would not restart. I moved the spare server to the office, updated the database, change it's IP, and everything worked. But this go me thinking on if there’s a better way.
Food-for-thought:
If a SQL server bluescreens right now, and the system databases haven't been backed up in 6 days, and/or when attempting to restore them on the new server you get an error that there's torn pages or some other data-integrity check or inconsistency...you'll be wishing that you had known about it a lot sooner, so you can have caught it and corrected it? The same goes for the other user databases.
This is basically why I added my prior post...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 16, 2011 at 12:44 pm
So look into differential backup to cut down on rebuilding times. My database just tracts cattle from birth to harvest; not the most important but helps to find trends and how to improve the harvest.. Most of the up time is just tracking the 2 weather stations.
My blue screen came from a bad ram chip. The server ran 24/7 for 8 months before it showed it's head.
November 16, 2011 at 1:14 pm
mobgrazer 98125 (11/15/2011)
... sometimes it gets hammered an hour. Weekly and sometimes bi weekly huge amounts of data gets dumped into the data base.
Hi,
What do you consider as Huge amounts of data - can you quantify? gets hammered an hour - again - can you quantify? What is the biggest your transaction log has grown to? that should help to quantify the max churn on your db in a 30 min period
what kind of work does your DB do? mainly inserts or are there lots of DELETES/UPDATES? just trying to understand how busy your database actually is as based on the 80mb a year growth its very dormant -if its mainly inserts then thats around 4kb churn every 30 mins which in my opinion isnt worth tlog backups every 30 mins - personally think that would be overkill
What is your backup retention policy?
November 16, 2011 at 1:20 pm
logicinside22 (11/16/2011)
Would SuggestTransaction Log Backup - every 15 min or 30 min.
Differential backup -Every night
FUll backup - once in a week .
system backup - once in week or you can do byweekly also .
Differential? as posted database is under 7 Gig in size - I think poster has a pretty good backup/recovery strategy.
Having said that, poster has said nothing about business requirements regarding business continuity and tolerance to data loss and blackout time.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 16, 2011 at 1:35 pm
November 16, 2011 at 3:11 pm
What do you consider as Huge amounts of data - can you quantify? About a meg in 90 min
gets hammered an hour - again - can you quantify? 2 users running quires till It slows down
What is the biggest your transaction log has grown to? A bit under a meg
what kind of work does your DB do? Tracts the growth of my cattle from birth to harvest
mainly inserts or are there lots of DELETES/UPDATES? 8% insets 20% updates
What is your backup retention policy? Never made one
the impact data loss would have? About 10 megs of the data a year is needed to show that I raised the cattle according to contract. A lot of the information is used to find what I could of done differently to make a better product and more money.
November 16, 2011 at 11:29 pm
mobgrazer 98125 (11/16/2011)
What do you consider as Huge amounts of data - can you quantify? About a meg in 90 mingets hammered an hour - again - can you quantify? 2 users running quires till It slows down
What is the biggest your transaction log has grown to? A bit under a meg
what kind of work does your DB do? Tracts the growth of my cattle from birth to harvest
mainly inserts or are there lots of DELETES/UPDATES? 8% insets 20% updates
What is your backup retention policy? Never made one
the impact data loss would have? About 10 megs of the data a year is needed to show that I raised the cattle according to contract. A lot of the information is used to find what I could of done differently to make a better product and more money.
In my opinion as long as you have daily FULL backups (ready-to-use, tested) you are in a good shape, if 1 day data loss is acceptable in disaster recovery. You can minimize this data loss, if you are equally fine with Log Files Restore.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply