This tutorial will take you through the steps to restore an old MSSQL 2005 database to a newer release of MSSQL 2012/2014. In consideration of operability and convenience, we’d like to take the example of a lightweight Microsoft software application named as SQL Server Management Studio in the following parts.
Restore Prerequisites & Considerations
Restoring SQL 2005 database to SQL 2012/2014 database is almost impossible with standard restore facilities. The good news is that you can use the mentioned SQL Server Management Studio to achieve your goal. To be frank, this SQL management tool has been widely used for managing, administering and configuring all Microsoft SQL server related components.
Being similar to the Visual Studio web publishing tool, this Microsoft application so far has been rewritten in WPF subsystem, and its latest 2012 version is available for free download through the following URL.
Before starting out, you should make sure that you are the only person presently using the ready-to-restore database. In the meantime, you have no other choice but to back up your active transaction log whether under the bulk-logged or full recovery model. To restore an encrypted database, you also need the access to the asymmetric key or certificate. Note also that, restoring to an alternate location within SQL Server Management Studio means you can restore a database and re-define its file location and database name.
Use SQL Server Management Studio to Restore Your Database
To start performing the SQL database restore, you should connect to the instance of the SQL Server Management Studio firstly. From the left-side “Object Explorer” section, just click the computer icon next to the “Connect” option.
This will open a new dialogue window, from which you can specify the “Server Type” and “Server Name.” Beware that, the filled-in server name is what we call “External Server” from the control panel. Within the drop-down “Authentication” list, select the “SQL Server Authentication” and input your database username and password as below.
Choose Tasks Tab
Having connected to your database server, you will need to find out the database to be restored from the left part of SQL Server Management Studio. Next, right-click on the chosen database and you will be displayed with a new options menu as follows. All you need to do is expand the “Tasks” tab and go to “Restore” > “Database.”
Enter Source Section
Once clicked, a “Restore Database” dialogue box will appear on the screen, and you should make use of the “Source” section to define the location and source of the backups to restore afterwards. From here, click the radio button of “Database” and make a selection of the database to restore. Pay attention that, this list only displays databases that have already been backed up based on your msdb backup history.
Configure Restore Database
If you make the backups from different servers, then the destination server won’t display the backup history for the chosen database. If this is the case, you will have to use the below “Device” section to specify the device or file manually. To be precise, just click on the ellipsis button to open a “Select Backup Devices” window. From the next screen, pick out your needed device type. To add one or more device types to the “Backup Media” box, you can click on this “Add” button.
Select Your Device
Having done selecting your devices for the “Backup Media” box, you can click the bottom “OK” button to revert to the previous “General” menu. Just choose the database to restore within the “Database” list. Please note that the mentioned database list is only available when you have selected the “Device”, and only those backed-up databases will be displayed out there.
Customize Destination Box
By default, the below “Destination” will automatically provide you with various databases to be restored from the “Database” box. As for the next “Restore to” box, just leave it to “The last backup taken” or press the “Timeline” box to re-select a unique point in time as you need.
Within the last “Backup Sets to Restore” section, you will be presented with all available backups for your specified location. Just check the “Restore” box next to the database you want to restore.
Important to Note: If needed, you can go to the “Files” section to re-define the location of your database files. As is showed, click on the box of this “Relocate all files to folder” and you will be able to specify a new location for both of the “Data file folder” and “Log file folder.” By the way, you also have the ability to adjust “Backup”, “Restore” and “Connection” options within the “Options” tab.