Archive for the ‘Dynamic SQL Restore Script’ Category
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