Make a copy of a database in SSMS!

1. Make a .bak file backup.
    
2. Run this SQL referencing the file you just made:
   RESTORE FILELISTONLY FROM DISK = 'C:\Backups\Backup.bak'
    
3. This will give you details about the .mdf (datastore), .ndf (secondary data), and .ldf (transaction log) files which need to be copied elsewhere.
    
4. With that information do something like this:
   RESTORE DATABASE Foo_Rollback FROM DISK = 'C:\Backups\backup.bak' WITH MOVE 'Foo' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_rollback.mdf', MOVE 'Foo_History' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_rollback.ndf', MOVE 'Foo_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_rollback.ldf', STATS = 1
    

Above we copy Foo to Foo_Rollback. Not to do: When you right-click on a database in SSMS and then pick "Tasks" there will be a "Copy Database..." option beneath "Tasks" ...This will bring up the Copy Database Wizard which should do what you might think but I couldn't get it to work. You may see an error along the way saying "SQL Server Agent does not appear to be running on the destination server. If SQL Server Agent is not running on the destination server, Copy Database Wizard will not function properly. Do you want to continue?" and if so go into Services and manually start the SQL Server Agent.

Addendum 6/26/2015: The second step will return a LogicalName column in its recordset and this value should be used where I have Foo, Foo_History, and Foo_log in the fourth step.

Addendum 6/29/2015: The paths to files need to be unique paths to new files to be created upon the script running.

Addendum 7/1/2015: If you'll notice the piece of the paths above which read MSSQL11.MSSQLSERVER this piece gives away a version number. Version 11 is MSSQL Server 2012 while version 10 is MSSQL Server 2008.