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 |
|
tosberg34
Starting Member
2 Posts |
 Posted - 2006-11-30 : 19:07:28
|
| So I have this discussion at work with a Sys Admin about backing up all of our SQL Servers. We currently use Backup Exec 10d and he just backs up the database directory (on each server) as opposed to using the Backup Exec SQL Server Agent for the backups. I keep telling him to use the agent but, again, he insists that there is no difference the way he does or by using the agent. So here is my question: Why use the agent as opposed to just backing up the .mdf and .ldf files (in the directory they reside in)? What are the pros and cons of both? Does it really not matter like he says? I guess I need to know to solve this argument. Thanks!Edward T Osberg |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-11-30 : 19:10:39
|
| One reason is that the files are constantly in use, so you can't verify the backup unless you shut down the service first.And the backup files should be smaller. |
 |
|
|
tosberg34
Starting Member
2 Posts |
Posted - 2006-11-30 : 19:14:28
|
| So the agent shuts the service down as opposed to you having to do it manually if you're just doing a flat file backup? What happens if the files are in use and you don't use the agent? If you use the agent can you still work in the database while it is being backed up?Edward T Osberg |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-11-30 : 19:34:27
|
| I don't know if the agent shuts the service down (I doubt it). I think it just issues a backup database command then grabs the resultant file and saves it to tape. It might take the DB offline or set it to single user during this process that should lock out other users. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-01 : 00:15:49
|
| Plenty of posts on here from people who are using tape software to back up direct from SQL Server to tape (using the tape software's "SQL Agent") and have run into trouble.The general consensus seems to be to use SQL Server's tool to back up to disk, and then backup those disk files to tape.Backuping up SQL Server's data directory (i.e. the MDF and LDF files) will not work if SQL Server Service is running at the time. And possibly not even if SQL Server Service is shut down - particularly if you need to restore to a different machine, rather than back to the original machine.Plus you are backing up the whole of the MDF and LDF files to tape - and they routinely contain a large amount of "working space" which is not included in the Backup files that SQL Server makes.But then your Sys Admin will have done a test restore from the tape backups and proved that they work OK, and thus you have nothing to worry about <thud!>Kristen |
|
|
|
|
|
|
Backing Up SQL Server