How To Copy A SqlServer 2005 Database

So today I started a new project here at the office. I'm going to update our homebuilt CMS to include some new functionality that our sites require. The changes I plan to make will require changes to the database structure. Now the current version of our app's database is running on SqlServer 2005. Since other developers will continue work on the old app while I work on the new one it was necessary for me to create a copy of the database and do my development against this copy. The problem was that every time I need to make a copy of a database I have a hard time remembering the right way to go about it. Searching on Google was no help. I got links to a bunch of 3rd party crapware and out of date MSDN articles. None of which helped. Eventually I figured out a good way to do it and figured I'd post it here for anyone else who might need to do this. What I'm describing here is a way to create a duplicate of an existing database on the same server as the existing db. This method uses Sql Server Management Studio Express. I'm sure the full version would work as well. I imagine a similar procedure would work across servers too. The first step I did was to create a new database on the server which would be the duplicate db. For the purposes of this article let's assume that my original db is called 'Prod' and my new db is called 'Dev'. So I right clicked on the 'Databases' folder in SSMSE (Sql Server Management Studio Express), chose 'New Database . . .' and created a new db called 'Dev'. Next up I right-clicked on the 'Prod' db (the one I want to duplicate) and chose 'Tasks -> BackUp . . .' I created a full backup of the db just to make sure I had the latest data. Once the backup had completed I then right-clicked on the 'Dev' database and chose 'Tasks -> Restore -> Database . . .' On the 'General' page I specified 'Prod' as my 'From database' and 'Dev' as my 'To database'. Note that when you select the 'From database' the 'To database' value gets changed to that value. You need to make sure that the 'To database' value is actually the name of the duplicate database. Next I clicked over to the Options page in the Restore dialog. Here I had to change the 'Restore As' names of the files in the file listing. By default they are the same as the 'Original File Name' values. But that would cause the backup to overwrite the original database. What we want is to change the 'Restore As' names to be the names of the duplicate database's files. In this example I changed these two entries: C:\Program Files\Microsoft SQL Server\MSSQL\data\Prod.mdf C:\Program Files\Microsoft SQL Server\MSSQL\data\Prod_log.ldf To: C:\Program Files\Microsoft SQL Server\MSSQL\data\Dev.mdf C:\Program Files\Microsoft SQL Server\MSSQL\data\Dev_log.ldf Then, still on the Options page of the 'Restore Database' dialog, I checked the 'Overwrite the existing database' checkbox. Next just hit the 'OK' button and let it do its thing. The end result should be a duplicate of your original database! Hope this helps some of you out there. So first I created a new, empty db called 'Dev'.