[RESOLVED] SQL Server 2005 Auto Restore

:confused:
I know it sounds like a bad title but here is what I want to do:

I have backups running of various SQL Server (actually LiteSpeed backups but not important for this point). The backups are named DBName_date_time.bak and stored on a server.

I want to find the lastest backup of the database (full each night) and apply that to another server (a reporting and test server). I understand how to call the LiteSpeed restore xp_ proc and what goes inside it.

How do I get the latest file for each database using T-SQL.

Gary

The backups - if done with a mainteance plan - are stored in some tracking table. Have you tried to find this system table with backup history info?

I'm 95% sure of this...

(info below and link might be useful)

Tracking Tables

Several tables exist in the msdb database that track the backup operations which occurred on the server. These tables include:

backupfile - Contains one row for each data or log file that is backed up
backupmediafamily - Contains one row for each media family
backupmediaset - Contains one row for each backup media set
backupset- Contains a row for each backup set

http://vyaskn.tripod.com/backup_and_..._litespeed.htm

In the env I'm in I'm not sure I get the luxury of looking back the database where the backups occurred from.

I'm thinking of using xp_cmdShell to perform a Dir operation and storing the results to a table then querying the table for the latest date.

Here is what I came up with so far:

sql Code:

CREATE PROCEDURE dbo.MyDdRestore
(
    @path varchar(2000), 
    @Supress int = 0,
    @DBName varchar(100),
    @PhysicalDataName Varchar(100),
    @PhysicalLogName Varchar(100),
    @NewPathDataFile Varchar(1000),
    @NewPathLogFile Varchar(1000)
)
AS
 
SET NOCOUNT ON
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
 
TRUNCATE TABLE Directory_Contents_Stage
TRUNCATE TABLE Directory_Contents
 
    DECLARE @cmd varchar(4000)
    IF Right(@path,7) <> '\*.bak' 
        BEGIN
            Set @path = @path + '\*.bak'
        END 
 
     SELECT @cmd = 'Dir "' + @path + '"'
     --print @cmd
INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd
 
  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         EXEC sp_configure 'show advanced options', 1
         -- To update the currently configured value for advanced options.
         RECONFIGURE
         -- To enable the feature.
         EXEC sp_configure 'xp_cmdshell', 0
         -- To update the currently configured value for this feature.
         RECONFIGURE
 
         RETURN
    END
 
  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         EXEC sp_configure 'show advanced options', 1
        -- To update the currently configured value for advanced options.
        RECONFIGURE
        -- To enable the feature.
        EXEC sp_configure 'xp_cmdshell', 0
        -- To update the currently configured value for this feature.
        RECONFIGURE
        RETURN
    END         
 
  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         EXEC sp_configure 'show advanced options', 1
         -- To update the currently configured value for advanced options.
         RECONFIGURE
         -- To enable the feature.
         EXEC sp_configure 'xp_cmdshell', 0
         -- To update the currently configured value for this feature.
         RECONFIGURE
         RETURN
    END
 
     UPDATE Directory_Contents_Stage SET [dir] = @path
 
DELETE FROM Directory_Contents WHERE [dir] = @path
 
INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , CONVERT(bigint,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] 
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
     , [dir]
     , 'FILE'
      FROM  Directory_Contents_Stage
     WHERE  SUBSTRING(dir_output,1,1) <> ' '
       AND (SUBSTRING(dir_output,1,1) <> ' ' 
       AND  SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62)) 
 
 
INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) As [File_Name]
     , [dir]
     , CHAR(60)+'Dir'+CHAR(62) AS  [Struct_Type]
       FROM  Directory_Contents_Stage 
      WHERE  SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62) 
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
 
IF @Supress = 0
 
  DECLARE c1 CURSOR FOR   Select File_name From Directory_Contents Where Create_time In (
                            select max(create_Time)From Directory_Contents
                            where Struct_Type = 'FILE' And 
                                  File_Name LIKE   '%_db%' ESCAPE '_'
                            Group by Substring(File_name,1,14) )
 
    Declare @FileName as Varchar(100)
    Open c1
    Fetch c1 into @FileName
    WHILE @@FETCH_STATUS = 0 
        BEGIN
            IF @DBName = SubString(@FileName,1,Len(@DBName)) 
                BEGIN
                    DECLARE @cmdRestore Varchar(max)
                    DECLARE @sMovingData Varchar(max)
                    DECLARE @sMovingLog VARCHAR(Max)
 
                    if @NewPathLogFile <> '' 
                       Set @sMovingLog = ', @with = ''move ''' + @PhysicalLogName + ''' to ''' + @NewPathLogFile + ''''    
                    
                    if @NewPathDataFile <> '' 
                       Set @sMovingData = ', @with = ''move ''' + @PhysicalDataName + ''' to ''' + @NewPathDataFile + ''''
 
 
                    Set @cmdRestore = 'exec master.dbo.xp_restore_database @database = ''' + @DBName + ''', @filename = ''' +  @FileName + ''',@with=''STATS = 10''' + @sMovingData + @sMovingLog + ',@affinity = 0,@logging = 0'
                    Print @cmdRestore
                END
            Fetch c1 into @FileName
        END
    CLOSE c1
    DEALLOCATE c1

