How can I back up my website and MS SQL database?
Backing up your SQL Server databases constantly is a good practice for any database administrator to avoid data loss or corruption. Backing up is the safest way to protect and restore your data from many failures like hardware failure, user failures, media errors and many more.
Moreover, a database backup is also ideal for day-to-day administrative tasks such as, database mirroring and copying a database from one server to another.
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.
- Firstly, open SSMS from the Start menu.
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.
Check out the next article on How can I access MS SQL 2000?