Auto generate database copy with backup and restore

Article
01/08/2013

Vedo spesso la necessità di poter disporre di un database “copia”, per attività di controllo, verifica, reportistica, …

In pratica, risolvere la necessità di costruire al volo e dinamicamente una copia del database, “accanto” a quello operazionale.

Per rendere l’idea:

Possiamo automatizzare tutto tramite script (più in basso in questo post)

Tre sono le variabili che devono essere impostate per il corretto funzionamento:

definire il nome del database che vogliamo copiare
definire il path dove memorizzare il backup del database da copiare
definire il path dove effettuare il restore

Le istruzioni seguenti, elencando le attività in una sorta di workflow, effettuano:

impostazione delle variabili necessarie a definire db, path, nomi file, ecc…
full database backup (compresso e in copy_only)
recupero nomi logici e fisici dei file del db (necessari ad effettuare la restore in maniera dinamica)
restore database

Questo lo script (che può essere inserito, per semplicità d’uso, in un’opportuna procedura):

 USE MASTER
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

/* Procedure Variables */
DECLARE @backupFile nvarchar( 100 );
DECLARE @databaseName nvarchar( 100 );
DECLARE @tSQL nvarchar( 2000 );
DECLARE @NEWdatabaseName nvarchar( 100 );
DECLARE @pathRestore nvarchar(1000);
DECLARE @restoreCommand nvarchar( 2000 );



/* *** SET VARIABLES *** */
SET @databaseName        = 'AdventureWorks2012'; 

SET @backupFile        = 'c:\data\';
SET @pathRestore        = 'c:\data\';
/* *** SET VARIABLES *** */


/* build the new database name. es: myDB_20121220_122543 */
SET @NEWdatabaseName    = 
    (select @databaseName + '_' + 
       convert(char(8), getdate(),112) + '_' + replace(convert(char(8), getdate(),108),':',''))

SET @backupFile = @backupFile + @NEWdatabaseName + '.bck'

print '@NEWdatabaseName: ' + @NEWdatabaseName
print '@backupFile: ' + @backupFile
print ''


/* FULL DATABASE BACKUP - compression, copy_only*/
/* compose the BACKUP statement */
set @tSQL = 
    'BACKUP DATABASE ' + @databaseName + ' TO DISK= ''' + @backupFile + '''
    WITH 
       COMPRESSION,    /* Explicitly enables backup compression */
       FORMAT,        /* FORMAT causes the backup operation to write a new 
                       media header on all media volumes used for the backup operation */
       COPY_ONLY        /* A copy-only backup is created independently 
                       of your regularly scheduled, conventional backups */
    '
/* execute BACKUP */
PRINT ''
PRINT @tsql;
EXECUTE sp_executesql @tsql;

SET @tSQL = 
'
IF EXISTS( SELECT 1
             FROM sys.databases
             WHERE name = ''' + @NEWdatabaseName  + ''' )
    BEGIN
       ALTER DATABASE ' + @NEWdatabaseName + '
       SET SINGLE_USER WITH
        ROLLBACK IMMEDIATE
    END
'
PRINT @tsql;
EXECUTE sp_executesql @tsql;
PRINT ''


/* RETRIEVE LOGICAL AND PHYSICAL INFO FROM BACKUP TO EXECUTE RESTORE DINAMICALLY */
    DECLARE @fileList TABLE
    (
       LogicalName  nvarchar(128),
       PhysicalName nvarchar(260),
       Type   char(1),
       FileGroupName nvarchar(128) null,
       Size   numeric(20, 0),
       MaxSize   numeric(20, 0),
       FileId   int null,
       -- Flags   int null,
       CreateLSN    numeric(25,0) null,
       DropLSN     numeric(25,0) null,
       UniqueFileId  uniqueidentifier null,
       readonlyLSN     numeric(25,0) null,
       readwriteLSN     numeric(25,0) null,
       BackupSizeInBytes bigint null,
       SourceBlkSize  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)
    )

    set @tSQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @backupFile + ''''
    PRINT ''
    PRINT @tsql;
    INSERT @fileList
    EXECUTE sp_executesql @tsql;

-- Build the T-SQL RESTORE statement 
SET @restoreCommand = 'RESTORE DATABASE ' + @NEWdatabaseName + ' FROM DISK = ''' + @backupFile + ''' WITH REPLACE, '; 
    SELECT @restoreCommand = @restoreCommand + CHAR( 13 ) + ' MOVE ''' + LogicalName + ''' TO ''' + 
          @pathRestore + LogicalName + '_NEW' + '.' + 
          RIGHT( PhysicalName , CHARINDEX( '\' , PhysicalName )) + ''','
     FROM @fileList
     WHERE IsPresent = 1;
    SET @restoreCommand = SUBSTRING( @restoreCommand , 1 , LEN( @restoreCommand ) - 1 ); 
    PRINT ''
    PRINT @restoreCommand;

EXEC sp_executesql @restoreCommand;


set @tSQL = 
'
ALTER DATABASE ' + @NEWdatabaseName + '
SET MULTI_USER
'
PRINT @tsql;
EXECUTE sp_executesql @tsql;
GO

E se volessimo effettuare la copia su un’istanza differente?

Le istruzioni si complicano di poco, dovendo:

costruire una procedura, sull’istanza remota, che contenga le istruzioni per effettuare il restore in locale (attenzione: non è possibile chiamare, dal server locale, una RESTORE sul server remoto. Serve necessariamente una procedura già presente sulla seconda istanza)
costruire, in locale, un linked server verso l’istanza remota, ad esempio:

 EXEC sp_addlinkedserver   
   @server=N'SQLRestoreInstance', 
   @srvproduct=N'',
   @provider=N'SQLNCLI', 
   @datasrc=N'(local)\SQL2012_2';

EXEC master.dbo.sp_serveroption 
    @server=N'SQLRestoreInstance', 
    @optname=N'rpc out', 
    @optvalue=N'true'
GO

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'SQLRestoreInstance', 
    @locallogin = N'<local login>', 
    @useself = N'False', 
    @rmtuser = N'<remote user>', 
    @rmtpassword = N'<the password of user on remote instance>'
GO

Una volta creato il linked server, il nostro codice locale, una volta effettuato il backup, dovrà chiamare la procedura remota.

Ad esempio:

 EXEC SQLRestoreInstance.Master.dbo.sp_restoreBackupDB

Enjoy.

Comments

Anonymous
January 08, 2013
Ma è proprio quello che mi serve non potendo usare AlwaysOn (grazie a Magic che non funziona su SQL2012...)

Share via

Auto generate database copy with backup and restore

Comments

Additional resources