The main task in a Database manager is to not just make sure all data is inserted, but also that these data continue existing. Regardless of the contingency system, the Backup system is indispensable. In this chapter, we will discuss how the system works and which size the database must have to define the better backup policy.
There are two types of Backup:
Full: Backs up all data and part of the log, that is, contains all data from a database or from a set of file groups, in addition to the log for allowing data retrieval.
Differential: Backs up the part of the database that had been changed since the last full backup. This data backup is based on the most recent full backup of a partial or full database, or on set of file groups (the differential basis) that contain only the extension of data changed from the differential basis. A partially differential backup records only the data extensions that had been altered in the group file since the previous partial backup, known as the basis for the differential.
1) Backup via graphic interface
Most of the times, data can be backed up via a graphic interface, as seen below (right-click Database – Tasks – Back Up):
Figure 1: Back Up Database
2) Restore Database
This is an important resource for retrieving information. A database can be returned only from a full backup. Data should be restored frequently in a test environment in order to avoid failure, thus checking the process’s consistency. Any time data is restored, the database becomes unavailable for users access, as seen below (Tasks – Restore – Database):
Figure 2: Restore Database
3) Backup via commands
You can also generate a Database backup in the SQL Server via E3 by using commands. To do so, access E3Studio and create a Query linked to the Database and then edit it with SQL Backup Database command.
For example, a query called Query1 with backup full SQL command to save on C:\Backup folder the ‘test.bak‘ backup file:
BACKUP DATABASE [DatabaseName] TO DISK = ‘c:\Backup\test.bak’
After that, you must create a script to run this Query via Execute() method. For example, the script below is executed at the Click event of a button on screen:
4) Restore via commands
Once the SLQ DB is backed up, to restore the generated .BAK file, follow these guidelines:
1. Open Management Studio.
2. Click New Query option or File-New-Query with current connection menu.
3. On the query’s window, type this command:
FROM DISK = ‘c:\Backup\testsql.bak’
4. Click Execute; the result will display the .MDF and .LDF files with each file’s respective logical name.
5. Create a new query by repeating step 2.
6. Type the following command:
FROM DISK = ‘c:\Backup\testsql.bak’
WITH MOVE ‘teste’ TO ‘c:\Backup\test.MDF’, MOVE ‘test_log’ TO ‘c:\Backup\test_Log.LDF’
7. Click Execute (the messages regarding your backup restore will be displayed).
5) Full Backup Scheduling
To create a Backup scheduling, you will need to use the SQL Server Agent, which is a Microsoft Windows service that lets you automate a few administrative tasks. It executes jobs, monitors the SQL Server, and processes alerts. The SQL Server Agent must be running so that local or multi-server administrative jobs can be executed automatically.
To schedule a Full Backup of a certain database every day at 01:00 AM, follow the guidelines below:
1. Right-click Jobs folder, right below the SQL Server Agent, and select New Job.
Figure 3: Job
2. Go to New Job, General tab, and type Backup Full at Name.
Figure 4: New Job
3. On Steps tab, click New.
Figure 5: Steps tab
4. On New Job Step window, type Backup Full at Step Name.
Figure 6: New Job Step
5. In Command field, write:
BACKUPDATABASE DBNAME TO DISK = ‘C:\BACKUP\DBNAME-FULL.BAK’ WITH INIT
The INIT option overwrites the previous file; if it’s not used, the destination file will be appended to the previous file.
6. Click OK.
7. On New Job window, Schedules tab, click New.
8. On New Job Schedule window, write Backup Full at Name.
Figure 7: New Job Schedule
9. In Frequency, go to Occurs option and pick Daily.
10. In Daily Frequency, go to Occurs once at option and pick 01:00:00
11. Click OK twice.
12. Go to SQL Server Agent | Jobs folder and test the Full Backup scheduling by right-clicking the object and selecting Start At.
Figure 8: Start Jobs
NOTE: the SQL Server Express versions do not support SQL Server Agent. To check the SQL Server version, execute the command select @@version.
- Structure Query Language (SQL): Chapter 1 – Manipulating information in the DB.
- Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs.
- Structure Query Language (SQL): Chapter 3 – Data Discard and DB Limits.
- Structure Query Language (SQL): Chapter 4 – Backup.
- Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries.
- Structure Query Language (SQL): Chapter 6 – SQL Commands.
- Structure Query Language (SQL): Chapter 7 – Views.
- Structure Query Language (SQL): Chapter 8 – Triggers.
- Structure Query Language (SQL): Chapter 9 – Stored Procedures.