Delete backup file in transact-sql during database copy/restore

Question

I have a "copy" stored procedure for a database. During the copy, I backup the database with something like:

exec('backup database [' + @source + '] ' + 
   'to disk = N''' + @backupdir + '\copybackup'' ' +
   'with noformat, noinit, name=N''copybackup-full'', ' + 
   'SKIP, NOREWIND, NOUNLOAD,  STATS = 10;');

And then create and empty database, and restore to it:

exec('create database [' + @dest + '];');

exec('restore database [' + @dest + '] ' + 
   'from disk = N''' + @backupdir + '\copybackup'' ' + 
   'with file = 1, ' +
   'move N''' + @source + ''' to N''' + @dbdir + '\' + @dest + 
                 '.mdf'', ' +
   'move N''' + @source + '_log'' to N''' + @dbdir + '\' + @dest +
                 '_log.ldf'', ' +
   'NOUNLOAD,  REPLACE,  STATS = 10;');

So all is well and good. Except, now I'm left with a file at @backupdir\copybackup that I really don't want. How do I get rid of it?

Since this is a stored procedure, I really don't want to have to wrap it all in a batch file or some other hokey thing on the server itself. I'd like to take care of it from T-SQL right here when I create the mess in the first place. I've grepped through the MS Docs, but no joy. (SQL Server 2005, please)

Ideas?

Answer 1

You aren't going to be able to use T-SQL straight up to do this. If there was a way for T-SQL to delete files on the filesystem, that would be a very bad thing.

You have two options if you want to keep this in SQL Server. More than likely, the Microsoft Scripting Library is installed. You could use the sp_OA* stored procedures to create an instance of the Scripting.FileSystemObject and then delete the file.

However, this is a bad idea, since SQL Server can't really protect itself against malicious code in this case and it can't dictate memory management in the server process either.

The preferred solution would be to create a CLR stored procedure which will take the path of the file that you wish to delete and then use the classes in the System.IO namespace to delete the file (using the File or FileInfo classes).

This way, SQL Server can sandbox the execution of the CLR stored procedure, and it also has control over memory management of the CLR.