Archive

Archive for June, 2009

BlogCatalog

June 30, 2009 Leave a comment

Technology Blogs - BlogCatalog Blog Directory

Categories: Uncategorized

Dynamic SQL to Restore Database

June 8, 2009 Leave a comment
We have a testing envornment with more then 35 databases.Every week we need to restore all the database
with recent backup of prodcution databases.
So we came up with dynamic sql solution.We keep all the backup file at one location and script checks the 
database name and overrite the database using respective backup file.
Once the restore is done script truncate the log file.
Code will check the existing database file location and will keep the fill to the same location on restoring the 
database.

Version

Microsoft

Feb 9 2007 22

Microsoft Corporation Enterprise Edition
SQL Server 2005 - 9.00.3042.00 (Intel X86) :47:07 Copyright (c) 1988-2005 
on
Windows NT 5.2 (Build 3790: Service Pack 2) 
USE [master]
GO
--****** Object:  StoredProcedure [dbo].[Usp_dba_RestoreDatabase]   
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Usp_dba_RestoreDatabase]
AS
BEGIN
 
SET NOCOUNT ON
DECLARE @DBName               varchar(255)
DECLARE @vBackupFileName      varchar(255) 
DECLARE @vNewPath             varchar(1000) 
DECLARE @vFileName            varchar(100) 
DECLARE @BackupFilePath       varchar(1000) 
DECLARE @SQL                  varchar(max)
DECLARE @dated                varchar(50) 
DECLARE @vCount               int 
DECLARE @vNumDBs              int 
 
DECLARE @LogDFileName         varchar(max)
DECLARE @LogDFilePath         nvarchar(max)
 
DECLARE @LogLFileName         varchar(max)
DECLARE @LogLFilePath         nvarchar(max)
 
DECLARE @vErrorString         varchar(1000) 
--***********--
--Error log variables
DECLARE @cmd varchar(500), @var varchar(500),@ServerName varchar(50),
            @ErrorMessage varchar(100),@filename varchar(100)
---------------
 
DECLARE @vDBDaFileList Table 
( 
DBDaFile Varchar(256) ,DataFilePath varchar(max)
)
 
DECLARE @vDBDLList Table 
( 
DBLgFile Varchar(256) , LogFilePath varchar(max)
)
 
DECLARE @vDBList Table 
( 
DBID INT NOT NULL IDENTITY(1, 1),DBakName Varchar(256) 
)
DECLARE @vDBHList Table
(
DBID INT NOT NULL       IDENTITY(1, 1),
BackupName              nvarchar(128),
BackupDescription       nvarchar(255),
BackupType              smallint,
ExpirationDate          datetime,
Compressed              tinyint,
Position                smallint,
DeviceType              tinyint,
UserName                nvarchar(128),
ServerName              nvarchar(128),
DatabaseName            nvarchar(128),
DatabaseVersion         int,
DatabaseCreationDate    datetime,
BackupSize              numeric(20,0),
FirstLSN                numeric(25,0),
LastLSN                 numeric(25,0),
CheckpointLSN           numeric(25,0),
DatabaseBackupLSN       numeric(25,0),
BackupStartDate         datetime,
BackupFinishDate        datetime,
SortOrder               smallint,
CodePage                smallint,
UnicodeLocaleId         int,
UnicodeComparisonStyle  int,
CompatibilityLevel      tinyint,
SoftwareVendorId        int,
SoftwareVersionMajor    int,
SoftwareVersionMinor    int,
SoftwareVersionBuild    int,
MachineName             nvarchar(128),
Flags                   int,
BindingID               uniqueidentifier,
RecoveryForkID          uniqueidentifier,
Collation               nvarchar(128),
FamilyGUID              uniqueidentifier,
HasBulkLoggedData       bit,
IsSnapshot              bit,
IsReadOnly              bit,
IsSingleUser            bit,
HasBackupChecksums      bit,
IsDamaged               bit,
BeginsLogChain          bit,
HasIncompleteMetaData   bit,
IsForceOffline          bit,
IsCopyOnly              bit,
FirstRecoveryForkID     uniqueidentifier,
ForkPointLSN            numeric(25,0) NULL,
RecoveryModel           nvarchar(60),
DifferentialBaseLSN     numeric(25,0) NULL,
DifferentialBaseGUID    uniqueidentifier,
BackupTypeDescription   nvarchar(60),
BackupSetGUID           uniqueidentifier NULL
)
 
