Restore DB script

backuprestore

I wrote this script for restore DB:

declare
@dir nvarchar(70),
@year nvarchar(4),
@month nvarchar(2),
@day nvarchar(2)

set @year = datepart(year,getdate())

set @month = datepart(month,getdate())
if(len(@month) = 1)
set @month = '0' + @month

set @day = datepart(day,getdate())
if(len(@day) = 1)
set @day = '0' + @day

set @dir = 'F:\BackUp\PreviousDay\Database_backup_' + @year + @month + @day + '0007.bak'

select @dir

RESTORE DATABASE [PreviousDayTables]
FROM DISK = @dir
WITH REPLACE

Now I have DB backups:

Database_backup_201604190007.bak, Database_backup_201604200017.bak, Database_backup_201604210023.bak

I need to change line down below for automatic restoring.
How can I do this?
set @dir = 'F:\BackUp\PreviousDay\Database_backup_' + @year + @month + @day + '0007.bak'

Is there some possibility to do this with '%' or something similar?

Tnx.

Best Answer

Instead of using this script I would attempt to use this method.

the script below assumes the following:

  1. The restored database will have the same name as the backed up database
  2. The restored database will be restored in the same location as the backed up database
  3. The files have the following naming format dbName_YYYYMMDDHHMM.xxx
  4. XP_CMDSHELL is enabled
  5. There are no missing transaction logs that may break the restore chain
  6. File extensions are as follows
    • Full backup - BAK
    • Differential backup - DIF
    • Transaction log backup - TRN

The script below will read through the directory and create the restore script for us. The only two parameters that would need to change are the @dbName and the @backupPath.

 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 = ----INSERT DATABASE NAME HERE
 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 


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