January 26, 2018 at 2:25 pm
I have two servers that the XP_delete_file command recognizes the sql backups created in SSMS as invalid backups. The files pass a restore headeronly correctly. I can restore the backups with no problems. I have even copied a few of the backups to another machine and ran the same exact command in SSMS and it deleted them. It's SQL 2014. I realize that the XP_delete_file command is undocumented and buggy, but that is the command that sql uses to delete older backups.
Permissions are not the problem. For testing purposes only, I changed sql server and server agent services to both run as one of the domain administrator accounts that has full permissions to the files and it made no difference. I can manually delete the files. sql account used is a sysadmin account.
Scripts used are: EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\',N'bak',N'2017-12-28T14:32:33',1 completes successfully, but nothing is deleted and EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup.bak' which gives the error that the file is not a sql backup.
Is there any way to force sql to recognize the backup files it creates as valid backup files? This problem is affecting all backup files created whether they are created with a maintenance plan or created manually. The logic built into the maintenance plan will not even run the cleanup scripts until a full backup completes, but because they are not recognized correctly the cleanup scripts are executing successfully because it doesn't "see" any backups left to delete.
January 26, 2018 at 3:42 pm
chrisr2 - Friday, January 26, 2018 2:25 PMI have two servers that the XP_delete_file command recognizes the sql backups created in SSMS as invalid backups. The files pass a restore headeronly correctly. I can restore the backups with no problems. I have even copied a few of the backups to another machine and ran the same exact command in SSMS and it deleted them. It's SQL 2014. I realize that the XP_delete_file command is undocumented and buggy, but that is the command that sql uses to delete older backups.Permissions are not the problem. For testing purposes only, I changed sql server and server agent services to both run as one of the domain administrator accounts that has full permissions to the files and it made no difference. I can manually delete the files. sql account used is a sysadmin account.
Scripts used are: EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\',N'bak',N'2017-12-2​8T14:32:33',1 completes successfully, but nothing is deleted and EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup.bak' which gives the error that the file is not a sql backup.
Is there any way to force sql to recognize the backup files it creates as valid backup files? This problem is affecting all backup files created whether they are created with a maintenance plan or created manually. The logic built into the maintenance plan will not even run the cleanup scripts until a full backup completes, but because they are not recognized correctly the cleanup scripts are executing successfully because it doesn't "see" any backups left to delete.
Double check the backup folder to make sure the SQL Server service account has full control and inheritance is in order (folder, subfolder, files).
Sue
January 26, 2018 at 3:51 pm
Sue, I have triple checked the backup folder permissions, even down to the file level itself. The sql server agent and sql service account have full control. It is searching the folder looking for the files. I have tested this on the largest folder by intentionally putting .bak into the script versus just bak. When using .bak, the script finishes instantly, when using bak it takes minutes to complete. In those minutes, it is reading the file headers to verify they are sql backup files that can be deleted. The problem is that sql isn't recognizing the backup files that it creates as backup files so it isn't deleting them, but the restore headeronly command is successful on each and every file that I have tried it on.
January 26, 2018 at 4:08 pm
chrisr2 - Friday, January 26, 2018 3:51 PMSue, I have triple checked the backup folder permissions, even down to the file level itself. The sql server agent and sql service account have full control. It is searching the folder looking for the files. I have tested this on the largest folder by intentionally putting .bak into the script versus just bak. When using .bak, the script finishes instantly, when using bak it takes minutes to complete. In those minutes, it is reading the file headers to verify they are sql backup files that can be deleted. The problem is that sql isn't recognizing the backup files that it creates as backup files so it isn't deleting them, but the restore headeronly command is successful on each and every file that I have tried it on.
It doesn't just read the headers though and even though that tidbit was leaked a long time ago, they never did say what exactly it was looking for in the file header for databases. A connect item was filed about this years ago and they essentially said "Good Luck" if anyone need this to work reliably. Here is the link - read the Microsoft Program Managers comments:
xp_delete_file not working
Is there a reason you have to use xp_delete_file? I've been using Powershell for quite awhile to clean up backup files. People also use xp_cmdshell to do the same. So there are other options which are documented and the use of those is supported.
Sue
January 26, 2018 at 4:14 pm
Basically, I'm an accidental DBA. I have learned a lot, but still have a lot to learn. I use the sql maintenance plan because I can set it to run after a completed full backup. Most of the servers that I assist with are from small customers that our company provides assists with their backups of our software for free. With that being said, there is no way we can log into each server everyday to check everything. SSMS uses the XP_delete_file in the maintenance plans for cleanup.
January 26, 2018 at 4:30 pm
chrisr2 - Friday, January 26, 2018 4:14 PMBasically, I'm an accidental DBA. I have learned a lot, but still have a lot to learn. I use the sql maintenance plan because I can set it to run after a completed full backup. Most of the servers that I assist with are from small customers that our company provides assists with their backups of our software for free. With that being said, there is no way we can log into each server everyday to check everything. SSMS uses the XP_delete_file in the maintenance plans for cleanup.
But using a supported method doesn't mean you have to log into all servers and check everything. There is no difference administration-wise between a job that runs xp_delete_file and a job that has a powershell script to delete files or a job that has an xp_cmdshelll script to delete files. The only difference is that xp_delete_file isn't supported.
SSMS was written by Microsoft so they can use it and it was intended for their use only. And when you generate the T-SQL, they have some thing about it not being exact. The Microsoft response to the connect item in part was:
The stored procedure was never intended to be called by an end user and the chances that you could ever be successful in having it do what you intend are almost zero. I took a quick peek at the implementation we are using and it takes some very specialzed arguments for a very specific task.
Everything you ever read about using it is not documented anywhere by Microsoft so really is a best guess by someone. I wouldn't use it.
Sue
January 26, 2018 at 4:56 pm
chrisr2 - Friday, January 26, 2018 4:14 PMBasically, I'm an accidental DBA. I have learned a lot, but still have a lot to learn. I use the sql maintenance plan because I can set it to run after a completed full backup. Most of the servers that I assist with are from small customers that our company provides assists with their backups of our software for free. With that being said, there is no way we can log into each server everyday to check everything. SSMS uses the XP_delete_file in the maintenance plans for cleanup.
Real DBA's don't use maintenance plans. Heck, real DBA's don't even want to take backups if it weren't for the wimpy bosses. <This is a bad-taste joke but I had to tell>
If you manage lots of other people's DB servers, create a set of jobs and alerts on each target server. These jobs and alerts should gather vital data into a set of tables. This data-gathering is the initial stage self-diagnosis. Then you can have a monitoring script that periodically query those tables and evaluate whether the deltas warrant further investigation.
There is no set rules on how you should manage a server. Find the best way to free up your time. But don't tell your boss.
January 29, 2018 at 10:40 am
RandomStream - Friday, January 26, 2018 4:56 PMchrisr2 - Friday, January 26, 2018 4:14 PMBasically, I'm an accidental DBA. I have learned a lot, but still have a lot to learn. I use the sql maintenance plan because I can set it to run after a completed full backup. Most of the servers that I assist with are from small customers that our company provides assists with their backups of our software for free. With that being said, there is no way we can log into each server everyday to check everything. SSMS uses the XP_delete_file in the maintenance plans for cleanup.Real DBA's don't use maintenance plans. Heck, real DBA's don't even want to take backups if it weren't for the wimpy bosses.
If you manage lots of other people's DB servers, create a set of jobs and alerts on each target server. These jobs and alerts should gather vital data into a set of tables. This data-gathering is the initial stage self-diagnosis. Then you can have a monitoring script that periodically query those tables and evaluate whether the deltas warrant further investigation.
There is no set rules on how you should manage a server. Find the best way to free up your time. But don't tell your boss.
I guess I'll have to say "speak for yourself" when it comes to what "real DBA's" do or want to do. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 29, 2018 at 11:04 am
Jeff Moden - Monday, January 29, 2018 10:40 AMRandomStream - Friday, January 26, 2018 4:56 PMchrisr2 - Friday, January 26, 2018 4:14 PMBasically, I'm an accidental DBA. I have learned a lot, but still have a lot to learn. I use the sql maintenance plan because I can set it to run after a completed full backup. Most of the servers that I assist with are from small customers that our company provides assists with their backups of our software for free. With that being said, there is no way we can log into each server everyday to check everything. SSMS uses the XP_delete_file in the maintenance plans for cleanup.Real DBA's don't use maintenance plans. Heck, real DBA's don't even want to take backups if it weren't for the wimpy bosses.
If you manage lots of other people's DB servers, create a set of jobs and alerts on each target server. These jobs and alerts should gather vital data into a set of tables. This data-gathering is the initial stage self-diagnosis. Then you can have a monitoring script that periodically query those tables and evaluate whether the deltas warrant further investigation.
There is no set rules on how you should manage a server. Find the best way to free up your time. But don't tell your boss.
I guess I'll have to say "speak for yourself" when it comes to what "real DBA's" do or want to do. 😉
Corrected. Thx.
January 29, 2018 at 11:08 am
Check the author's last comment in the following post.
“Specified file is not a SQL Server backup file” Error with xp_delete_file
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply