Post reply

Need Help

  • October 7, 2013 at 9:12 am

    #280597

    Hi ,

    I am trying to delete files in a folder except the last created file in the folder.

    can you please help me with the logic.

    The files in the folder are sql server audit files with the extension .sqlaudit

    I cannot choose the files based on date because if the auditing data is not present on todays date . the old files can be deleted.

    I am looking for a logic which should look at the files in the folder and delete all the files in that folder except the last created file in that folder.

    I am using SQL Server 2008 R2 Enterprise edition RTM 64 bit on windows server 2008 R2 Enterprise edition sp1 64 bit

    Thank You,

  • October 7, 2013 at 9:23 am

    #1656403

    I am not clear with what the criteria you are using for deleting. Maybe an example could help.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • October 7, 2013 at 9:45 am

    #1656413

    Hi,

    Example: Audit_DBA_991AEE46-71F7-470D-B63C-2EC208FD3303_0_130126053692760000.sqlaudit

    Auditing file looks like this. when i go to folder i could see date and time the file was modified.

    My auditing files doesn't contain the datetime. i have to check in the folder (M:\Auditing\DBA ) containing the files for the date and time.

    I have to delete the files in the folder name M:\Auditing\DBA to save the disk space.

    Thank You,

  • October 7, 2013 at 9:59 am

    #1656420

    I might look through our scripts for something similar: http://www.sqlservercentral.com/search/?q=delete+old+backups&t=s&sort=relevance

    Powershell is how I'd probably do this now, though VBScript works well. You can easily manipulate files and delete all but the most recent.

  • October 7, 2013 at 10:09 am

    #1656425

    Does your instance of SQL Server have xp_cmdshell enabled? If so you can capture the result of the DOS command "DIR" into a table variable containing the date and time. Here is a snippet of code that I use for looking for database backups using this method:

    set @FullyQualifiedPath = @BackupPath + '\' + @Database_Name

    set @DirectoryCmd = 'dir /b /o:-d ' + @FullyQualifiedPath + '\*.bak'

    delete from @DatabaseBackups

    insert into @DatabaseBackups (BackupFileName)

    exec xp_cmdshell @DirectoryCmd

    delete from @DatabaseBackups

    where BackupFileName is null or

    charindex(@TodayDate, BackupFileName) = 0

    Here is the table definition for @DatabaseBackups:

    declare @DatabaseBackups table (database_id int

    ,BackupFileName varchar(max))

    This gives you an idea what you can do. You can capture the date/time of the files then process what ever you want.

    Hope this helps.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • October 7, 2013 at 11:44 am

    #1656458

    Hi Steve,

    I don't know Powershell.

    Thank You

  • October 16, 2013 at 5:16 pm

    #1659097

    sql2k8 (10/7/2013)

    Hi Steve,

    I don't know Powershell.

    Thank You

    This is a good chance to learn. It's not terribly difficult and you have a specific project here to work on. Do a little research and googling.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply