Tara's DB Backup Script.... - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tara's DB Backup Script....

Author

Topic

beanz
Starting Member

35 Posts

Posted - 2005-05-03 : 06:00:45

I am trying to get one of Tara's maintenance scripts to run (see http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx)

As part of the backup scripts there is a section that deletes files in the backup folder older than x number of days. This is done by formatting the results of a DIR command and building a delete statement.

That is where I have a problem. The following SQL is from Tara's proc:

FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0

This is deleting all the records in #DeleteOldFiles because ISDATE is returning 0 because the initial DIR command returns dates in American format.

I have tried the following with no success:

SELECT * FROM #DeleteOldFiles
WHERE ISDATE(CONVERT(DATETIME,SUBSTRING(DirInfo, 1, 10),102)) = 0

Can anyone point out where I'm going wrong or suggest how to get it working? If you need any more info just let me know.

Many thanks,
Danny

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 17:52:50

I didn't realize it wouldn't work for non-American dates. Could you provide an example date that isn't working?

Tara

beanz
Starting Member

35 Posts

Posted - 2005-05-04 : 03:29:41

Hi Tara,

I found a cowboy work around using the delete command found at:

http://eBible.org/mpj/software.htm

in conjunction with the @Retention parameter but would definitly like to get it working properly.

Dates the proc doesn't seem to like are:

04/13/05, 04/14/05, 04/15/05, 04/16/05 etc... Basically anything where the UK month part is greater than 12.

Thanks for following this up.
Danny

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 12:50:23

Right after this command:

INSERT INTO #DeleteOldFiles
EXEC master.dbo.xp_cmdshell @cmd

Could you add a SELECT * FROM #DeleteOldFiles and post the results here?

There might be an easy fix: SET DATEFORMAT.

Tara

beanz
Starting Member

35 Posts

Posted - 2005-05-04 : 13:11:16

Here is some of the output from the DIR command.

DirInfo ----------------------
Volume in drive F has no label.
Volume Serial Number is 8C8A-729B
NULL
Directory of F:\SQLBackups\LFRMI
NULL
04/29/2005 08:00a 11,660,800 LFRMI_tlog_200504290800.TRN
04/29/2005 08:20p 94,464,344 LFRMI_20050429201326.BAK.zip
04/30/2005 07:34a 847,311,360 LFRMI_db_200504300730.BAK
05/03/2005 08:40p 94,464,312 LFRMI_20050503203217.BAK.zip
05/04/2005 06:07p <DIR> ..
05/04/2005 06:07p <DIR> .
4 File(s) 1,047,900,816 bytes
2 Dir(s) 26,242,080,768 bytes free
NULL

Many Thanks,
Danny

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 13:14:10

Looks like the dates from the dir command are in US date format, so the problem must be that SQL Server wants the dates as dmy.

I think to fix this, you'll need to put this in the sproc. Put it with the SET NOCOUNT ON.

SET DATEFORMAT mdy

Let me know if that works.

Tara

beanz
Starting Member

35 Posts

Posted - 2005-05-04 : 13:23:37

Hi Tara,

That worked a treat! Does the SET DATEFORMAT command need resetting once the proc is complete??

Thanks for your help!

danny

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 13:25:28

No it doesn't. The SET commands are only valid within the scope of the proc. Once the proc completes, the defaults are now true.

Tara

beanz
Starting Member

35 Posts

Posted - 2005-05-04 : 13:29:45

Cool.

Thanks very much for taking the time to help me out.
Danny