| Author |
Topic |
|
Robwhittaker
Yak Posting Veteran
85 Posts |
 Posted - 2003-01-07 : 06:38:11
|
| Hi All,For our backup stratagy, we use EDM to backup the disks that have the ldf and mdf files saved on them. The SQLserver logs in the enterprise manager shows that the backups have completed, and lists this one database at a time. However, because I'm not using the sqlserver to backup the database, the transaction logs don't get truncated! Is there a way to get my transaction logs to truncate once the EDM has backed up the databases.ThanksRob |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-07 : 07:15:39
|
| Do you close down sql server or dettach the databases before backing up the files?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-01-07 : 07:19:19
|
| create a maintenance job that will truncate the log for you every few hours or so depending on the size of your DB. Ideally you should back it up in the conventional manner at regular intervals in case of a DB failure and you need to restore data. With the log(s) backed up you can restore to an exact point in time. You would be wise to reassess your method methinks.===========Paul |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-01-07 : 07:21:57
|
| Sorry I'm not great in this area, but my understanding is that they are hot backups, and the databases are up and running whilst being backed up. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-07 : 07:38:50
|
| hmmm.Does it lock all the mdf and ldf files so that they remain consistent while the backup is taking place and so that sql server can't write to the files in the meantime?Or maybe sql server has something bult in to allow this now?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 01/07/2003 07:40:49 |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-01-07 : 08:38:55
|
| I believe it locks the files, so that they remain constant and then makes any changes that are happening rectrospectively after they are backed up. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-07 : 12:29:53
|
| I would suggest testing a restore on a spare server. If the EDM software has a sql hookin, or whatever you want to call it, then you are probably getting good backups. But if it doesn't, you probably won't ever be able to restore the databases. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-07 : 13:10:12
|
quote:
I believe it locks the files, so that they remain constant and then makes any changes that are happening rectrospectively after they are backed up.
That's what a sql server backup does.EDM would have to know that it is backing up database files (unless it locks the whole disk) to handle them correctly. This would mean looking at the master database for any active sql servers and dealing with the database files differently from other data files.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 04:00:14
|
| Any admin guys got any comments on whether EDM (or sql server now for that matter) allows you to back up the database files on an active database (and restore from them).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-01-08 : 06:19:07
|
| SQL Server 2000 is specifically designed so that you can back up a database without taking it down, (Hot backups) My understanding, but I haven't been doing this long, so please correct me if I've got this wrong, basically it backs up the data at a point in time, and puts a checkpoint in the transaction log, any changes will be written to the transaction log after the checkpoint whilst the MDF file is being backed up. These changes are then added to the database after the backup finishes. Of course you can read from a MDF that is being backup without any problems. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 06:38:13
|
| I guess the server will have to know this by the files being locked.The backup would have to lock all the mdf files or make sure that at least one was locked for the duration otherwise the server might start applying the log.It can't also lock the ldf files otherwise the server couldn't write to them.The backup software doesn't have to lock anything of course - could just copy the data - don't see how sql server could manage that at all.Now when it backs up the ldf's then how does the server keep writing to them.When you do a sql server backup it marks any pages that are changed during the backup and writes them at the end so that you end up with a point in time restore.Be interested to see a link to how sql server allows third party disk file backups.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
truncating transaction logs