Обновление моей базы данных UAT к недавней копии живых (SQL Server MS)

USE YOUR DATABASE
go
DECLARE @BackupFile NVARCHAR(250)
,   @DatabaseName NVARCHAR(50)
,   @BackupPath NVARCHAR(150)
,   @BackupDescription NVARCHAR(150)
,   @RestoreTarget NVARCHAR(250)
,   @RestoreName NVARCHAR(50)
,   @RestoreData NVARCHAR(60)
,   @RestoreLog NVARCHAR(60)
,   @RestoreDataLoc NVARCHAR(300)
,   @RestoreLogLoc NVARCHAR(300)
,   @DVar NVARCHAR(14)
,   @SQLVer INT
,   @RunCmd VARCHAR(1000)
DECLARE @Source VARCHAR(1000)
,   @NewSource VARCHAR(1000)
,   @Found INT
,   @Find VARCHAR(5)

SELECT  @DatabaseName = DB_NAME()
SELECT  @RestoreName = 'DATABSE_Test'
SET @BackupPath = 'C:\FILE NAME\'
SET @Find = '%\%'
SET @NewSource = ''


SELECT  @DVar = CAST(DATEPART(yyyy, GETDATE()) AS CHAR(4))
SELECT  @DVar = @DVar + (CASE WHEN DATEPART(mm, GETDATE()) < 10 THEN '0' + 
CAST(DATEPART(mm, GETDATE()) AS CHAR(1))
                          ELSE CAST(DATEPART(mm, GETDATE()) AS CHAR(2))
                     END)
SELECT  @DVar = @DVar + (CASE WHEN DATEPART(dd, GETDATE()) < 10 THEN '0' + 
CAST(DATEPART(dd, GETDATE()) AS CHAR(1))
                          ELSE CAST(DATEPART(dd, GETDATE()) AS CHAR(2))
                     END)
SELECT  @DVar = @DVar + (CASE WHEN DATEPART(hh, GETDATE()) < 10 THEN '0' + 
CAST(DATEPART(hh, GETDATE()) AS CHAR(1))
                          ELSE CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
                     END)
SELECT  @DVar = @DVar + (CASE WHEN DATEPART(mi, GETDATE()) < 10 THEN '0' + 
CAST(DATEPART(mi, GETDATE()) AS CHAR(1))
                          ELSE CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
                     END)

-- Database Variable Population
SELECT  @BackupFile = CAST(('' + @BackupPath + '' + @DatabaseName + '_' + @DVar + 
'.bak') AS NVARCHAR(500))
SELECT  @BackupDescription = @DatabaseName + ' backup on ' + CAST(GETDATE() AS 
CHAR(26))
SELECT  @RestoreData = name
,       @Source = filename
FROM    dbo.sysfiles
WHERE   (SUBSTRING(name, LEN(name) - 2, LEN(name)) = 'ata')
    OR (name LIKE '%dat%')
SET @NewSource = ''
SELECT  @Found = PATINDEX(@Find, @Source)
WHILE (@Found > 0) 
    BEGIN
        SELECT  @NewSource = @NewSource + SUBSTRING(@Source, 0, @Found) + '\'
        SELECT  @Source = SUBSTRING(@Source, (@Found + 1), LEN(@Source))
        SELECT  @Found = PATINDEX(@Find, @Source)
    END
SELECT  @RestoreDataLoc = @NewSource + @RestoreName + '_DATA.MDF'

SELECT  @RestoreLog = name
,       @Source = filename
FROM    dbo.sysfiles
WHERE   SUBSTRING(name, LEN(name) - 2, LEN(name)) = 'log'
SET @NewSource = ''
SELECT  @Found = PATINDEX(@Find, @Source)
WHILE (@Found > 0) 
    BEGIN
        SELECT  @NewSource = @NewSource + SUBSTRING(@Source, 0, @Found) + '\'
        SELECT  @Source = SUBSTRING(@Source, (@Found + 1), LEN(@Source))
        SELECT  @Found = PATINDEX(@Find, @Source)
    END
SELECT  @RestoreLogLoc = @NewSource + @RestoreName + '_LOG.LDF'


/**********************************************************
    Backup Live Database
**********************************************************/
BACKUP DATABASE @DatabaseName 
TO DISK = @BackupFile
WITH NAME = @DatabaseName, DESCRIPTION = @BackupDescription, NOSKIP , NOFORMAT, INIT 
, STATS = 10, COMPRESSION


/**********************************************************
    Kill any active session on TEST database
**********************************************************/
USE master 

DECLARE @execSql NVARCHAR(1000)
SET @execSql = ''

SELECT  @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' '
FROM    master.dbo.sysprocesses
WHERE   DB_NAME(dbid) = @RestoreName
        AND DBID <> 0
        AND spid <> @@spid

EXEC (@execSql)


/**********************************************************
    Restore TEST database
**********************************************************/ 
RESTORE DATABASE @RestoreName
FROM DISK = @BackupFile
WITH REPLACE, MOVE @RestoreData TO @RestoreDataLoc, MOVE @RestoreLog TO 
@RestoreLogLoc , STATS = 10


/**********************************************************
    Clean Up
**********************************************************/
SET @RunCmd = 'del ' + @BackupFile
EXEC master.dbo.xp_cmdshell 
    @RunCmd
,   NO_OUTPUT
GO


/**********************************************************
    Enable Service Broker
**********************************************************/
USE [master]
GO
ALTER DATABASE [YOUR DATABSE] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO


/**********************************************************
    Enable Snapshot Isolation
**********************************************************/
ALTER DATABASE [YOUR DATABASE] SET ALLOW_SNAPSHOT_ISOLATION ON ;
ALTER DATABASE [YOUR DATABSE] SET READ_COMMITTED_SNAPSHOT ON ;
GO


/**********************************************************
    Set TEST database configs
**********************************************************/
USE YOUR DATABASE
go

UPDATE  Company
SET     Data_File = DB_NAME()
,       NTAuthDSNName = 'YOUR DATABASENT'
,       DontLogin = 0
,       COMPANY_NAME = 'TEST TEST ' + COMPANY_NAME
GO

UPDATE dbo.DistributedDatabases
SET     DatabaseName = DB_NAME()
GO

Вы можете добавить сюда больше обновлений в зависимости от того, какие еще поля вам нужно обновить от живого до тестового