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 |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2011-07-28 : 10:13:35
|
| We are looking to finally upgrade from SQL Server 2000 to 2008 R2. As part of this we have setup a small database on SQL Server 2008 R2 Express Edition for proof of concept. We would be looking to move to SQL Server 2008 R2 Standard (or higher, I'm not sure at the moment) edition at some point in the not too far flung future.This has raised a question over the backups. Currently on SQL Server 2000 I take regular SQL backups and have ready access to them. We also backup the box using the backup system supplied by a very large IT company, I am told by our server people who do this that it takes correct backups of a SQL Server, though thankfully I have never had to test that myself. Part of the reason for me taking backups is that the backup file will itself get backed up centrally. This system also allows me to do test restores etc. whenever I feel like. I'm now being told that there will be no need to take my own backups on the new system as the backup system we have will cover us. This makes me uneasy, so I was wondering what experiences other people have with backup systems that claim to backup your SQL Server correctly, I know in the past that there was an issue with the files that are held open but I would hope that by now that has been overcome. Am I wrong?Thanks steve-----------What color do you want that database? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-28 : 10:19:11
|
quote:
I am told by our server people who do this that it takes correct backups of a SQL Server
Don't believe them. Put together a demo where you accidentally drop a table and need to restore it. Demonstrate that recovery on a separate machine, and require them to demonstrate the same capability with their software. Even if they can restore a perfect copy of the database, make your own SQL backups anyway.Any backup system that just copies files cannot properly restore a SQL Server database or log file.And if they're using DPM, just laugh in their face and say No thanks, I'll make my own. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-28 : 16:02:38
|
| I would tend to agree with robvolk...however, it really depends on the software they have and whether or not they really understand how to backup SQL Server. Almost every time I have run across this, the administrators did not understand the requirement to backup the transaction log frequently (e.g. every 15 minutes) and would state that it was not necessary with their software.Make sure you test likely scenarios for recovery and the ability to restore as a different database, on another server, to a point in time (apply transaction log backups), etc...Also, see what happens when you try to restore from a couple days back and apply the transaction logs forward to the current point in time. Is that even possible with their software?A few other things I have seen are the requirement to have a separate LUN for each 'set' of user databases. This is when using SAN utilities to snapshot the databases and then backup the snapshot to tape. SAN snapshots are performed on the LUN level - not the database level so all databases on that LUN are backed up at the same time. This could be a good thing - however, if you need to restore just one database out of that set it could be a lot more work.Jeff |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2011-08-04 : 05:50:36
|
| Thanks for this guys, just what I wanted.Steve-----------What color do you want that database? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-04 : 06:39:40
|
quote:
Originally posted by elwoos
We also backup the box using the backup system supplied by a very large IT company, I am told by our server people who do this that it takes correct backups of a SQL Server, though thankfully I have never had to test that myself.
You must test it. You don't want to be finding out that the restores are useless after the system has crashed.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
backup system question