How do you copy a data base?


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 = "\dbserverC$Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackuppubs.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)

Tags: sql-server sql-server-2008

Source: By Dan as answer to the question

This code snippet was collected from stackoverflow, and is licensed under CC BY-SA 3.0

Related code-snippets:

Add code snippet

code-tutorial.com

How do you copy a data base?

Related code-snippets: