How To Backup and Restore MSSQL on a Windows VPS or Dedicated Server

How To Backup and Restore MSSQL on a Windows VPS or Dedicated Server

Database backups crucial for any application and files to safeguard against any eventuality that may lead to data loss. Microsoft has an inbuilt feature that is used to easily backup and restore data.

Overview

The Microsoft SQL Server Management Studio Express (SSMSE) is an open source graphical management tool that is used to manage, configure and administer your SQL server. This tutorial will cover how to back up and restore our SQL database. The backup is saved in the form of a .bak file either locally or externally depending on your choice of backup location.

Creating a backup

2012 MSSQL database is used for this demonstration. The backup file created is a .bak file extension. Download the SSMSE suitable for your system.

Open  MSSMS and Log in to your database using your Database username and password.

Expand databases and select the database to backup then right-click tasks then click back up.

A backup window appears. On the database option, make sure that it is the correct database that you want to backup. Select Destination (where backup file will be stored).

Then you are ready to choose the backup type.

Choose Backup Type; Full, Name of the backup file ;

For our backup, we will select full and specify the location where we want to store the backup.

On the backup database window, click on add to select the storage location and a name for the database backup.

Click on OK to finish the backup. A confirmation window will appear with a “completed successfully” message.

You have successfully created a backup of your MSSQL database.

Restoring the Backup

Open your Microsoft management studio and connect to your database. Then select databases and right click it. Click restore database.

A restore database window appears. Select the source as Devices. Browse to the location of the backup created above.

Click add, and navigate to the folder where you saved the backup.

Click OK when done. Specify the destination, and by default, it is going to keep the database name. If you leave it as it is, it is going to wipe the original database and restore it, so for this tutorial, the backup will be restored as database testDB2.

Change the data and log file for testDB2. So click on files and navigate to restore as. Change the name to testDB2.mdf. Do the same for Log file and name it testdb2.log

Click on OK to begin the restore.

You have successfully restored your backup to the database to TestDB2.

If you go to the management studio and refresh databases, you will see both databases there.

Conclusion

We have successfully restored a database thanks to the Microsoft SQL Server Management Studio Express. This tool combines a broad group of graphical tools to provide one of the most trusted backup software’s. Any developer of any skill level can use it as it is quite simple to use. You can also use some third-party apps, but this is the most comfortable method yet.

Check out the top 3 VPS services:

Kamatera
£3.15 /mo
Starting price
Visit Kamatera
Rating based on expert review
  • User Friendly
    3.5
  • Support
    3.0
  • Features
    3.9
  • Reliability
    4.0
  • Pricing
    4.3
ScalaHosting
£2.32 /mo
Starting price
Visit ScalaHosting
Rating based on expert review
  • User Friendly
    4.8
  • Support
    4.3
  • Features
    4.4
  • Reliability
    4.8
  • Pricing
    4.5
Liquid Web
£3.93 /mo
Starting price
Visit Liquid Web
Rating based on expert review
  • User Friendly
    3.8
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.6
  • Pricing
    3.8

How To Install and Configure MySQL for PHP Applications on Windows IIS 7

When hosting PHP apps on IIS 7 it’s highly recommended you use the Miscrosoft
3 min read
Idan Cohen
Idan Cohen
Marketing Expert

How to Install MySQL on a Windows Web Server Running Apache

This tutorial will show you how to install the MySQL database on a Windows serve
3 min read
Michael Levanduski
Michael Levanduski
Expert Hosting Writer & Tester

How To Install MySQL on Windows

This how-to article teaches you how to install MySQL on Windows.
4 min read
Avi Ilinsky
Avi Ilinsky
Hosting Expert

How to Install Ruby on Rails on a Windows VPS or Dedicated Server

This guide shows you how to install the Ruby on Rails web framework on Windows.
5 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top