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:
- What is a change to SQL Server tables?
- How do I upgrade my SQL Server database version?
- What is the best implementation of MySQL for SQL Server 2005?
- The ability to edit a database by multiple users is essential.
- How do I unit test persistence?
- Convert HashBytes to VarChart?
- How do I divide a string so I can access item x?
- How do you get the data from a temporary table in SQL Server?
- How do I use T-SQL group?
- How much disk space does a SQL Server table currently use?
- Can you remove the time portion of a datetime value?
- How do you get leading wildcard full-text searches in SQL Server?
- How can we convert a SQL column to a row?
- SQL query for database scheme.
- T-Sql Remove decimal point from Money Data Type.