0

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 ?

  • 2
    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
1

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
0
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.