How can I back up my website and MS SQL database?
Any database administrator who wants to prevent data loss or corruption should regularly backup their SQL Server databases. The safest way to safeguard and recover your data from several problems, including hardware failure, user failures, media mistakes, and many more, is to create a backup.
A database backup is also perfect for routine administration activities like database mirroring and copying databases between servers.
MSSQL is the most widely used database management system for Windows-based systems powered by Microsoft. It is used to store website data such as blog and user information.
In this guide, we will show you how to backup and restore the MSSQL database on Windows operating system.
Prerequisites
- A server running on Windows server 2016.
- SQL Server and SQL Server Management Studio (SSMS) installed in your system.
Backup MS SQL Database
You may download all your websites via FTP to your local PC.
However, to access the MS SQL server, we will send you the configurations for MS SQL upon request and you will be able to remotely access the server and perform a backup.
Create New Database
- Firstly, open SSMS from the Start menu.
- Secondly, click on Connect button to connect your MS SQL Server.
- Then, click on New Query to open a new query window.
- Type the following query in the above query window to create a new database named NEWDB and create a new table named TABLE1:
USE [master] GO CREATE DATABASE [NEWDB] GO USE [NEWDB] GO CREATE TABLE TABLE1 ( ID INT NOT NULL PRIMARY KEY, a1 VARCHAR(100) NOT NULL, dt1 DATETIME NOT NULL DEFAULT getdate() ) GO
- Next, click on the Execute button to run the query.
Take Backup with SSMS
- Firstly, open up SSMS and expand Databases section.
- Secondly, right-click on the NEWDB and click on the Tasks > Back Up.
- Select all required information like, Database, Backup type, Backup path then click OK.
- Lastly, you get the confirmation message The backup of database “DBTEST” completed successfully if the backup is successful.
Restore MS SQL Backup
- Firstly, open SSMS.
- Then, right-click on the NEWDB and click on Tasks > Restore > Database.
- Next, select Device and click on the “…“.
- Click on the Add button.
- After that, select your database backup file and click OK.
- Click on the OK button again to close the “Select backup devices window”.
- Lastly, click on the OK button to restore the backup of your database.
