SQL Server xp_cmdshell

Question

Is there 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

I do realize that this thread is 5 years old but I thought I'd post another non-xpCmdShell, non-CLR alternative. Details are in the following code and it's pretty simple stuff.

--===== Define the path and populate it.
     -- This could be a parameter in a proc
DECLARE @pPath VARCHAR(512);
 SELECT @pPath = 'C:\Temp';

--===== Create a table to store the directory information in
 CREATE TABLE #DIR
        (
        RowNum     INT IDENTITY(1,1),
        ObjectName VARCHAR(512),
        Depth      TINYINT,
        IsFile     BIT,
        Extension AS RIGHT(ObjectName,CHARINDEX('.',REVERSE(ObjectName))) PERSISTED
        )
;
--===== Get the directory information and remember it
 INSERT INTO #DIR
        (ObjectName,Depth,IsFile)
   EXEC xp_DirTree 'C:\Temp',1,1
;
--===== Now do whatever it is you need to do with it
 SELECT * FROM #DIR;

Answer 2

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 3

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 4

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.