SQL Server xp_cmdshell

Question

Is their any other way to get a list of file names via T-SQL other than

INSERT INTO @backups(filename) EXEC master.sys.xp_cmdshell 'DIR /b c:\some folder with sql backups in it

I am attempting to get a list of sql backup files from a folder to restore and I do NOT want to use the xp_cmdshell for obvious security reasons.

Answer 1

If you have access to the server that backup up the files, you can use the system tables to find the backup file(s) you prefer.

http://msdn.microsoft.com/en-us/library/ms188062.aspx

You'll be interested in the backup tables.

Answer 2

Three options, depending on your environment and needs:

1. If you're using SQL2005 or 2008, you can almost certainly write a CLR stored procedure to do this job. If you haven't done it before, it's probably more work than you're looking to do, but since I already have a project I could add this to, it's probably what I would do if I really needed SQL to be able to read from a directory.
2. As Sam suggests, if you have access to the source of the backups, you can query the tables in the MSDB database. My suggestion might be to query msdb.dbo.backupmediafamily.physical_device_name to get a list of files that might be in available to you, then test if they exist by using: RESTORE FILELISTONLY disk='FULL_PATH_TO_YOUR_FILE'. This throws a non-fatal error if the file doesn't exist. You can check for an error in T-SQL by testing if @@error is non-zero.
3. If your environment makes it possible, a quick script running in Windows, outside of SQL Server, might be your best bet. You can set it up as a scheduled task if you need to. You could, for example, have it run every 15 minutes, check if a file has appeared since the last time the script ran, and insert any files into a table in SQL Server. I've done similar-enough tasks in Perl, Ruby, and VBScript. It could probably be done with a batch file, too. Again, I don't know your exact needs or skillset, but if I just needed to get this done, and didn't 100% need it to run from within SQL Server, I'd probably just write a script.

Answer 3

The alternative to xp_cmdshell I came up is below:

My situation is a little unique in that I am attempting a poor man's log shipping. I have a folder (not the default SQL backup location) where transaction logs are deposited from a remote server. I am writing a c# app that loops through that directory and creates a comma delimited string of file names with paths (e.g. FooA.txn,FooB.txn,FooC.txn). I then feed this string into a stored procedure that I wrote which parses the string and inserts the file names into a temp table. Then I loop through the temp table restoring each transaction log in order.

Basically the C# app replaces xp_cmdshell and from the app I can call a stored procedure.