Creating A New Database From A Backup Of Another Database On The Same Server
Yeah, the title is a bit of a mouthful but this is something that I’ve seen people struggle with constantly. The issue being that if you’re trying to restore a copy of an existing database on the same server using a different name for the restored database, when not configured properly, it can actually overwrite the existing database.
I recently ran into a situation which required me to create a second copy of a very large and very important database on the production server. Specifically, the company financial database associated with Dynamics GP. After a major upgrade we noticed two of the Historical GL tables had been wiped out. Fortunately these were historical tables so the likelihood of new data being inserted into these during normal business processing was very low. The solution was to restore a copy of the company database alongside the current company database so that we could script out the necessary INSERT
script to copy the data over.
The steps outlined below will walk you through the process of creating a new database from a backup of another database on the same SQL Server and giving it a new name so as to avoid overwriting the existing database.
Let’s assume you have already created a backup of the database.
There are a couple different ways to go about this. Though it is not necessary to create a new blank target database for the restore, I find that doing this further minimizes the risk of overwriting the existing database.
To create a new blank target database connect to the server where you will be restoring the new database and right-click Databases. From there click the option New Database.
In the New Database window you’ll need to provide a name for the new database on the General page. In this example I am calling it CARDF2 as the original database was named CARDF. From here you can also assign the location for the data (.mdf) and log (.ldf) files in the Path column.
You also want to make sure you provide the necessary file name for the new .mdf and .ldf files under the File Name column.
This is very important. If you do not specify the new file names the restore could overwrite the existing database.
Though you can further configure the new database on the Options Page, this is not really necessary when performing a basic restore. I would also recommend leaving the database in Simple Recovery mode unless you have a specific reason for changing it.
Now that the empty target database has been created, the next step is to restore the backup to the new database.
To begin this process right-click on the newly created database (CARDF2 in my example) and select Tasks >> Restore>>Database… as shown below.
On the Restore Database window the first step is to select the backup (.bak) file that you want to restore. Under the Source section of the window select Device and click the ellipses button to select a backup device.
On the Select backup devices window click the Add button. Locate the path to the backup file and click OK.
You should now see the file listed under Backup media. Once confirmed click OK to return to the Restore Database window.
The next steps are the most important as I will further configure the restore to ignore the original database and restore the the newly created target database.
On the General Page confirm that the database listed under Destination is the same as the newly created target database.
Though you will see the original database information listed under Backup sets to restore, this only confirms that the backup you are restoring is from the original source database.
Next go to the Files Page. Here you will want to scroll over to the Restore As column and click the ellipses for both the data and log files.
Select the appropriate file location for each file and enter the new file name for the restore. It is very important that you provide an alternative file name to the original database file name. For best practice use the name of the newly created target database (in this case CARDF2).
Once configured, click OK.
Finally, on the Options Page you’ll want to make sure to select the check box next to Overwrite the existing database (WITH REPLACE). You may also want to uncheck the box next to Take tail-log backups before restore. Not only will this further prevent the original database from being referenced, it will speed up the total time it takes for the restore as the tail log backup can take a while to run depending on the size of the transaction log. The only time you really need to include the tail log backup is if you are going to restore a database to the point of failure. If you’re simply restoring the database from an existing backup, which I find to be the most common practice, no tail log backup is required.
You can also select the check box to Close existing connections to destination database, though this is not required.
This is vital to guaranteeing that the original database does not get overwritten.
Once the above steps have been completed click OK to kick off the restore.
Once the restore has started you can refresh the list of databases in SQL Management Studio to confirm that the newly created database is being restored with the data from the original.
As always, if you’re new to this process it is best to run through these steps in a lab environment first. Once you are comfortable with the steps outlined above, and only if necessary, should you perform these steps in a live production environment.