Copy Database

Copy Database

• Donald_La

   

  1)  just back up the original database and make a copy

  2) Create a new database

  3) Then use attach and dettach command

   

  EXEC sp_attach_db @dbname = N'AdventureWorks',
      @filename1 = N'c:\Program Files\Microsoft SQL Server
          \MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
      @filename2 = N'c:\Program Files\Microsoft SQL Server
          \MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf' ;

   

   just set the parameters..all these you can do in one SP.

  
  
  

• kje

  One easy way to set it up (if you have two machines runing SQL Server) is to use the Copy Database Wizard and save either the DTS in the database or save to a script.  Then I think you might be able to tweak the script to modify the target database name and filenames.

  I haven't done this before so no guarantees.  Once you had it setup though as a DTS, you could use Global variables to pass in the databasename and and filenames.  You could then execute the DTS from T-Sql and pass in the variables.

  Otherwise, I think you can use SMO although it will take looking closely to see the best path.  Even  using SMO, the easiest way may end up being scripting a backup and restore of the database.  It seems to me that it is the cleanest option.

  
  

• punkrock

  At the end of the wizard, you can set it to run on a schedule instead of immediately. In the schedule, just set it for a previous date so it won't ever run. Then it will create the copy package for you.
  

• Stephen.

  That was a great idea....BUT...the script button is disabled.

  
  

• Gaurav Sehgal

  We dont' want to "deploy" a database. It need to be a SQL SP that we can run that will copy an existing "base" database in our server to a new database.
  

• Mike The Bike

  There are different ways you could approach this depending on your needs. If one day of data latency isn't an issue, you could just restore a nightly backup of the database in question as a new database name. You could setup a stored procedure to receive variables for database name, physical file name, and any other specific options. Then parse and execute your restore statement.

  http://msdn2.microsoft.com/en-us/library/ms186858(d=ide).aspx

  
  

• maggi

  You can use the "VS2003 setup project" to deploy the database to the client. VS 2005 has better support for these kind of scenario.
  
  

• Jesper

  I was looking for a more direct approach. I know there is the Copy Database wizard. Are there commands that I can use that use the SMO version of that wizard
  

• XxxFG

  The copy database wizard has no facility to create a script.

  This needs to be something that can be run through the application by an administrator.

  
  
• Copy Database