主页 / server / 问题 / 389654
Accepted
NimChimpsky
Asked: 2012-05-17 05:50:40 +0800 CST

将我的 UAT 数据库更新为最近的实时副本 (MS SQL Server)

  • 772

我有一个带有两个数据库的 sql server 实例：

myDB_LIVE

我的数据库测试

两者都指向单独的 mdf 和 ldf 文件，并且每晚都进行备份。我想复制一份 LIVE 并将其放入 TEST。我什至想把它作为一份常规工作，最好的方法是什么？

我可以从实时备份进行恢复，但担心我会覆盖现有的实时数据库，因为它们位于同一服务器实例上？

sql-server-2008

2 个回答

  1. Best Answer

    我会将以下设置为存储过程，然后创建一个每晚运行它的作业：

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

    您可以在此处添加更多更新，具体取决于您需要从实时更新到测试的其他字段

    • 0

相关问题