How to copy a SQL Server database to a different database

It is useful to be able to restore a database to a different database.
For example, you may want to create a development database with the
same structures and data as the production database.

Here are the steps:

1. Find out the file names of the database you are copying.

restore filelistonly
   from disk='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\my_prod.bak'

This will list the names of the database files.

2. Restore the database to the new database, moving the datafiles

restore database my_test
   from disk='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\my_prod.bak'
   with move 'my_data' to
             'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_test.mdf',
        move 'my_log' to
             'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_test.ldf',
        stats=5

This will do the restore and move the data and log files to new locations.
If you don't include the "move" parameters, it will try to restore over the
existing files and you will get an error like this:

Msg 1834, Level 16, State 1, Server TRF-SQL01, Line 1
The file 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_data.MDF'
cannot be overwritten.
It is being used by database 'my_prod'.

Msg 3156, Level 16, State 1, Server TRF-SQL01, Line 1
File 'my_Data' cannot be restored to
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_data.MDF'.
Use WITH MOVE to identify a valid location for the file.