Home » Questions » Computers [ Ask a new question ]

What is the best way to copy a database?

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?

Asked by: Guest | Views: 42
Total answers/comments: 4
Guest [Entry]

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.
Guest [Entry]

"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)"
Guest [Entry]

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.
Guest [Entry]

"The Publish to Provider functionality has worked great for me. See Scott Gu's Blog Entry.

If you need something really robust look at redgate software's tools here...if you are doing much SQL at all, these are worth the $$."