Reworked with out cursor

sql Code:

IF EXISTS (SELECT Name From SYS.OBJECTS WHERE Name = 'proc_MyDdRestore' AND type = 'P')
    DROP PROCEDURE proc_MyDdRestore
GO
 
CREATE PROCEDURE dbo.proc_MyDdRestore
(
    @path varchar(2000), 
    @Supress int = 0,
    @DBName varchar(100),
    @PhysicalDataName Varchar(100),
    @PhysicalLogName Varchar(100),
    @NewPathDataFile Varchar(1000),
    @NewPathLogFile Varchar(1000)
)
AS
 
SET NOCOUNT ON
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
 
TRUNCATE TABLE Directory_Contents_Stage
TRUNCATE TABLE Directory_Contents
 
    DECLARE @cmd varchar(4000)
    IF Right(@path,7) <> '\*.bak' 
        BEGIN
            Set @path = @path + '\*.bak'
        END 
 
     SELECT @cmd = 'Dir "' + @path + '"'
     --print @cmd
INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd
 
  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         EXEC sp_configure 'show advanced options', 1
         -- To update the currently configured value for advanced options.
         RECONFIGURE
         -- To enable the feature.
         EXEC sp_configure 'xp_cmdshell', 0
         -- To update the currently configured value for this feature.
         RECONFIGURE
 
         RETURN
    END
 
  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         EXEC sp_configure 'show advanced options', 1
        -- To update the currently configured value for advanced options.
        RECONFIGURE
        -- To enable the feature.
        EXEC sp_configure 'xp_cmdshell', 0
        -- To update the currently configured value for this feature.
        RECONFIGURE
        RETURN
    END         
 
  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         EXEC sp_configure 'show advanced options', 1
         -- To update the currently configured value for advanced options.
         RECONFIGURE
         -- To enable the feature.
         EXEC sp_configure 'xp_cmdshell', 0
         -- To update the currently configured value for this feature.
         RECONFIGURE
         RETURN
    END
 
     UPDATE Directory_Contents_Stage SET [dir] = @path
 
DELETE FROM Directory_Contents WHERE [dir] = @path
 
INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , CONVERT(bigint,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] 
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
     , [dir]
     , 'FILE'
      FROM  Directory_Contents_Stage
     WHERE  SUBSTRING(dir_output,1,1) <> ' '
       AND (SUBSTRING(dir_output,1,1) <> ' ' 
       AND  SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62)) 
 
 
INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) As [File_Name]
     , [dir]
     , CHAR(60)+'Dir'+CHAR(62) AS  [Struct_Type]
       FROM  Directory_Contents_Stage 
      WHERE  SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62) 
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
 
IF @Supress = 0
    BEGIN
        Declare @FileName as Varchar(100)
 
        Select @FileName = File_name From Directory_Contents Where Create_time In (
                select max(create_Time)From Directory_Contents
                where Struct_Type = 'FILE' And 
                      SubString(File_Name,1,Len(@DBName)) = @DBName
                Group by Substring(File_name,1,14))
 
        DECLARE @cmdRestore Varchar(max)
        DECLARE @sMovingData Varchar(max)
        DECLARE @sMovingLog VARCHAR(Max)
 
        if @NewPathLogFile <> '' 
           Set @sMovingLog = ', @with = ''move "'+ + @PhysicalLogName + '" to "' + @NewPathLogFile + '"'''    
                    
        if @NewPathDataFile <> '' 
           Set @sMovingData = ', @with = ''move "' + @PhysicalDataName + '" to "' + @NewPathDataFile + '"'''
 
        Set @cmdRestore = 'exec master.dbo.xp_restore_database @database = ''' + @DBName + ''', @filename = ''' +  @FileName + ''',@with=''STATS = 10''' + @sMovingData + @sMovingLog + ',@affinity = 0,@logging = 0'
        Print @cmdRestore
    END

This is very nice - I might find a use for this immediately...

We are developing a complex case management / document tracking system - and have a need for the server engine to be able to find files in a disk folder.

Thanks!

Steve

I might try placing the whole thing in a Try Catch structure to ensure that if a error occurs I get the xp_cmdshell set back to turned off for security reasons.