restoration automation

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • bharadwaj
    New Member
    • Jun 2007
    • 22

    #1

    restoration automation

    Hi,
    I am trying to do restoration for n databases using automated script.The script is given below. it is not giving errors while job execution ,but restoration is not happening.(no record is in msdb.restorehis tory).
    any advice from you. I have doubt on my dynamic sql part.

    CREATE procedure test_restore
    as

    DECLARE @backup_set_id_ Full INT
    --DECLARE @Full_Backup_Se t_Date datetime
    DECLARE @backup_set_id_ Diff INT
    --DECLARE @backup_set_id_ end INT
    --DECLARE @Last_Backup_Se t_Date datetime
    DECLARE @databaseName sysname
    DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
    DECLARE @RowCnt INT -- stores @@ROWCOUNT
    DECLARE @filename VARCHAR(200)

    SET @fileName = 'E:\test\'+ @dbname + '.bak'
    SELECT name
    INTO #WhichDatabase
    FROM master.dbo.sysd atabases
    WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
    ORDER BY name
    -- Get the database to be backed up
    SELECT TOP 1 @DBName = name
    FROM #WhichDatabase

    SET @RowCnt = @@ROWCOUNT

    -- Iterate throught the temp table until no more databases need to be backed up
    WHILE @RowCnt <> 0
    BEGIN

    -- Get the ID of the most recent full backup for the database
    SELECT @backup_set_id_ Full = MAX(backup_set_ id)
    FROM msdb.dbo.backup set
    WHERE database_name = @databaseName
    AND type = 'D'

    -- If no DIFF backup exists, then set the DIFF ID to the full backup ID
    IF @backup_set_id_ Diff IS NULL
    BEGIN
    SET @backup_set_id_ Diff = @backup_set_id_ Full
    END

    SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '+ @filename
    + mf.physical_dev ice_name + ''' WITH ' + 'FILE = ' + convert(varchar (10), b.position) + ', RECOVERY'
    as CommandSet
    FROM msdb.dbo.backup set b,
    msdb.dbo.backup mediafamily mf
    WHERE b.media_set_id = mf.media_set_id
    AND b.database_name = @databaseName
    AND b.backup_set_id = @backup_set_id_ Full
    --ORDER BY backup_set_id
    DELETE
    FROM #WhichDatabase
    WHERE name = @DBName

    -- Get the database to be backed up
    SELECT TOP 1 @DBName = name
    FROM #WhichDatabase

    SET @RowCnt = @@ROWCOUNT

    -- Let the system rest for 5 seconds before starting on the next backup
    WAITFOR DELAY '00:00:05'

    END

    DROP TABLE #WhichDatabase
Working...