title: Create Duplicate SQL Database for Testing
body: <p>I have created a database on SQL server and a front end user application in winforms c#.
  It's up and running and working fine, but I've now been asked to set up a test version by the client for training new employees, so they can put in dummy data whilst they are trained without effecting the 'real' live database.</p>
  
  <p>When I installed the database I had it all scripted, but things have changed since, and I don't particularly want to script all the tables again.  </p>
  
  <p>Is there a fast, convenient way of duplicating the database (and its data) on the same server, but under a different name?</p>
  
  <p><strong>EDIT (subsequent to Tony Hopkinson post)</strong></p>
  
  <p>I've got as far as this</p>
  
  <pre><code>exec sp_addumpdevice 'Disk','MyDatabaseBackup',N'D:\MyDatabaseBackup' 
  Restore Database MyDatabase From MyDatabaseBackup With File = 1, 
  Move 'MyDatabase' To N'C:\Program Files\Microsoft SQL 
  Server\MSSQL11.SQLE\MSSQL\DATA\MyDatabaseTEST.mdf', 
  Move 'MyDatabase_Log' To N'C:\Program Files\Microsoft SQL 
  Server\MSSQL11.SQLE\MSSQL\DATA\MyDatabaseTEST_log.ldf', 
  NORECOVERY,  NOUNLOAD,  STATS = 10 
  RESTORE LOG [MyDatabaseTEST] FROM  [MyDatabaseBackup] WITH  FILE = 2,  NOUNLOAD,  STATS = 10 
  exec sp_dropdevice MyDatabaseBackup 
  </code></pre>
  
  <p>But I'm getting the following error message</p>
  
  <p><em>Msg 3234, Level 16, State 2, Line 2
  Logical file 'MyDatabase' is not part of database 'MyDatabase'. Use RESTORE FILELISTONLY to list the logical file names.
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.
  Msg 3154, Level 16, State 4, Line 6
  The backup set holds a backup of a database other than the existing 'MyDatabaseTEST' database.
  Msg 3013, Level 16, State 1, Line 6
  RESTORE LOG is terminating abnormally.
  Device dropped.</em></p>