转自:http://blog.csdn.net/tianshijianbing1989/article/details/41013885
SQL Server 2008如何copy database:
方法一:使用Microsoft SQL Server Management Studio中的Copy Database Wizard来辅助完成。(很容易失败)
方法二:采取Backup/Restore的方式(推荐)
a.备份数据库到磁盘
- BACKUP DATABASE Source_Database
- TO DISK = 'D:\Backup\Source_Database.bak'
- WITH FORMAT;
b.查看备份文件的逻辑文件
- RESTORE FILELISTONLY
- FROM DISK = 'D:\Backup\Source_Database.bak';
c.创建目标数据库
- CREATE DATABASE Target_Database;
d.从磁盘复制数据库
- /*
- 利用bak恢复数据库,强制还原(REPLACE)
- STATS = 10 每完成10%显示一条记录
- Source_Database和Source_Database_log是上面D:\Backup\Source_Database.bak里的逻辑文件
- */
- RESTORE DATABASE Target_Database
- FROM DISK='D:\Backup\Source_Database.bak'
- WITH
- MOVE 'Source_Database' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Target_Database.mdf',
- MOVE 'Source_Database_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Target_Database_log.ldf',
- STATS = 10,REPLACE
附:实例:
USE [database_name]
GO
/****** Object: StoredProcedure [dbo].[CopyDatabase] ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** 对象: 存储过程 dbo.CopyDatabase ******/
/****** 对象: 存储过程 dbo.CopyDatabase ******/
CREATE PROCEDURE [dbo].[CopyDatabase]
(@SourceDbName varchar(50),
@NewDbName varchar(50))
AS
declare @tempDevice varchar(50)
declare @tempBkupFile varchar(50)
declare @tempBkPath varchar(200)
declare @dataFilePath varchar(200)
declare @logFilePath varchar(200)
declare @sdataFile varchar(100)
declare @slogFile varchar(100)
SET @sdataFile=@SourceDbName+"_data" /*查看备份文件的逻辑文件RESTORE FILELISTONLY FROM DISK =tempBkPath+tempBkupFile'; 特别重要,此处就按查看结果设置,下面slogFile相同的道理*/
SET @slogFile=@SourceDbName+"_log"
SET @dataFilePath="D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\"+@NewDbName+".mdf"
SET @logFilePath="D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\"+@NewDbName+".ldf"
SET @tempBkupFile=@NewDbName+".dat"
SET @tempBkPath="D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP\"+@tempBkupFile
SET @tempDevice=@NewDbName+"back"
EXEC sp_addumpdevice "disk", @tempDevice,@tempBkPath
BACKUP DATABASE @SourceDbName to @tempDevice
RESTORE DATABASE @NewDbName FROM @tempDevice WITH RECOVERY,
MOVE @sdataFile TO @dataFilePath,
MOVE @slogFile TO @logFilePath
EXEC sp_dropdevice @tempDevice,"DELFILE"
GO