Copying Databases with SQL Server Express Management Studio

Often you need to make a full copy of a database, for example, if you you want to change something and see how it goes without ruining a production database. And if you happen to use SQL Server Express edition you are not so fortunate because famous Copy Database Wizard does not seem to be unavailable in  SQL Server Express Management Studio. There is a simply solution, however. It is using the backup and restore features.

Just open SQL Server Management Studio Express, right-click on your database and choose Tasks > Back Up

You can leave the default settings or, for instance, save a backup in a different location.

After doing a copy step it is a about the time to do a paste step.

Right Click on the Database node and choose Restore.

Select your source database in the From database drop-down box and choose a destination in the To database you can also type a name of a database you would like to create and it will be created automatically containing all the data from the source database.

And voila, your database including all its data is copied and you can freely play with it without risking to break a functionality of your production database.

Of course, there are other solutions but this one seems to be the best one so far. Indeed, if you Import and Data Wizard, then, your destination database will lack all the foreign keys for some reasons, while using generated SQL scripts can be tedious.