January 5, 2010 at 3:36 am
Hi I am looking for some advice if possible. I have inherited some Databases to support from previous teams.
I have a database that is approx 600GB in size.
We have limited backup space but just enough to make a full backup of this database to a network share. The network performance is poor and the backup will proabably take some time to complete.
New data is imported into the database at regular intervals everyday and the backup will take more than 24hrs to complete (last one took around 3days).
The database will also have to be queried during backup process.
What is the best backup method to backup all data and the transactions occuring during the backup so that nothing is missed at all?
Recovery Model is currently set to SIMPLE with Compatibility set to SQL 2000 (80)
Files consist of one Primary File Size 615215MB and a Log File.
any help is greatly apprecaited.
January 5, 2010 at 4:04 am
Dies existing data gets modified?if no,you may consider moving static data to seperate file groups and take backup of those once every 1 or 2 weeks. Can you run report to see what is the database growth and log growth are set to,also can you fund out how much of 600gb is actually being used.
There few things that sound fundamentally wrong,but I can't comment on those since I don't know a nature of the business.
January 5, 2010 at 4:21 am
The data that is entered is new data however all old data is still stored in the database as a seperate application queries the historic data. (I have argued about getting this changed but to no avail).
Tried to run a report for Disk usage etc off but it times out and just sits doing nothing.
many thanks for you help!
January 5, 2010 at 10:13 am
Have you tried a 3rd party backup tool ? They compress the backup file about 80% and run faster than native backups. You can get a free triel to try it out.
January 5, 2010 at 7:53 pm
Investigate using a third-party backup utility like redgates SQL backup tool, Quest's SQL Litespeed, Idera's backup utility or Hyperbac (compression only).
FWIW - I was managing a system at more than 500GB that compressed to less than 80GB and backed up in less than an hour across the network. However, we had a very fast and reliable backup network in place to support that process.
If you have the storage locally available for the compressed version of the backup, you could then use Robocopy to copy the file to your network storage instead of backing up across the network. That would speed up your backups and make them much more reliable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2010 at 1:38 am
Thanks for the replys.
I am fairly new to SQL have always done server, hardware and network support .
Should I back up all transaction logs aswell with or without truncate?
January 6, 2010 at 2:49 am
pmathewson (1/6/2010)
I am fairly new to SQL have always done server, hardware and network support .
No senior DBA there?
Should I back up all transaction logs aswell with or without truncate?
Please read through this - Managing Transaction Logs[/url]
How critical is the data? How much data loss is acceptable in the case of a disaster? If a drive fails, is restoring to the previous night's backup and losing a day's data acceptable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2010 at 3:00 am
A Senior DBA would be too easy lol
Instead I have to learn as much about SQL asap!
Thanks for the Link.
The data is pretty critical although can be retrieved from the original source incase of a total disaster.
January 6, 2010 at 3:25 am
Spend 100$ for a 1.5TB USB disk.
Hook that onto your server.
Create the db backup to that usb disk
robocopy the backup file to your network safe zone location.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply