May 12, 2010 at 9:43 am
the problem is the HFMSYDB without the underscore date on it.
try
delete from
@myTable2
where FileNames not like 'HFMSYSDB_%' or FileNames is null
and then change it to
delete from
@myTable2
where FileNames <>'HFMSYSDB'
you will need to run it 2 different times intitally. If you do backups with the _date on it it will always work.
This throws an error on that subquery just for that reason. I orginially built this to delete audit logs older than a certian date, every audit log has its name_daterange on it i.e. auditlog_05122010
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 12, 2010 at 10:27 am
okay so it really bugs me when I say code "can't" do something
--**********************************************************
--Enable the ability to set advance settings and xp_cmdshell
--**********************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
--******************************************************
--Declare variable's to use in the code and set their values
--******************************************************
declare @myTable as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileInfo varchar(max))
declare @myTable2 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max))
declare @myTable3 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @myTable4 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @filecount int
declare @i int
declare @filepath varchar(100)
declare @sqlcmd varchar(max)
declare @sqlcmd2 varchar(max)
declare @sqlcmd3 varchar(max)
declare @filename1 varchar(100)
set @filepath = '"F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'
set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''
set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''
set @i = 1
--*********************************************************
--insert the contents of the direcotry path into our table
--variables. @myTable will hold the file names, @myTable2
--will hold all of the file names and the size and date of
--creation
--********************************************************
insert @myTable exec(@sqlcmd)
insert @myTable2 exec(@sqlcmd2)
--******************************************************
--we only want to delete the database files delete all info
--that is null or is not an a databasefile
--******************************************************
delete from
@myTable2
where FileNames not like 'HFMSYSDB%' or FileNames is null
--******************************************************
--Insert into @myTable3 the FileName and Date info from
--the file directory, convert the char date text into a
--real datetime field
--******************************************************
insert into @myTable3
select
mt2.filenames,
Case (select count(*) from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%'))
when 1 then
(
convert(datetime,left((select mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%')), 20))
)
else
(
convert(datetime,left((select top 1 mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%') and mt1.FileInfo like ('%' + mt2.FileNames + '_%')), 20))
)
end as FileCreationDate
from @myTable2 mt2
--*****************************************************
--Reinsert all data into @myTable4 so the myID column
--will incriment at the same rate as the counter we will
--use to delete these files from the directory
--*****************************************************
insert into @myTable4
select FileNames, FileCreationDate
from @myTable3
order by FileCreationDate
--*****************************************************
--Set the @filecount variable that we will use for our
--loop
--*****************************************************
set @filecount = (select COUNT (*) from @myTable3 where FileCreationDate < DATEADD(dd, -4,getdate()))
--*******************************************************
--Begin loop, @i should match @myTable4.myID, so that way
--we can select a filename from @myTable4 to delete from
--the file structure.
--*******************************************************
while @i <= @filecount
Begin
set @filename1 = (select filenames from @myTable4 where myID= @i)
set @sqlCmd3 = 'master..xp_cmdshell' + ''''+ 'del /Q ' + @filepath + @filename1 + ''''
exec(@sqlCmd3)
set @filename1=''
set @sqlcmd3=''
set @i = @i + 1
end
--*******************************************************
--Re-Configure the server not to display advanced options
--Re-Configure the server not to allow xp_cmdshell commands
--to be run
--*******************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 0
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
this should now account for the dbname without an underscore
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 12, 2010 at 12:31 pm
Ok, that worked great for the bak files on the root, now I think it has deleted .trn files on the sub folders not following the rules, I'm missing log files for today :s
Is it possible?
Thanks a lot for the help
Lucas
May 12, 2010 at 12:33 pm
Icibert
what version and SP of SQL Server 2005 are you using? (e.g. 9.00.1399, etc)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2010 at 12:37 pm
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) = 2005 SP1 RTM
And I cannot upgrade it because it's not supported by the application vendor for what I'm running on that server.
Thanks
May 12, 2010 at 12:49 pm
Hey Lucas,
That should not be possible, the dir command just looks in the directory that it is specified to. this will not work against sub folders, you would need to specify the sub folder, or create a code that would loop the script to look under each sub folder
set @filepath = '"F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'
set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''
here's the msdn on xp_cmdshell, it references the 'dir' command, but doesn't have much more that it discusses
http://msdn.microsoft.com/en-us/library/ms175046.aspx
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 12, 2010 at 2:13 pm
lcibert (5/12/2010)
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) = 2005 SP1 RTMAnd I cannot upgrade it because it's not supported by the application vendor for what I'm running on that server.
Thanks
Thought so, Maint plans are all fixed in service pack 2
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2010 at 3:31 pm
Perry Whittle (5/12/2010)
lcibert (5/12/2010)
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) = 2005 SP1 RTMAnd I cannot upgrade it because it's not supported by the application vendor for what I'm running on that server.
Thanks
Thought so, Maint plans are all fixed in service pack 2
I know there are some issues with the first versions, but what I can't understand is that it worked fine for years and now it started with this issue.
May 13, 2010 at 1:04 am
Have any other hotfixes been applied at all?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 13, 2010 at 6:28 am
Can't answer for him, but based on what he has posted I would say no.
He's working with 9.0.2047 the SP 1 RTM release number
http://sqlserverbuilds.blogspot.com/
Lucas if it gives you any ammo, you could let your boss know that not only has support ended for SQL 2005 SP 1, but SP 2 support ended in January. SP 3 is the only covered version out right now. If you guys ever had an error or a stack dump that required Microsoft PSS the first thing they would tell you is you have to upgrade to a supported version.
FYI SP 4 will be out in Q 4 of this year, and it will be the final SP released for SQL 2005.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 13, 2010 at 8:23 am
Thanks a lot for all your help guys.
I know this server is out of date, but for now I cannot upgrade it, but I have a project to migrate that application to a new server fully updated so this will not be an issue in about 2 month, but I need this server running as is till then.
Once again, thanks for all your help, it's truly appreciated.
Lucas
May 13, 2010 at 8:25 am
No worries Lucas Happy to Help!:-D
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 13, 2010 at 1:50 pm
Bradly, today I tested it again after I had a few .trn files and 2 sets of .bak files. I run the script for each database, and I check exactly what I had before and after. It removed correctly the .bak files but it also wiped out completly everything that was on each sub folder with the database name.
The bak files are on the root of:
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
and the .trn files are on:
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DEVELOP1
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\HBIPLUSSYSDB
....
etc
This is what I run:
--**********************************************************
--Enable the ability to set advance settings and xp_cmdshell
--**********************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
--******************************************************
--Declare variable's to use in the code and set their values
--******************************************************
declare @myTable as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileInfo varchar(max))
declare @myTable2 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max))
declare @myTable3 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @myTable4 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @filecount int
declare @i int
declare @filepath varchar(100)
declare @sqlcmd varchar(max)
declare @sqlcmd2 varchar(max)
declare @sqlcmd3 varchar(max)
declare @filename1 varchar(100)
set @filepath = '"F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'
set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''
set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''
set @i = 1
--*********************************************************
--insert the contents of the direcotry path into our table
--variables. @myTable will hold the file names, @myTable2
--will hold all of the file names and the size and date of
--creation
--********************************************************
insert @myTable exec(@sqlcmd)
insert @myTable2 exec(@sqlcmd2)
--******************************************************
--we only want to delete the database files delete all info
--that is null or is not an a databasefile
--******************************************************
delete from
@myTable2
where FileNames not like 'DEVELOP%' or FileNames is null
--******************************************************
--Insert into @myTable3 the FileName and Date info from
--the file directory, convert the char date text into a
--real datetime field
--******************************************************
insert into @myTable3
select
mt2.filenames,
Case (select count(*) from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%'))
when 1 then
(
convert(datetime,left((select mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%')), 20))
)
else
(
convert(datetime,left((select top 1 mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%') and mt1.FileInfo like ('%' + mt2.FileNames + '_%')), 20))
)
end as FileCreationDate
from @myTable2 mt2
--*****************************************************
--Reinsert all data into @myTable4 so the myID column
--will incriment at the same rate as the counter we will
--use to delete these files from the directory
--*****************************************************
insert into @myTable4
select FileNames, FileCreationDate
from @myTable3
order by FileCreationDate
--*****************************************************
--Set the @filecount variable that we will use for our
--loop
--*****************************************************
set @filecount = (select COUNT (*) from @myTable3 where FileCreationDate < DATEADD(dd, -1,getdate()))
--*******************************************************
--Begin loop, @i should match @myTable4.myID, so that way
--we can select a filename from @myTable4 to delete from
--the file structure.
--*******************************************************
while @i <= @filecount
Begin
set @filename1 = (select filenames from @myTable4 where myID= @i)
set @sqlCmd3 = 'master..xp_cmdshell' + ''''+ 'del /Q ' + @filepath + @filename1 + ''''
exec(@sqlCmd3)
set @filename1=''
set @sqlcmd3=''
set @i = @i + 1
end
--*******************************************************
--Re-Configure the server not to display advanced options
--Re-Configure the server not to allow xp_cmdshell commands
--to be run
--*******************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 0
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
I did a job with this same script repeted x step times replacing the database name in each one.
I'm asuming that as the .trn files are in a sub folder starting with the same name of the DB it's deleting whatever content is in there. Can I add the extension to this script so that it only deletes .bak files?
Thanks
May 14, 2010 at 6:35 am
Hey Lucas to check for file type I added the code to check for the last four characters on the filenames. What I edited is below.
delete from
@myTable2
where FileNames not like 'HFMSYSDB%' or FileNames is null
or lower(RIGHT(FileNames, 4)) <> '.bak'
I did it for 4 characters as I wanted to make sure it is only with the .bak extention, if you wanted to manage log files you could just change this to the .trn extention.
The backups must use this type of extention or they will not be deleted.
Here is the whole thing:
--**********************************************************
--Enable the ability to set advance settings and xp_cmdshell
--**********************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure
go
--******************************************************
--Declare variable's to use in the code and set their values
--******************************************************
declare @myTable as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileInfo varchar(max))
declare @myTable2 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max))
declare @myTable3 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @myTable4 as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
FileNames varchar(max),
FileCreationDate datetime)
declare @filecount int
declare @i int
declare @filepath varchar(100)
declare @sqlcmd varchar(max)
declare @sqlcmd2 varchar(max)
declare @sqlcmd3 varchar(max)
declare @filename1 varchar(100)
set @filepath = '"F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'
set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''
set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''
set @i = 1
--*********************************************************
--insert the contents of the direcotry path into our table
--variables. @myTable will hold the file names, @myTable2
--will hold all of the file names and the size and date of
--creation
--********************************************************
insert @myTable exec(@sqlcmd)
insert @myTable2 exec(@sqlcmd2)
--******************************************************
--we only want to delete the database files delete all info
--that is null or is not an a databasefile or anything
--that does not have a .bak extention
--******************************************************
delete from
@myTable2
where FileNames not like 'HFMSYSDB%' or FileNames is null
or lower(RIGHT(FileNames, 4)) <> '.bak'
--******************************************************
--Insert into @myTable3 the FileName and Date info from
--the file directory, convert the char date text into a
--real datetime field
--******************************************************
insert into @myTable3
select
mt2.filenames,
Case (select count(*) from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%'))
when 1 then
(
convert(datetime,left((select mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%')), 20))
)
else
(
convert(datetime,left((select top 1 mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%') and mt1.FileInfo like ('%' + mt2.FileNames + '_%')), 20))
)
end as FileCreationDate
from @myTable2 mt2
--*****************************************************
--Reinsert all data into @myTable4 so the myID column
--will incriment at the same rate as the counter we will
--use to delete these files from the directory
--*****************************************************
insert into @myTable4
select FileNames, FileCreationDate
from @myTable3
order by FileCreationDate
--*****************************************************
--Set the @filecount variable that we will use for our
--loop
--*****************************************************
set @filecount = (select COUNT (*) from @myTable3 where FileCreationDate < DATEADD(dd, -4,getdate()))
--*******************************************************
--Begin loop, @i should match @myTable4.myID, so that way
--we can select a filename from @myTable4 to delete from
--the file structure.
--*******************************************************
while @i <= @filecount
Begin
set @filename1 = (select filenames from @myTable4 where myID= @i)
set @sqlCmd3 = 'master..xp_cmdshell' + ''''+ 'del /Q ' + @filepath + @filename1 + ''''
exec(@sqlCmd3)
set @filename1=''
set @sqlcmd3=''
set @i = @i + 1
end
--*******************************************************
--Re-Configure the server not to display advanced options
--Re-Configure the server not to allow xp_cmdshell commands
--to be run
--*******************************************************
exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'xp_cmdshell', 0
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 2:45 pm
Your so good!!
That worked great!...once again thanks a lot!!!
I really appreciate it!
Thanks
Lucas
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply