我有一个带有两个数据库的 SQL 服务器实例:
myDB_LIVE
myDB_测试
两者都指向单独的 mdf 和 ldf 文件,并且每晚都会备份。我想复制一份 LIVE 并将其放入 TEST 中。我甚至想把这当作一份常规工作,最好的方法是什么?
我可以从实时备份中进行恢复,但担心我会覆盖现有的实时数据库,因为它们位于同一个服务器实例上?
答案1
我将以下内容设置为存储过程,然后创建一个作业来每晚运行它:
----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
答案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
您可以在此处添加更多更新,具体取决于您需要从实时更新到测试的其他字段