T-SQL : Generate UpToDate TestDB - Automatic TestDB creation

[Former Member]

Hi All,

Here is a TSQL query example to generate a test database from a production database. It can be planned through SQL Maintenance plan to have TestDB updated from Productive DB from a regular basis.

Update SQL Variables with desired values :

@PRODdatabaseName :  Source DB Name

@TESTdatabaseName : Target DB Name (will be deleted before restore if DB already exists)

@SQLDATAPath : Path for storing mdb and mdf files for the targetDB.

@BackupPath : Path for storing the backup file. (will be deleted and regenerated on each execution)

Add the query into SQL maintenance plan and plan recuring execution.

Code Snippet

-- =======================================================================================
-- Version :     <1.03>
-- Author:       <Emmanuel ALBERT, ealbert@self-informatique.fr>
-- Description:  <Create Backup from specified DB and restore as test DB>
-- Create date:  <18 Fev 2014,,>
-- Update Date : <19 Fev 2014, overwriting existing backup set,             
--                Switch TestDB to recovery mode=Simple and clean logs. >
-- Update Date : <13 Mars 2014, Closing opening connection if existings on testDB before Restore,,>
--
-- =======================================================================================use [master]
go

declare @PRODdatabaseName nvarchar (128)
set @PRODdatabaseName= 'SBODemoFr';
declare @TESTdatabaseName nvarchar (128)
set @TESTdatabaseName= 'SBODemoFr_Test_UpToDate';

Declare @SQLDATAPath as nvarchar( 128);
  Set@SQLDATAPath = 'd:\temp\'

declare @BackupPath nvarchar (128);
   set @BackupPath ='d:\temp\' + @PRODdatabaseName + '.bak' ;

DECLARE @SQL_SCRIPT VARCHAR (MAX);
Declare @TSQL_DROP_Template VARCHAR (MAX)
Set @TSQL_DROP_Template= 'DROP DATABASE {DBNAME}';--Testing if SourceDB exists before starting :
  IFEXISTS (SELECT name FROM sys.databases WHERE name = @PRODdatabaseName)
BEGIN
Print 'Staring Prod DB Backup - (WITH FORMAT, INIT, COMPRESSION)'
  BACKUP DATABASE  @PRODdatabaseName  --BAckup BDPROD
  TO DISK = @BAckupPath WITH INIT
    
Print 'Test TargetDB exists and drop if true'
  -- Test TargetDB exists and drop if true:
  IF  EXISTS ( SELECT name FROM sys. databases WHERE name = @TESTdatabaseName)
      Begin
      Print 'Target DB Exists : '
      --Kill Opened Connections :
      Print 'Trying to Kill Open Connection on TargetDB'
                         DECLARE @dbid tinyint
                         DECLARE @spid smallint
                         DECLARE @exec_str varchar (10)
                         -- Get the ID of the Database you wish to kill the connections of
                         SET @dbid = DB_ID( @TESTdatabaseName)                         -- Set a var to the first process ID connecting to that database
                         SET @spid = (SELECT MIN( spid) FROM master..sysprocesses WHERE dbid = @dbid)                         WHILE @spid IS NOT NULL
                         BEGIN
                         IF @spid <> @@SPID -- Make sure you don't drop your own connection
                         BEGIN
                         SET @exec_str = 'KILL '+ LTRIM(STR (@spid))
                         Print 'Killing Connection ' + LTRIM(STR (@spid))
                         EXEC(@exec_str ) -- Kill the connection
                         END
                         --Get next SPID
                         SET @spid = (SELECT MIN( spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
                         END
                   SET @SQL_SCRIPT = REPLACE( @TSQL_DROP_Template, '{DBNAME}', @TESTdatabaseName )
            EXECUTE (@SQL_SCRIPT ) --Suppression Base Test si existante
            Print 'Old TargetDB deleted'
      End
    
  Declare @TSQL_REstore_FileList_Template VArchar(MAX )
  SET @TSQL_REstore_FileList_Template ='RESTORE FILELISTONLY FROM DISK=''{BackupPath}'' '
  Declare @TSQL_Restore_Template VARCHAR(MAX )
  Set @TSQL_Restore_Template ='RESTORE DATABASE {DBNAME} FROM DISK=''{BackupPath}'' WITH REPLACE,RECOVERY, MOVE ''{DataVolNAme}'' TO ''{TargetDataVolNAme}'', MOVE ''{LogName}'' TO ''{TargetLogNAme}''';

--Manage mdf and ldf filenames..
Print 'Manage mdf and ldf filenames'
DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128 ) NOT NULL,
      PhysicalName nvarchar(260 ) NOT NULL,
      Type char (1) NOT NULL,
      FileGroupName nvarchar(120 ) NULL,
      Size numeric(20 , 0) NOT NULL,
      MaxSize numeric(20 , 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25 ,0) NULL,
      DropLSN numeric(25 ,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25 ,0) NULL ,
      ReadWriteLSN numeric(25 ,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25 ,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32 ) NULL
);

declare @RestoreStatement nvarchar (max), @BackupFile nvarchar( max);
set @BackupFile = @BackupPath

   SET @RestoreStatement =N'RESTORE FILELISTONLY
      FROM DISK=N''' + @BackupFile + ''''

INSERT INTO @FileList
      EXEC(@RestoreStatement );

declare @logical_data nvarchar (max), @logical_log nvarchar( max);
set @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
set @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)

--Running restore with new filenames
Print 'Starting TargetDB restore'
       SET @SQL_SCRIPT = REPLACE( @TSQL_Restore_Template, '{DBNAME}', @TESTdatabaseName)
       SET @SQL_SCRIPT = REPLACE( @SQL_SCRIPT, '{BackupPath}', @BackupPath)
       SET @SQL_SCRIPT = REPLACE( @SQL_SCRIPT, '{DataVolNAme}', @logical_data)
       Declare @DynPath as nvarchar( 128);
       set  @DynPAth= @SQLDATAPath + @TESTdatabaseName + '.mdf'

       SET @SQL_SCRIPT = REPLACE( @SQL_SCRIPT, '{TargetDataVolNAme}', @DynPAth)
       SET @SQL_SCRIPT = REPLACE( @SQL_SCRIPT, '{LogName}', @logical_log )
       set  @DynPAth= @SQLDATAPath +  @TESTdatabaseName +'.ldf'
       SET @SQL_SCRIPT = REPLACE( @SQL_SCRIPT, '{TargetLogNAme}', @DynPAth)
       EXECUTE (@SQL_SCRIPT )
       Print 'TargetDB restored' ;
 
   --Switch to recovery mode=simple and clean ldf logs
         exec( 'alter database [' + @TESTdatabaseName + '] set recovery Simple')
         exec( '
            use [' + @TESTdatabaseName + '];' +'
      
            declare @logfileName nvarchar(128);
            set @logfileName = (
                  select top 1 [name] from sys.database_files where [type] = 1
            );
            dbcc shrinkfile(@logfileName,1);
            ' )  END

Feel free to comment,

Regards;

Emmanuel