MS SQL Server - looking for script to backup Transaction log and delete any thing older than 24 hours

Author

looking for script to backup Transaction log and delete any thing older than 24 hours

Omran Abdurrahman

2006-10-31, 12:15 am

looking for script to backup Transaction log and delete any thing older than
24 hours

Arnie Rowland

2006-10-31, 12:15 am

Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:

Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).

If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.

Posted to .tools

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Omran Abdurrahman" <omranab@comcast.net> wrote in message
news:ux9eBD9%23GHA.2408@TK2MSFTNGP05.phx.gbl...
> looking for script to backup Transaction log and delete any thing older
> than 24 hours
>
>

Terry

2006-10-31, 12:15 am

Omran,
If you are using SQL 2005, look at the new XP_Delete_File stored
procedure. Otherwise, Tracy is on the right track. You can store your
backups in a table and run cmd queries with xp_cmdshell to delete files
older than your threshold. This does not need to be a persisted table.
You can also use the dir command to populate a temp table, and delete
based off that.

Delete bak files over 46 hours old
Declare @deletedate nvarchar(255). @Path nvarchar(200)
set @Path = '\\fileserver\db_bac
kup'
set @deletedate = cast(dateadd(hh,-46,getdate()) as varchar(255))
EXECUTE master.dbo.xp_delete_file 0,@Path,N'bak',@dele
tedate

Personally I would disregard the comments from Arnie Rowland. It is too
bad he does not offer constructive comments.

Terry

Tracy McKibben wrote:
> Omran Abdurrahman wrote:
>
> Start here:
> http://realsqlguy.com/serendipity/a...-Send-In-Backup!.html
>
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Sponsored Links