[RESOLVED] SQL Server 2005 Auto Restore

[GaryMazzone]

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

[szlamany]

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...

[kevchadders]

(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

[GaryMazzone]

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.

[GaryMazzone]

Here is what I came up with so far:

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

[GaryMazzone]

Reworked with out cursor

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

[szlamany]

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!

[GaryMazzone]

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.