| **
Hi Dave, I’ll add my bit to this too. As LJ
rightly said, investigate your maintenance plans. I would recommend that you
look in depth at this. As you know, having a sound DR plan is invaluable. The maintenance
plans have various options that can be selected to help fit in with your
constraint. You are able to do a full sys backup every night but delete the
previous nights one, simply by selecting remove files older than XXX hours (XXX
being less than 24 hours). Therefore your back files are managed by sql. As for
the transaction log files, we don’t back these up as we have our db set
up as simple recovery model. Having the recovery model as full (which is the
default) will continue to grow the log file to enormous proportions (this will
clearly add to your space problem) and if it fills the disk you will no longer
be able to insert or modify on the db. It believe that performance is also degraded
with such a large file. The pro here is that if you have a failure, you can
take the last backup and sequentially restore the log files to get the db to
the same state just before the failure, depending on when the last transaction
log file was created. There are how ever commands that can be used to truncate
and shrink the log file bringing it back to a manageable size. Check the books
on line or help on enterprise manager for recovery models. This will help you
decide which one will work for you. Also, you can have your maintenance jobs shrink
the data files which I think would be similar to doing a defrag and you specify
how much free space should be provided thereafter. What can also do, which we do for our daily
and monthly reporting, is to copy the backup file after it is created to
another server and do a restore. In this way we test our backups and also large
report requests are done outside of the production box. We use a simple command
line statement in the sql job which first deletes the files in the specified
directory on the other machine and then does the copy. This is all done outside
of normal business hours so network users not affected. We also do a copy of
the necessary via Veritas Net backup onto tape and this goes offsite to a
proper storage facility. Hope that some of this helps. I am not a
DBA but I have learnt a bit through trail and error. Regards, Basil Webster Remedy Developer Siemens
Business Services (Pty) Ltd ( Tel:
+27 11 652 7523 From:
Action Request System discussion list(ARSList) [mailto: ** That is incredibly true; I heard a few days ago that a test restore had
been tried on our big ERP application (first time ever), only to find that it
didn't work (the backup was performed on data that was being updated). I don't think we've ever had to do a restore of the database, not even
sure there is a DR plan in place either. Thanks, Dave On 11/05/06, L. J.
Head <[EMAIL PROTECTED]>
wrote: ** The only advise I'll give you regarding
backups is what was given to me..."A backup is only as good as the last
time it was restored"...and with both files being in existence...I would
be willing to bet that if you delete it today...it will be re-created within a
week. I think your best option is to change your Maintenance schedule in
SQL Enterprise Manager to either not keep the history that it currently
keeps...or to move the backup location to another place From: Action
Request System discussion list(ARSList) [mailto:[email protected] ] On Behalf Of Dave Barber Sent: Thursday, May
11, 2006 11:53 AM ** The only server I normally have direct access to is the test system,
and to be honenst, I've rarely looked into SQL server in much depth, so I
wasn't too sure how safe it was to delete those files. Thanks for the info - with there being 2 .bak files, being about 10 or
11 gig each, that means that both are full system backups, I'll delete the
older one. Regards Dave On 11/05/06, L. J.
Head <[EMAIL PROTECTED]>
wrote: ** Well...if you lookup your backup schedule
you will likely find that you are doing a full backup periodically (the .bak
files) and doing just transaction log backups (the .trn files) more
frequently. You can easily delete the .trn files that are older than the
most recent .bak file...as well as any .bak file other than the most
recent. All of this is of course as long as you don't need more than one
backup. One might argue that you however shouldn't be storing your backup
files on the same hard drive as the DB anyway for disaster recovery purposes. From:
Action Request System discussion list(ARSList) [mailto:[email protected] ] On Behalf Of Dave Barber ** All, Our (somewhat old) production server is running a little short on
storage. On the data drive, in the SQL server folder, there are a bunch
of BAK and TRN files. The bak files are a week+ old, the two largest are 10+ gig. Is it
safe to delete these? Thanks,
__20060125_______________________This posting was submitted with HTML
in it___ __20060125_______________________This posting was submitted with HTML
in it___
__20060125_______________________This posting was submitted with HTML in it___
|
- Re: SQL server/files question Webster, Basil
- Re: SQL server/files question Dave Barber
