sql-server - without - sql select no duplicates column




What is the best way to copy a database? (4)

I always create a new empty database, after that backup and restore of the existing database into it, but is this really the best way? As it seems very error prone and over complicated for me.


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.


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)





What you're looking for is SQL Server Express with the portable .mdf files. To get around the copying limitation you need to make sure that the software in question doesn't keep connections open (i.e. create a disconnected data access layer).


  • Users can copy the database while it is open without risking corruption.

You can't do that with any database file with multiple users and/or processes modifying it.





sql-server-2008