Delete 2 Days Old Files - Xp_cmdshell
Apr 18, 2008Folks:
I want to delete files on windows from a directory which are 2 days old. I understand we can do that using xp_cmdshell. Anybody with a script would really help me.
Thanks !
ADVERTISEMENT
Xp_cmdshell To Delete Files
Jul 26, 2006can 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
Delete Files Older Than 3 Days Using SSIS Tasks
Aug 11, 2006I 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.
How To Copy Files Using Xp_cmdshell
Nov 18, 2007Howdy, hope someone can help me out with this.I want to run a job each night that copies files from one server to another.I cant even get a simple copy one file from one directory to another,statement to work.When I try :@cmd 'copy c: empfile1.txt c:ackupsfile1.txt'master.dbo.xp_cmdshell @cmdI get the msg: 'c' is not recognised as an internal command, program orbatch file, Null.What I ideally want to do is pass xp_cmdshell variables for the 2 file'spath+names. I have tried numerous variations of single and double quoteswithout success, which is why I decided to work up form the simple 'copyc: empfile1.txt c:ackupsfile1.txt', but can't even get that to work.Any advice much appreciated.
View 3 Replies View RelatedXp_cmdshell Not Unzipping Files
Feb 23, 2008Hi,
I am trying ti unzip a backup file throguh xp_cmdshell. I am getting this error. It works when I tried manually unzipped.
NULL
C:WINDOWSsystem32>"C:Program Files (x86)WINZIPwzunzip.exe" -ybc -s0Ff$1tE
WinZip(R) Command Line Support Add-On Version 2.0 (Build 7041)
Copyright (c) WinZip International LLC 1991-2005 - All Rights Reserved
NULL
ERROR: missing name of Zip file
Program is terminating!
NULL
My batchfile: "C:Program Files (x86)WINZIPwzunzip.exe" -ybc -s0Ff$1tE E:OffsiteBackupsGLPArchival20080201.zip E:RestoreDatabaseVEENATest_unzipbackup
Mt stored procedure :
CREATE PROCEDURE USP_UNZIP
@ZIPFILE VARCHAR(2000),
@BAKPATH VARCHAR(1000)
AS
DECLARE @SQLSTATEMENT VARCHAR(2000)
SET @SQLSTATEMENT =''
SET @SQLSTATEMENT = 'C:UNZIP.BAT "'+@ZIPFILE+'" "'+@BAKPATH+'"'
PRINT 'SQL STATEMENT'
PRINT '-------------'
PRINT @SQLSTATEMENT
PRINT 'MESSAGE'
PRINT '-------'
EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT
Thanks,
-G
SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files
Sep 11, 2015I 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
SQL 2012 :: Delete DB After X Days
Jan 23, 2015I am creating a Scheduled JOb that will delete backups older than 3 days old, however I seem to be missing something in my script.
DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -3, GetDate())
EXECUTE master.dbo.xp_delete_file 0,N'E:BackUps',N'bak',N' + @DeleteDate + ''
Xp_cmdshell Can't Copy Files From Different Domain
Jun 19, 2000I was trying to copy backup files from production domain to test domain nightly using xp_cmdshell. I
View 3 Replies View RelatedDelete Records Older Than 5 Days .
Aug 20, 2002Hello ,
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
SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?
Feb 24, 2015I 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?
Delete Records In A Table Older Than 90 Days . Help
Aug 5, 2002Hi ,
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
How To Delete Data Older Than X Days, Without Considering Time
Nov 11, 2005When 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 RelatedBack-up DB But Keep The Files For 3 Days?
Jun 17, 2006Hi
I
have created a job to backup all our database. It works fine and
creates .bak files in default folder. I have scheduled the task to run
every 4 hours.
My question is how can i modify or program this
backup plan to keep the backup files for only last 3 days and delete
older backup files?
Mits
T-SQL (SS2K8) :: Winscp To Get Only Current Days Files?
Jan 21, 2015I have to download the files from SFTP server, for which i am using WINSCP and i am able to successfully automate the process to download the files. But it is downloading all the files instead of only current day's files. Ihave searched for WINSCP documentation for time query parameter to pass to the get command. But its not working.
My source file name contains Datefield as "Filenameexample_150120_N001.txt".
Here 150120 mean Jan 20 2015.
Winscp has no functionality to query the files to parse using datefield. how to look for files which are from today's date.
Currently i am downloading files which contain *.* (meaning all files).
Deleting Backup Files Older Than 2 Days
Jun 23, 2008Friends -
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
Deleting Backup Files Older Than 5 Days Old.
Mar 14, 2007I 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
Creating CSV Files Using BCP && Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@
Jan 24, 2008Hi all,
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
=========================================================================================
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
===================================================================
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
Thanks in advance,
Scott Chang
SQL Server 2008 :: Delete Folders / Sub Folders Older Than N No Of Days
Jul 30, 2015If 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 RelatedDelete Files
Aug 31, 2006I 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 RelatedCan Not Delete Log Files
Jun 9, 2008Hello 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.
Delete Files
Aug 31, 2006I 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 RelatedDelete Files Automatically? Help!!
Jul 31, 2002I 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.
Delete Old Backup Files
Jun 5, 2001hi,
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
Delete Files More Than N Hours
Mar 1, 2004Hi everybody,
Can anyone help me out in deleting files more than n- hours from a physical folder using xp_cmdshell ....
Delete Files From Directory?
Nov 15, 2014how 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 RelatedDelete Old Files (SSIS Or DOS)
Oct 9, 2007I've been tasked with coming up with a process to delete old files out of a certain directory. It's prefered to have a configuration file to go in and be able to change the number of days to retain files (7 for delete files older than 7 days for example). I've been told I can do this with SSIS. Can anybody point me in the right direction to do this in SSIS or even in DOS? I don't know ActiveX scripting if that's what's needed in SSIS. I prefer doing something like this in DOS but not sure it's possible to automate. Any help is appreciated.
Van
Delete Remote Files
Mar 3, 2006I wish to delete remote files on an FTP server after I download them. I want to download files daily and delete them daily after I finish downloading. I am trying to use the FTP task to do this but I get an error when it tries to delete files from specified location. It says:
"Unable to delete files using FTP Connection Manager" The FTP connection manager contains the FTP connection string and password. I have unchecked the passive mode box. Can someone help me with this error,
Thanks
Annie
I Have Two Transaction Log Files In My Database, I Want To Delete One, How?
Sep 14, 2006I already did the following but still it wont delete the log file because it is not empty- DBCC SHRINKFILE('logfilename',EMPTYFILE) - DBCC SHRINKFILE('logfilename',TRUNCATEONLY)- ALTER DATABASE databasename REMOVE FILE logfilename
View 4 Replies View RelatedHow To Delete Old Files Stored In A Directory?
Jan 7, 2003Hello,
How to delete old files stored in a directory using dos script?.
I appreciate your feedback.
Thanks,
Ravi
How To Combine Log Files && Delete Secondary
Oct 21, 2003I have a database with 2 log files VE_log and VE_log2 (the split is left from legacy system with limited disk space). I want to eliminate the 2nd log.
I've tried SHRINKFILE (EMPTYFILE), which of course reduces the file to the default minimum pages, SHRINKFILE (TRUNCATEONLY), SHRINKDATABASE (VE), and in every case the ALTER DATABASE REMOVE FILE command fails cause the log isn't empty.
Ideas???
Thanks,
Al
Delete Backup Files Older Then...
Mar 5, 2007Ok 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
T-SQL To Delete Files From Backup Device
Nov 3, 2006I 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
Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates
Jan 7, 2014I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.