You have just installed SQL database backup solutions after putting the importance of database backup into perspective. What next? You need a guide on how to create a full database backup in SQL 2017 version, lest having the solutions (which in this case could be PowerShell, Management Studio or Transact-SQL) will be pointless.
There are a few things you need to take note of before you kick-off. Since there are many versions of SQL servers, for instance, SQL server 2012, 2014 and 2017, if you create a backup in a newer version, you cannot restore your data in an older version of the server.
Also, the larger the size of a database, the more storage space it takes and the longer it will take you to do a full backup. In such cases, you can consider doing a series of differential backups. Permission problems can also get in the way of you creating a backup. Ensure that your server can read and write to the device.
How to backup SQL database
1. The first step to back up your database is to start your database backup tool: in this case Management Studio. The next thing you will be required to do is to select the server that you want to connect. For example, the Database Engine, which you will also be required to enter the name of the particular server.
2. The instance you get connected, Management Studio will bring up a window(object explorer) that has features that will assist you in managing objects such as the database, security, and replication to mention a few.
3. On that screen, click on the object labeled as ‘Database’ and expand it. The Database will provide you with a list of databases. All you will be required to do is select the database you intend to back up. It could be database snapshots or system database: it all depends on how they have been named in your computer.
4. After selecting on the database you are interested in, the next step you should take is right clicking on it. You will be given a menu that will have some submenus. Among them is a “Tasks” submenu. Open the submenu and select the “Backup…” option.
5. Once that is done, there is a dialogue box that will appear with several options. Yes, it might look more tedious than the steps you have already gone through, but there should be no cause for alarm.
On the left side of your screen, there are some tabs, the first one being “General.” That is what you are to select. The tab will give you some setting that you will be required to adjust: if need be.
i. You will see “Backup type,” click on the drop-down arrow and set it to “Full” if it was on any other type of backup.
ii. Just below, at the bottom of the dialogue, there probably will be backup settings already defined, mostly from previous backup operations. You will need to select all of them and click “Remove.”
6. In the same dialogue box, below “General,” there is a “Backup options” tab. On clicking Backup options, you will get some settings, one being an expiry option. You should ensure that it is set to “After 0 days“. It is an important setting that you would not want to leave out for anything!
7. After adjusting all the mentioned settings on the “General” and ”Backup options ” tabs, go back to the dialogue box. You will note that settings from the two tabs will be displayed on the dialogue. Just below it you will need to select the destination of your backup: it could be to Disk. Once you have chosen the right destination, click “Add.”
8. There will be more destination settings once you click on “Add.” You will need to choose the File Name option and click on the button “…” on the right. The next step will be finding the location to store your backup: that is, on the Locate Database files dialogue.
You will be provided with a field where you will enter a name for the backup. The name you enter should be followed by “.Bak”. For instance, if you have named your file as MyKPIs, it should appear as MyKPIs.Bak. Remember that if you are doing a remote back up, the backup destination will be on the remote server and not on your local computer.
Once that is done, click “OK” to save all your settings.
9. Do a quick check to ensure that everything is in place. Do you feel that you are now ready to start the backup? Well, what is remaining is as simple as clicking “OK” at the bottom right of the window.
10. Once the backup is complete, you will get a success message.
Note that for every successful backup, you will always get success messages as entries in both system event and server error logs. What happens if you do regular backups? The messages will accumulate, and you will get huge error logs which can make locating other messages a hassle. You can counter this by using a trace flag which will allow you to disable the logging of all successful operations into your server.
