Updating my UAT database to recent copy of live (MS SQL Server)

Question

I have one sql server instance with two databases :

myDB_LIVE

myDB_TEST

both point to separate mdf and ldf files and are backed up each night. I want to take a copy of LIVE and put it in TEST. I would even like to do this as a regular job, what is the best way ?

I can do a restore from a live back-up but was concerned I would overwrite the existing live database as they are on same server instance ?

Please check your open source zealotry at the door. Connotations like putting a "$" in MS are unprofessional. — Chris S, May 16 '12 at 14:26

score 1 · Accepted Answer · answered May 16 '12 at 14:13

I would set the following up as a Stored Procedure and then create a job to run it each night:

----Make Database to single user Mode
ALTER DATABASE myDB_TEST
SET SINGLE_USER WITH
 ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE myDB_TEST
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
 mode.
 If error occurs please execute following command it will convert
 database in multi user.*/
ALTER DATABASE myDB_TEST SET MULTI_USER
 GO

score 0 · Answer 2 · answered Dec 04 '18 at 04:10

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

You can add more updates here depending on what other fields you need updated from live to test

Updating my UAT database to recent copy of live (MS SQL Server)

2 Answers2