SQL Server 2008 R2 provides the opportunity to take backup and restore, as in other Microsoft server and user operating systems.. The difference between backup and restore in SQL Server 2008 R2 is that the information on the database can be backed up and restored.. Taking backups of databases and keeping them in the right place is as important as taking backups of operating systems.. In case of possible problems, the database is damaged or lost, it is possible to return from the backup taken.. Ensuring the reliability of the functioning of the existing systems, which is one of the duties of system management, is achieved by performing the backup and restore operations correctly. Compress Backup tab should be selected by selecting Properties on SQL Server and coming to Database Settings settings.. This will help in compressing the data, thus saving space.. Its selection is optional.
After determining that the backup will be created in compressed file format, let’s move on to the backup processes.
Microsoft SQL Server Management Studio is opened to take a backup of the SQL Server 2008 R2 database being used and Tasks is created on the created database. Back Up is selected from the tab.
In order to make the settings of the database to be backed up, some information must be given in the interface that appears.. We can see the name of the database to be backed up on the Database tab, the backup of this database comes as Full Backup in default settings.. The preferred backup should be full backup, because in case of taking and returning a differential backup, only the changed differences will be restored and accessed, not all data.. Here, I show the backup of the database as selected. What date can it be scheduled to end?. This is a setting I would not prefer. Because although I have taken other backups in the future, I consider that I may need to return to previous backups when it comes to changing.
It can determine the path to take the backup from the Destination tab.. By default, SQL will take the backup by creating a Backup folder under Program Files on whichever drive it is installed.. However, since I installed on the D drive, it shows by default that it will take the backup files here by creating another folder with the MSSQL name extension under the folder named SQLDATA.. Here, it is possible to make optional changes to the folder path to be backed up.
I recommend removing the existing backup path by using the Remove button.. It will be more reliable to take and maintain backups on a different drive or even a different machine.. Because backups taken on the same disk will both negatively affect the performance of the server and in case of system failures on the server, access to the backup will become difficult or even impossible.. In order to avoid such undesirable situations, the Backup folder to be created on a different drive will do the trick.. For this, I show it by creating the SQLBACKUP folder under the D drive by using the Add button.
After the SQLBACKUP folder is displayed, the file is named. The file name extension is edited to be .bak and it is confirmed by selecting the arrow.. Devices on the network will not be visible as it is disabled by default.. TCP/IP settings must be enabled for this.
sqlfull under the SQLBACKUP folder created under the D drive. We can see that the bak file is now the location where the backups will be taken.
When switching from the General tab to the Options tab; In order to take the backup, it can be determined that the data is added to an existing backup location or overwritten.. For this, if it is only wanted to be added, the first option, “Appened to the existing backup set”, should be selected, and if it is preferred to overwrite the existing data, “Overwrite all existing backup sets” should be selected in the second option.. It is preferred in cases where it is aimed to replace the backup or to save space.
On the Reliability tab, there are options to control the backup to be taken.. Optionally, the selection process can be preferred.
Before starting the backup process, if desired, the Script tab can be selected to display the backup process with the SQL command line. should be started. We can see that the backup has been taken with the settings we have determined for the folder where the backup will be taken.
I am viewing the sqlfull.bak file in the SQLBACKUP folder I created under the D drive to check that the backup is taken to the specified location with the specified settings.
It’s time to return to the backup that was previously taken in case of possible disasters.
The database may be deleted or damaged, in case of such situations, let’s see how to easily restore from the backup taken.
SQL Server 2008 Selecting Restore Database on Databases under R2 will start the restore process.
To show the location of the backed up database, using the From device tab, the sqlfull.bak file is displayed. It is added to the Location section.
After the selected backup, if there is more than one database in SQL Server, the database to be restored should be selected.
When we go to the Options section after the settings made in the General tab, If the return operation will be done on the existing database, Overwrite the existing database in the first tab should be selected.
In the Recovery state tab, the first option is selected and ready for use, and the restore operation is performed from the selected location with the OK button.
In this article, SQL In systems using Server 2008 R2; I tried to explain how to make and receive backup and restore settings, and how to use the compressed backup feature in practice.