We developed an article to show you how to restore MSSQL 2005 database to a newer version of MSSQL 2012/2014 step by step. Considering manipuility and convenience, we take MSSQL Server Management Studio 2012 as an example in this post.
1. Prerequisites and Considerations Before Restoring
It is a hard task to restore MSSQL 2005 database to MSSQL 2012/2014 database with Standard recovery equipment. Fortunately, SQL Server Management Studio can help you to do that.
This Microsoft application has been reset in WPF subsystem up to now. Its 2012 version can be downloaded for free with the given URL. Download URL is: http://www.microsoft.com/en-US/download/details.aspx?id=29062
When you start, you should confirm that only you at that time are using the database that is to be restored. Meanwhile, you need to backup your daily transaction log. When restore a cryptographic database, you need to be entitled to get to the asymmetrical secret key. Also pay attention to that, using SQL Server Management Studio to restore an alternate location, you can restore databases and re-name its file location and database name.
2. Restore Your Database with SQL Server Management Studio
Before operating this tool to restore database, firstly you need to attach to SQL Server Management Studio. Find the “Object Explorer” section on the left, and click the computer icon beside the “Connect” option to connect to SQL Server via Management Studio.
Then you will see a new dialog box opened, and you can see the “Server Type” and “Server Name”, then specify them. You should know that, the name of the server filled-in is what we call “External Server” on the control panel (If you use our ASP.NET hosting, during your account opening, you will receive an email with the information of your database server). For example, we use icebreak.host4asp.net in this case. So choose icebreak.host4asp.net.1430 to connect to SQL Server 2012 or choose icebrek.host4asp.net.1431 to connect to SQL Server 2014. As for the “Authentication” list, you should choose the “SQL Server Authentication” and fill user name and password to log in.
3. Select Tasks Tab
After connecting to server, you need to seek out the database that will be used to restore from the left area of Microsoft SQL Server Management Studio. Then, choose the database you need, and right-click the database and you will notice there is a new option menu. What you need do is to unfold the “Tasks” tab and choose “Restore” and then click “Database”.
4. Enter Source Section
After you click the source section, you will see a “Restore Database” dialogue box present. You should use the “Source” section to edit the location and find what backups to restore. Then, click the “Database” key and choose what database to be restored. You should know that this list only shows databases that backed up before.
5. Configure Restore Database
If your backups are from different kinds of servers, the destination server won’t show the history of the backup. If this happens, you need to use the “Device” section to edit the backup media and its location for your restore operation. To be accuracy, click the ellipsis key to open a “Select Backup Devices” window. Then choose the device type you need from the device screen. You can click the “Add” key to add other types of device to the “Backup Media” box if needed.
6. Select Your Device
After selecting devices for the “Backup Media”, click the “OK” key back to the “General” menu. Then pick out the database that need to restore with the “Database” list. Please remember that only you have chosen the “Device” can the database list is available and can these backup databases will be shown.
7. Definite Destination Box
By default, below “Destination” there are many databases provided to be chosen to restore from the “Database” box. When it comes to “Restore to”, you can leave it to “The last backup taken” or just press the “Timeline” to re-choose a special point timely if needed.
Within the “Backup Sets to Restore” part, there will be all available backups displayed for your pointed location. Just verify the “Restore” box beside the database that you want to restore.
If needed, go to find the “Files” section to alter the location of your database files. As is indicated, click “Relocate all files to folder” and then you can edit a different location for both the “Data file folder” and “Log file folder”. Moreover, you can also adjust “Backup”, “Restore” and “Connection” options in the “Options” tab.
