SQL 2005 - quick way to quickly duplicate a database + data

I have my development database, I want to spin off a duplicate for testing. I want to do this often.

What's the fastest and easiest way to do this often? I can get the job done by generating scripts w/ data included but I feel like there must be a better way. Should I back up database 1 then screw around with it so I can restore it as a different name?

From stackoverflow

• There's a command line tool (DOS) for creating and restoring databases.

  Also you can perform this as T-SQL script. http://www.sqlmag.com/Article/ArticleID/46560/sql_server_46560.html

• Detach the database, copy the .MDF file (and .ldf file) and you can attach both. Or only one, to keep one as a copy for a fast copy later.

  The "Copy database" command from Mcirosoft SQL Server Management Studio can also work like this.

  You can automate/script this easily, using PowerShell or just .Cmd files with calls to osql.

• And you can also use the SQL Management Studio UI to create a file backup, and then restore it into a new database on the same or any other server.

  Redbeard 0x0A : Just keep in mind to watch where the restore operation is trying to put the mdf and ldf files (watch for it trying to overwrite files you don't want it to overwrite)...

  Kip : when i try restoring to a different database, i get an error: "The backup set holds a backup of a database other than the existing 'dbname' database"

  Charles Bretana : There's a checkbox somewhere there that allows you to restore from a backup taken from another server.. I don't have access to the screens right now, and it's in a different place depending on which version you have... but look for that checkbox option

• In SQL 2005, I would say the fastest way is to use Copy Database, under Database --> Tasks

  The source and destination can be the same, and it allows you to rename the database if desired.

• I would use scripts, as this aids transparency of what you're doing.
  (Especially if you want to do it often, as changes are each one will have slightly different data.)

  However if you don't want to go to the hassle of creating the scripts, the easiest way is to backup and then restore to a different name.

• If you want to script it you can use the following. This is also best if you can't take the db offline:

  USE master
  GO
  
  -- the original database (use 'SET @DB = NULL' to disable backup)
  DECLARE @DB varchar(200)
  SET @DB = 'PcTopp'
  
      -- the backup filename
  DECLARE @BackupFile varchar(2000)
  SET @BackupFile = 'c:\pctopp\sqlserver\backup.dat'
  
  -- the new database name
  DECLARE @TestDB varchar(200)
  SET @TestDB = 'TestDB'
  
  -- the new database files without .mdf/.ldf
  DECLARE @RestoreFile varchar(2000)
  SET @RestoreFile = 'c:\pctopp\sqlserver\backup'
  
  
  -- ****************************************************************
  --                    no change below this line
  -- ****************************************************************
  
  
  DECLARE @query varchar(2000)
  
  DECLARE @DataFile varchar(2000)
  SET @DataFile = @RestoreFile + '.mdf'
  
  DECLARE @LogFile varchar(2000)
  SET @LogFile = @RestoreFile + '.ldf'
  
  IF @DB IS NOT NULL
  BEGIN
      SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
      EXEC (@query)
  END
  
  -- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
  -- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
  -- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
  -- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
  
  IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
  BEGIN
      SET @query = 'DROP DATABASE ' + @TestDB
      EXEC (@query)
  END
  
  RESTORE HEADERONLY FROM DISK = @BackupFile
  DECLARE @File int
  SET @File = @@ROWCOUNT
  
  DECLARE @Data varchar(500)
  DECLARE @Log varchar(500)
  
  SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
  
  CREATE TABLE #restoretemp
  (
   LogicalName varchar(500),
   PhysicalName varchar(500),
   type varchar(10),
   FilegroupName varchar(200),
   size int,
   maxsize bigint
  )
  INSERT #restoretemp EXEC (@query)
  
  SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
  SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
  
  PRINT @Data
  PRINT @Log
  
  TRUNCATE TABLE #restoretemp
  DROP TABLE #restoretemp
  
  IF @File > 0
  BEGIN
      SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') + 
          ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
          QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
      EXEC (@query)
  END
  GO
  

  Copied from: http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx

• One more option to throw in,

  Here's a blog entry that talks about using the Database Publishing Wizard and how it's used to export all elements of a database, including data, to an SQL file. Might be helpful and seems pretty straightforward.

  http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/