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
|
Categories: Dynamic SQL Restore Script
msufian
