Structure Query Language (SQL): Chapter 4 - Backup.

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:

Screen.Item(“Query1”).Execute()

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:

RESTORE FILELISTONLY
FROM DISK = ‘c:\Backup\testsql.bak’
GO

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:

RESTORE DATABASE test
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.