Recently I had a conversation with someone who maintains an ISV offering. He is the acting SQL Server DBA (for several years now). He described what he did to move a copy of the current production system for a training session on another continent. The steps he took were:
When he arrived at the training site, he copied the files off the portable device, attached them .... SQL Server complained. He had not copied the transaction log. A few phone calls and a download, and then everything was fine. It worked, but there is an easier way of doing this.
In this post, I will illustrate how to do three actions
Create a copy-only backup of an operational database suitable for use by:
At the bottom, there is a Destination list box. My usual practice is to [Remove] everything listed first (this will not impact scheduled backups).
One word of warning, if there are two or more files listed, the backup will be split over each file.
That's it -- there is no need to copy transaction logs, detach databases, etc -- a standalone backup as a file has been created.
On the machine that you wish to place a clone on, do the following steps.
Click on [Add] . The Locate Backup File dialog will appear. Locate and select your backup and then click [OK].
If you cannot see your backup file where you expect it, the file may not have a .bak or a .trn extension. There are two solutions:
This dialog will close and you will see the file listed in the Backup location list box. Click [OK]
This dialog will reappear with the file(s) listed in the bottom right list box.
You need to enter the name of the database -- it should not be the same name as any existing databases on this SQL Server. I picked 'MyClone' for purposes of illustration
You will now see the database listed in SSMS.
Once the database has been cloned it is easy to restore it back to its original state. Why would you do this?
The steps are similar to the last section.
For restores, we need to go to the Option Page listed on the left. After clicking on Options, the page below will appear
Click [OK] and the backup is stored over the existing database, losing all changes made to it since you installed. A message will appear indicating successful completion as shown in our prior section.
Moving a database is not a daily or even monthly activity for many people that administer an ISV database as part of their other duties. The above guide should make it easy to do the next time you need to move or copy your production database for testings or training purposes.