Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now.
What on earth am i not doing?
Thanks in advanced
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
I want to delete all files in a given folder that are older than 3 days from today's date.
I tried using a "Foreach Loop container" with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?
TIA,
Barkingdog
P.S. History of the problem:
I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep! I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.
Friends -
I am looking for a windows script (bat file) to delete backup files which are older than 2 days.
Please provide scripts on this.
Appreciate your support
Cheers :)
Satish
I am using the backup task and backing up a database but want to delete all backup files older than 5 days old. I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time. I am getting illegal character in path. How can I go about this.
I currently have E:MSSQL.1MSSQLBackupdatabasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bak
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
hi,
i want to delete the backup files older than 15 days. how can i do this?
Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running .
Any idea? thanks in adv.
kumar
I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10
Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be.
All the backups and T-log backups are written to one device which is one file.
Thanks
Scott
Hello,
I am experiencing an issue with SQL Server 2000 Maintenance Plan.
DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS).
Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
Thanks in advance,
Hello ,
I am little confused in writing the exact query i.e filling the correct details in the query .
To simplify let me explain....
I am using the query as
delete MYTABLE
where datediff(dd,loaddate,getdate())>5
I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .
Now suppose if i want to delete previous 5 days records from today
( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
The records from 15/08/2002 to 20/08/2002 remain intact .
I am getting some different results .
Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .
Is the logic correct or i am missing someting important ?
Thanks and Regards
Admin001
I have a backup job that has failed.
The database size is 20.6 GB and the Transaction logs are 135MB
The amount of disk space I have left is 3.65MB. Which I know is not going to work.
However on the maintenance plan it is suppose to remove files older than 1 day.
I am wondering if a job works like this:
Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up
Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.
Is this correct??
Also is there a way that I can have step 3,4 done first.
Lystra
Hi ,
I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .
Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .
Any thoughts how to do it through query and schedule it ???
Thanks
When running the following SQL statements, I get the same results.Though I need to count only -30 days. Both statements below alsoconsider the time of the day as well, which is not desiredDELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < GETDATE()- 30DELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())Here is the format of the values in columnMNT_R.TIMESTAMP2005-08-09 06:06:44.5772005-08-09 06:06:46.8102005-08-09 06:06:49.060So, since data are inserted into the MNT_R table every few seconds, mydelete statement will delete different number of rows, according to thetime of the day it runs.Can you please post a SQL query that will not give me this headache?thanx a lot all
View 2 Replies View RelatedI have a table which has XML data, so it takes lot of time to delete it.
Here's the condition:
Archive all data older than 2 months and delete it.
What would be the good approach for this?
I am in the process of moving a database to a new location. My problem is that I do not have the most recent full backup file. I have a previous full backup file and I also have all of the transaction log backups from the time of the old backup to the present time.
My question is, do I need the most recent backup to restore the database, or can I do it with the older backup plus the transaction log backups?
Thanks in advance.
We have some to-disk backups scheduled on our 2000 Enterprise machine - nightly fulls, hourly logs - that go to a network share located on another machine. They were originally stored directly on the same machine as SQL Server, but we changed them to a remote destination within the past few weeks. This works okay, but despite having the maintenance plan set to remove files older than 2 days, old files don't seem to be removed. Understandably, this gets to be a problem when the backup disk becomes filled.
Is there any obvious reason why this option wouldn't work against a network share? I've checked the directory permissions, and the SQL Agent domain account should have no trouble deleting the files.
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
If I run the below command, it clean up the files inside the folders but unable to clean up the folder and sub-folders older than 30 days. xp_ cmd shell ' forfiles -p "D:abcd" -s -m *.* -d -30 -c "cmd /c del /Q @path"'
View 4 Replies View RelatedI have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.
I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.
I scheduled automatic backup process but its only showing backup of the only one .sql file in the backup folder. Other created .sql files are not backed up. Why is it so?
View 5 Replies View RelatedI dump my databases to backup devices each night.
However would like to purge old backups - say older than a week - from the device.
Is it a case that I have to drop and re-create the devices every 7 days or can SQL do this for me?
I have seen the RETAINDAYS claus but it applies to tape backups only?
Thanks
Hi everyone,
I want to do sql db backup.But how can I backup db to split backup files? The reason I want to split the backup file is becasue single file size is too big and I want to write to dvd.
any idea or scripts?
cheers
I need to delete all the *.txt file in a specific folder modified date is older than 2 months,. How can I do this in a stored procedure?
View 3 Replies View RelatedHello All
I have a SQL 2005 server I inherited and am trying to clean up. I am unable to delete some log files. The databases they were associated with do not exist in sysdatabases. However, when I try to delete them I get the message they are in use. There are no other instances attached to this server.
Any help is appreciated.
I need to delete all the *.txt file in a specific folder modified date is older than 2 months,. How can I do this in a stored procedure?
View 5 Replies View RelatedI need to setup an automated process to delete all the files older than a month in a particular directory on a network drive, how is that possible? Any help is greatly appreciated.
thanks.
Sheila.
can I use xp_cmdshell to delete files older than lets say 30 minutes? can anyone show me what the command will look like?
Thanks
Shahab
Hi everybody,
Can anyone help me out in deleting files more than n- hours from a physical folder using xp_cmdshell ....
how can I delete files, which are allready imported into a database. I have a folder with 600,000 files . All Files should be in a database. And I want to delete the files that are found in the table with filename . How can I make the ?? The Filename and physicals Name is the same so i can say table.filename=physical.filename.
View 20 Replies View Related