How to develop database locally and post to web host?

Hongju - can you be a little more specific on how to back up and restore the
databases. I have a very similar problem. I created a project that has a
database in APP_DATA. I'm hosting on GoDaddy and I need to take the APP_DAT
A
database (.mdf) and move it to one of their MySQL databases. I have no clue
on how to do that. Any help would be greatly appreciated.
Thanks
"hongju" wrote:
> You can backup local database and restore on the host database.
> If you developed database file using Visual Studio, you can attach databas
e
> on the host.
> And can modifiy connection string.
> "Noozer"?? ??? ??:
>Moving it to a MySQL database might take a little more work. Use the
"Generate scripts..." function in Management Studio and run those script
in the mysql database.
You will most likely need to modify them some to get them to work.
It's probably a good idea not to generate scripts for everything at
once, but to start with the tables first, then the views, then the SP's
etc etc...
Michelle wrote:
> Hongju - can you be a little more specific on how to back up and restore t
he
> databases. I have a very similar problem. I created a project that has a
> database in APP_DATA. I'm hosting on GoDaddy and I need to take the APP_D
ATA
> database (.mdf) and move it to one of their MySQL databases. I have no cl
ue
> on how to do that. Any help would be greatly appreciated.
> Thanks
> "hongju" wrote:
>

How to design Database to search faster from 1 million customer''s

I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.

Application serves two functionalities

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

Scenario: The above requirement can be complemented by following example

Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.

Now if some new customer want to open a new account in bank, then bank officials want to know whether the

new customer is one of the existing customer or not(without asking to customer itself).

System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface

then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).

Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).

Scenario: The above requirement can be complemented by following example

If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.

I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.

The bank example is not a good one, as the banks are always requesting a unique identifying attribute from the customers (like an id from their id card or their SSN). If you want to search through all the fields, you would have to implement something like fulltext searching / soundex functionality (as I assume that you did not wrote Jhon instead of John accidentially)

Requirement 2 is not a database issue, as the other bank database is normally not on the same server and is normally reached via a Web service through a service bus.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jeans for replying to my post.

Perhaps you got me otherwise.Let me now tell you exact sitiuation.

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

We consider exact sitiuation here,

We have a huge (about 1 million records) database of people involved in loan, credit card or any kind of fraud against banks.It is combined database for all banks in a country and each bank is contributing a list of defaulters from it's side to this combined database.

Now if some new customer comes to avail the services of a bank, then bank wants to ensure that new customer never appeared in this defaulter list before.

But one important searching criteria for matching against this defaulter's dabase is that we should able to carryout sounlike search i.e (let me explain with one example mentioned below)

If a person names "Mohammed Ali", then our application should be able to find out variation of names which sounds similar to orignal name of person, i.e

"Mohammad Ali"

"Mohamad Ali"

This requirement is expected from banks if new customer comes to bank with fake ID Proof or with forged documents to avail the services. In this case there will be no pre defined unique id for customer or any identifier and application will have to solely on name matching logic.

My concern is mostly associated with the performance of application, especially to the database design (for optimal performance), rather then logic of search.

|||

There are a lot of factors to take into account - hardware architecture, I/O, memory, database structure, etc. database architecture also has to be considered - creating filegroups which will contain the database files, storing the database files in multiple drive spindles, creating the tables to be stored in filegroups so that searches can be performed by multiple drive splindles, etc.

|||

Thanks bass,for your reply

But our main focus is on database design rather then Hardware configuration...Hardware is not a issue as we have plenty for our use.

I shall appriciate your efforts if you can suggest something for DB design or tuning of database.

|||

Although the soundex functionality is implemented in SQL Server there might be more sophisticated algorithms out there that might fit your need better than the SOUNDEX in SQL Server does. but to your point about the performance of the system: there should be no problem in searching even "large" (though 1M is not that large) database for the names passed by the application. My design suggestion would be to store each name normalalized in tables, generate the soundex words either in your frontend application or using a CLR function and query your table for the soundex terms. By the score of matching and found items you can choose to display the TOP N customers who match the names passed or who have a certain score (e.g. passing John Smith might bring back a long list of entries :-) ) If the tables gets even bigger you could decide to use table partitiioning to scale out your design.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to deploy an assembly (CLR)?

Good day to ALL,

I have already setup a db to my client.

My sp's are all created using CLR.

If my sp's are changed, how do I deploy them back to my client?

I usually send them a backup of the DB during the first few implementation.

But currently, their DB now contains live data, so I can't just let them restore the backup.

Is there another way?

Thanks and more power!

Arthur

If you just changed the code without adding any param on any other object you just need to issue an ALTER ASSEMBLY

If you added some object then you need to
- ALTER ASSEMBLY
- CREATE PROCEDURE or FUNCTION

If your changes are very hard, say you have modified SP params you need to drop all your objects, drop the assembly and recreate all from scartch.

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. Thexp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben NevarezI am having the same exact problem. Does anybody have the answer?

Scott Suddarth|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent

How to Delete Files on Maintenance Plan

Hello,

I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.

But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?

I do not see any documentarion about xp_delete_file either.

Thanks,

Ben Nevarez
I am having the same exact problem. Does anybody have the answer?

Scott Suddarth
|||

I have the same problem, I guess that you still did not find the solution?.

If ever,tell me..

FKy

THX

|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||

I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".

Additionally, there is a problem with the date format on non-English servers.

I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.

|||

We are taking this bug seriously and evaluating to get this addressed soon.

The suggested workaround in the blog is recommended, till this one gets addressed.

Thanks,

Gops Dwarak

|||

I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.

Thanks,

Gops Dwarak

|||

The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.

This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.

Thanks for listening!

Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.

I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.

|||

My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.

Best regards

Walter

|||

Hi

This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)

I'll prefere the same functionality to delete old files like in SQL2000

best reagards

Cougar J

|||

I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.

On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.

I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.

|||

Thanks for all your feedback , this issue is getting addressed for Service Pack 1.

The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000

Gops Dwarak

|||

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

|||

Kmartin wrote:

Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?

Thanks again,
Kevin

Seconding Kmartin's question - any idea when we might see SP1?

Many thanks,

Megan

|||

I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.

Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.

** I've changed the date between all of these steps and the .nb7 files are older than 1 month.

** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.

** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.

** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.

** The maintenance plan, both the daily and the testing, are being executed under the SA account.

** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\

** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.

**The maintenance tasks execute successfully.

Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?

I could just being losing it, but I could have swore that the I got this working before.

Please verify my findings? (SQL only deletes SQL files)

-Brent