复制数据库的最佳方法是什么?[英] What is the best way to copy a database?

问题描述

When I want to make a copy of a database, I always create a new empty database, and then restore a backup of the existing database into it. However, I'm wondering if this is really the least error-prone, least complicated, and most efficient way to do this?

推荐答案

Backup and Restore is the most straight-forward way I know. You have to be careful between servers as security credentials don't come with the restored database.

其他推荐答案

It is possible to skip the step of creating the empty database. You can create the new database as part of the restore process.

This is actually the easiest and best way I know of to clone a database. You can eliminate errors by scripting the backup and restore process rather than running it through the SQL Server Management Studio

There are two other options you could explore:

1. Detach the database, copy the .mdf file and re-attach.
2. Use SQL Server Integration Services (SSIS) to copy all the objects over

I suggest sticking with backup and restore and automating if necessary.

其他推荐答案

Here's a dynamic sql script I've used in the past. It can be further modified but it will give you the basics. I prefer scripting it to avoid the mistakes you can make using the Management Studio:

Declare @OldDB varchar(100)
Declare @NewDB varchar(100)
Declare @vchBackupPath varchar(255)
Declare @query varchar(8000)


/*Test code to implement 
Select @OldDB = 'Pubs'
Select @NewDB = 'Pubs2'
Select @vchBackupPath = '\\dbserver\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\pubs.bak'
*/

SET NOCOUNT ON;

Select @query = 'Create Database ' + @NewDB
exec(@query)

Select @query = '
Declare @vBAKPath varchar(256)
declare @oldMDFName varchar(100)
declare @oldLDFName varchar(100)
declare @newMDFPath varchar(100)
declare @newLDFPath varchar(100)
declare @restQuery varchar(800)

select @vBAKPath = ''' + @vchBackupPath + '''
select @oldLDFName = name from ' + @OldDB +'.dbo.sysfiles where filename like ''%.ldf%''
select @oldMDFName = name from  ' + @OldDB +'.dbo.sysfiles where filename like ''%.mdf%''
select @newMDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''ROWS''
select @newLDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''LOG''

select @restQuery = ''RESTORE DATABASE ' + @NewDB + 
' FROM DISK = N'' + '''''''' + @vBAKpath + '''''''' + 
'' WITH MOVE N'' + '''''''' + @oldMDFName + '''''''' +  
'' TO N'' + '''''''' + @newMDFPath + '''''''' +  
'', MOVE N'' + '''''''' + @oldLDFName + '''''''' +  
'' TO N'' + '''''''' + @newLDFPath + '''''''' +  
'', NOUNLOAD, REPLACE, STATS = 10''

exec(@restQuery)
--print @restQuery'


exec(@query)