用于“复制"的 SQL 脚本数据库 [英] SQL script to "copy" a database
本文介绍了用于“复制"的 SQL 脚本数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想编写一个 SQL 脚本来复制同一台服务器上的数据库.我可以进行备份/恢复,但我认为以某种方式复制"可能会更快.有谁知道这是否可能?有没有办法编写一个脚本,它只是分离,复制 HD 上的文件,然后重新附加两个副本?
I want to write a SQL script that will copy a database on the same server. I could do a backup/restore, but I think it might be faster to just "copy" somehow. Does anyone know if this is possible? Is there a way to write a script that will just detach, copy the file on the HD, and then reattach both copies?
推荐答案
@Tony the Lion:嗨 - 我在使用你的脚本时遇到了一些问题,所以我想出了你的脚本和这篇文章的混合体:链接
@Tony the Lion: Hi - I experienced some problems using your script, so I came up with a hybrid of your script and this post: link
USE master;
GO
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @SourceDatabaseName varchar(200)
DECLARE @SourceDatabaseLogicalName varchar(200)
DECLARE @SourceDatabaseLogicalNameForLog varchar(200)
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
DECLARE @LogFile varchar(2000)
DECLARE @BackupFile varchar(2000)
DECLARE @TargetDatabaseName varchar(200)
DECLARE @TargetDatbaseFolder varchar(2000)
-- ****************************************************************
SET @SourceDatabaseName = '[Source.DB]' -- Name of the source database
SET @SourceDatabaseLogicalName = 'Source_DB' -- Logical name of the DB ( check DB properties / Files tab )
SET @SourceDatabaseLogicalNameForLog = 'Source_DB_log' -- Logical name of the DB ( check DB properties / Files tab )
SET @BackupFile = 'C:\Temp\backup.dat' -- FileName of the backup file
SET @TargetDatabaseName = 'TargetDBName' -- Name of the target database
SET @TargetDatbaseFolder = 'C:\Temp\'
-- ****************************************************************
SET @DataFile = @TargetDatbaseFolder + @TargetDatabaseName + '.mdf';
SET @LogFile = @TargetDatbaseFolder + @TargetDatabaseName + '.ldf';
-- Backup the @SourceDatabase to @BackupFile location
IF @SourceDatabaseName IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @SourceDatabaseName + ' TO DISK = ' + QUOTENAME(@BackupFile,'''')
PRINT 'Executing query : ' + @query;
EXEC (@query)
END
PRINT 'OK!';
-- Drop @TargetDatabaseName if exists
IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TargetDatabaseName)
BEGIN
SET @query = 'DROP DATABASE ' + @TargetDatabaseName
PRINT 'Executing query : ' + @query;
EXEC (@query)
END
PRINT 'OK!'
-- Restore database from @BackupFile into @DataFile and @LogFile
SET @query = 'RESTORE DATABASE ' + @TargetDatabaseName + ' FROM DISK = ' + QUOTENAME(@BackupFile,'''')
SET @query = @query + ' WITH MOVE ' + QUOTENAME(@SourceDatabaseLogicalName,'''') + ' TO ' + QUOTENAME(@DataFile ,'''')
SET @query = @query + ' , MOVE ' + QUOTENAME(@SourceDatabaseLogicalNameForLog,'''') + ' TO ' + QUOTENAME(@LogFile,'''')
PRINT 'Executing query : ' + @query
EXEC (@query)
PRINT 'OK!'
这篇关于用于“复制"的 SQL 脚本数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文