execute automatically restore script

Hello,

How can I modify this script for executing restore database :

IF (@lastDifferentialBackup IS NOT NULL)
BEGIN
   
    PRINT 'RESTORE DATABASE ' + @DBName
    PRINT 'FROM DISK=''' + @lastDifferentialBackupPath + ''''


    IF (@lastDifferentialBackup = (SELECT MAX(id) FROM #MSDBBackupHistory))
        PRINT 'WITH RECOVERY'
    ELSE PRINT 'WITH NORECOVERY'

    PRINT 'GO'
    PRINT ''
END

Thanks
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
please consider to try this one from
http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @dbName = 'Customer' 
SET @backupPath = 'D:\SQLBackups\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' 
PRINT @cmd 

-- 4 - Find latest diff backup 
SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.DIF'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY' 
   PRINT @cmd 
   SET @lastFullBackup = @lastDiffBackup 
END 

-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR  
   SELECT backupFile  
   FROM @fileList 
   WHERE backupFile LIKE '%.TRN'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

OPEN backupFiles  

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @backupFile + ''' WITH NORECOVERY' 
   PRINT @cmd 
   FETCH NEXT FROM backupFiles INTO @backupFile  
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  

-- 6 - put database in a useable state 
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY' 
PRINT @cmd 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eugene ZCommented:
just if you wish to
exec
use after

print  @cmd

Execute (@cmd)

http://msdn.microsoft.com/en-us/library/ms188332(v=sql.105).aspx

same for your code, try exec after code concatenation

for example:

declare @sql varchar(8000)

IF (@lastDifferentialBackup IS NOT NULL)
BEGIN
   
    PRINT 'RESTORE DATABASE ' + @DBName
    PRINT 'FROM DISK=''' + @lastDifferentialBackupPath + ''''
set @sql = 'RESTORE DATABASE ' + @DBName +' FROM DISK=''' + @lastDifferentialBackupPath + ''''


    IF (@lastDifferentialBackup = (SELECT MAX(id) FROM #MSDBBackupHistory))
         Begin
            PRINT 'WITH RECOVERY'
              set @sql =@sql + ' WITH RECOVERY'
        end
    ELSE 
          Begin 
           PRINT 'WITH NORECOVERY'
set @sql =@sql + ' WITH NORECOVERY'
         End
    PRINT 'GO'
set @sql =@sql  + CHAR(13)+ CHAR(10) + ' GO'
    
print  @sql

Exec (@sql)
END

Open in new window

0
bibi92Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.