-----Insert backupfile name from the backup file location-----
 
Insert Into @vDBList 
exec xp_cmdshell 'dir D:\DataBackup\*.bak /B /O'
--------------------------------------------------------------
 
 
Set @vNumDBs = @@RowCount 
Set @vCount = 1 
 
While @vCount <= @vNumDBs 
 Begin 
      DELETE FROM @vDBList where DBakName is NULL
      SELECT @vBackupFileName = DBakName From @vDBList Where DBID = @vCount  and DBakName is not NULL
      SET @BackupFilePath=''''+'D:\DataBackup\'+@vBackupFileName+''''
 
      SET @SQL='RESTORE HEADERONLY FROM DISK= '+ @BackupFilePath
      --Print @SQL
      --SELECT * FROM sys.master_files
      INSERT INTO @vDBHList
      exec (@SQL)
      DELETE FROM @vDBHList WHERE DatabaseName is NULL or DatabaseName=''
      DELETE FROM @vDBHList WHERE DatabaseName in ('master','model','msdb')
      DELETE FROM @vDBHList WHERE DatabaseName not in (Select name from sys.databases)
     
      --SELECT DBID,databasename FROM @vDBHList --where databasename is not null or databasename=''
      SET @DBName=''
      SELECT @DBName=databasename from @vDBHList where DBID=@vCount ---and databasename is not null
       IF    @DBName <>''
            BEGIN
             SET @SQL='SELECT name,physical_name FROM sys.master_files where 
                       database_id=db_id('''+@DBName+''') and type_desc=''ROWS'''
             INSERT into @vDBDaFileList exec (@SQL)
                  select @LogDFileName=DBDaFile ,@LogDFilePath=DataFilePath from @vDBDaFileList
                                   
                  SET @SQL='SELECT name,physical_name FROM sys.master_files 
                            where database_id=db_id('''+@DBName+''') and type_desc=''LOG'''
                INSERT into @vDBDLList exec (@SQL)
                  select @LogLFileName=DBLgFile , @LogLFilePath=LogFilePath from @vDBDLList
           
      SET @SQL='RESTORE DATABASE ['+@DBName +
            '] FROM  DISK ='+ @BackupFilePath+
            ' WITH  FILE = 1,  '+
      'MOVE '+''''+@LogDFileName+''''+ 'TO '+ '''' + @LogDFilePath +''''+',  '+
      'MOVE '+''''+@LogLFileName+''''+ 'TO '+ '''' + @LogLFilePath +''''+',  '+
      'NOUNLOAD,  REPLACE,  STATS = 10'
      Print '----------Restore started for Database['+@DBName+']---------------'
      --Print  @SQL
      exec (@SQL)
      Print '----------Restore Completed for Database['+@DBName+']--------------'
      Print '                                               '
      Print'-----------Truncate Log for Database['+@DBName+'] started---------------'
      SET @SQL='BACKUP LOG '+@DBNAME+ ' WITH TRUNCATE_ONLY'
      exec (@SQL)
     
      SET @SQL='USE ['+ @DBName +'] ' +'DBCC SHRINKFILE(2,1) with mesg'      
  Print '----------Truncate Log for Database['+@DBName+'] completed--------------'
      Print '     '
      END
 
      SET @vCount = @vCount + 1 
     
     
 END
 
SET NOCOUNT OFF
 
END