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
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
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
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.
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way. My personal thoughts tend to change, hence the articles in this blog might not provide an accurate reflection of my present standpoint.
© Mike Borozdin