Search code examples
sql-servert-sqlbackupsql-server-2012smo

SQL Server 2012 : getting a list of available backups


I have a client - server desktop application (.NET) and client have to get list of available backup files stored in default back up folder (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup)

I decided to create a stored procedure which will return table with all needed files:

CREATE PROCEDURE [dbo].[spGetBackUpFiles] AS 
SET NOCOUNT ON
BEGIN

declare @backUpPath as table 
(
 name nvarchar(max),
 backuppath nvarchar(max)
)

insert into @backUpPath
EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

declare @table as table 
(
 filename nvarchar(max),
 depth int,
 filefile int
)

declare @backUpFilesPath as nvarchar(max) = (select top 1 backuppath from @backUpPath)

insert into @table
 EXEC xp_dirtree @backUpFilesPath, 1, 1

SELECT * FROM @table WHERE filename like N'MASK[_]%'
END

But I am getting following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

You can try this script on your machine...

What can be the problem?

Is there another way to get list of available backups (may be using SMO library) ?


Solution

  • It's to do with the way you pass the filepath to xp_dirtree, the only way I could get it working was with a temp table and dynamic SQL, like so:

    CREATE PROCEDURE [dbo].[spGetBackUpFiles]
    AS 
        SET NOCOUNT ON
        BEGIN
    
            IF OBJECT_ID('tempdb..#table') IS NOT NULL 
                DROP TABLE #table
    
            CREATE TABLE #table
                (
                  [filename] NVARCHAR(MAX) ,
                  depth INT ,
                  filefile INT
                )
    
            DECLARE @backUpPath AS TABLE
                (
                  name NVARCHAR(MAX) ,
                  backuppath VARCHAR(256)
                )
    
            DECLARE @SQL NVARCHAR(MAX)
    
            INSERT  INTO @backUpPath
                    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                        N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                        N'BackupDirectory'
    
            DECLARE @backUpFilesPath AS NVARCHAR(MAX) = ( SELECT TOP 1
                                                                  backuppath
                                                          FROM    @backUpPath
                                                        )
    
    
    
            SET @SQL = 'insert into #table
            EXEC xp_dirtree ''' + @backUpFilesPath + ''', 1, 1'
    
            EXEC(@SQL)
    
            SELECT  *
            FROM    #table WHERE [filename] like N'MASK[_]%'
            DROP TABLE #table
        END