1 Introduction
2 Preparation
3 Backup a Database
- 3.1 via SSMS Object Explorer
- 3.2 via SQL Query
4 Restore a Database
- 4.1 via SSMS Object Explorer
- 4.2 via SQL Query
5 Conclusion

1 Introduction

If you want to work on a private database, it is advisable to back it up at regular intervals. How to do this with the Microsoft SQL Server Management I want to show you in this post.

2 Preparation

For this post, I’m creating a sample database (TestDB) and including a sample table (Example_Table) here.

Since I would like to have the output of SSMS always in English I use the following syntax.

SET LANGUAGE ENGLISH

How I create the database and the tables can be read in my post Create a Database.

CREATE DATABASE TestDB;

USE TestDB;


CREATE TABLE Example_Table
    (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;

Now I’ll insert some sample data in this table.

INSERT INTO Example_Table VALUES ('Klara', 'Brown')
INSERT INTO Example_Table VALUES ('Jessy', 'Williams')
INSERT INTO Example_Table VALUES ('Emily', 'Taylor')



SELECT * FROM Example_Table

Okay has worked. Now we can get started.

3 Backup a Database

3.1 via SSMS Object Explorer

On the one hand, we have the possibility to backup the database using Object Explorer. To do this, right-click on the database

and select Tasks and Back Up...

After that this window will open:

Backup type: Full is the right setting for our project. Below you can also see a suggested path for saving the backup under Destination. It was created automatically during the installation of SSMS.

Now just press OK and the backup will be created.

Here you can also see the created backup:

3.2 via SQL Query

Personally, I find the way via the Object Explorer a bit cumbersome and therefore prefer to use the possibility of a SQL query.

Here would be the syntax for it. You just have to name the database you want to backup and the location (I created a folder called ‘backup’ under my C drive).

BACKUP DATABASE TestDB TO DISK = 'C:\backup\TestDB.bak';

Again, we see the successfully saved file:

4 Restore a Database

Now that we know how to back up databases, I would like to show you how to load these backups.

However, before that we need to delete the created database (‘TestDB’). I have described how to do that in this post: Create a Database 3.2.5 Delete the whole Database

4.1 via SSMS Object Explorer

For the first variant we use the Object Explorer again.

To do this, right-click on the Databases folder

and select Restore Database...

This window will then open:

Select ‘Device’ as source and click on the three dots to select the path.

Click on ‘Add’.

Navigate to your backup and select it.

Now confirm twice with OK.

Done!

Let’s test a short query:

SELECT * FROM Example_Table;

Perfect queries work and the database is restored.

We can also see this from the fact that the two files: + TestDB.mdf and TestDB_log.ldf

now appear here again:

4.2 via SQL Query

Also the recovery can be done via SQL query, if you want.

Here would be the necessary syntax for it:

RESTORE DATABASE TestDB 
    FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TestDB.bak'
    ;

5 Conclusion

In this post I showed how to create backups of databases with SQL Server and how to load them again.

How to Backup a Database