In this tutorial, we will explain how to back up an SQL database so that your databases are always secure as part of your Business Continuity Plan.
When it comes to keeping your business’s IT infrastructure safe and secure against any potential risks, the most basic measure is to ensure that you have a backup of all your systems, including your databases.
Not only can you use such backups to recover your systems but you can also use them to migrate data to another system.
In this tutorial, we’ll explain how to create a backup of your SQL database using SQL Server Management Studio. We’ll look at how to do this using Transact-SQL or PowerShell in future tutorials.
PLEASE NOTE: This tutorial describes the process to follow when using SQL Server 2019 on Windows Server 2002. However, there won’t be any significant differences if you’re using a different version of SQL Server.
How to Back up an SQL database
Before you begin
To successfully complete this tutorial and back up your SQL database, you will need:
- To be registered with an organisation on the Jotelulu platform and have logged in.
- To have registered for a Servers subscription
- To have an SQL Server running on your Servers subscription.
Backing up an SQL database
To back up your SQL database using SQL Server, you first need to launch the SQL Server Management Studio. Here, you will then need to browse the Object Explorer to find the database that you wish to copy (1). This can be either a system database or a user database.
Next, right-click on the database that you want to back up, select “Tasks” (2) and then select “Back Up…” (3).
At this point, a window will appear in which you need to configure the backup. First, select that the right database has been selected (4).
Then, select the type of backup from the three possible options (5):
- Full – a backup of the entire database.
- Differential – a copy of any changes made.
- Transactional Log.
For the purposes of this tutorial, we’ll select “Full”.
Under “Backup component”, you have the option to choose “Database” or “Files and filegroups” (6). Select “Database”.
Then, under “Destination”, you should enter the location where you want to save the backup (7). You can leave it set to the default path if you wish, but we always recommend backing up to an external location in case something happens to corrupt the server.
At this point, you also have the option to review “Media Options” and “Backup Options” (8).
Once you have reviewed everything, click on “OK” (9).
If necessary, you also have the option to review the connection properties by clicking on the link on the left-hand side (10).
If you click on “View connection properties”, you will see the details of the server, including the authentication details, network connection details, available memory, etc.
Once you click on OK, the backup process will begin. This may take a while depending on:
- Server Power (RAM, CPU, Disks, etc.).
- Network Connection between the server and the backup destination.
- Size of the database.
Once this process has finished, a message window will appear saying “The backup of database “DATABASE” completely successfully” (11) where DATABASE is the name of your chosen database, in this example, “SQLNachoPruebas”.
At this point, you have to option to copy the message (12) should you wish to use it for a report. However, I think a screenshot is more useful.
To finish the process, simply click on “OK” (13).
Lastly, to check that everything ran correctly, we recommend opening Windows Explorer and browsing to the backup location to check that the backup files have been created (14).
You have now backed up your database and can use the backup to restore your database if an incident occurs.
NOTE: When making a backup using SQL Server, you will not be able to restore the database using previous versions of SQL Server.
NOTE: You also have the option of making an encrypted backup for increased security.
In this tutorial, we have explained how to back up an SQL database using SQL Server Management Studio, hosted on Jotelulu. As you can see, the process is really very straightforward.
We hope that this short guide will help you to create your backups without any problems. But if you do encounter any issues, please don’t hesitate to contact us so we can help.
Thank you for choosing Jotelulu!