{"id":3341,"date":"2019-03-25T17:43:50","date_gmt":"2019-03-25T20:43:50","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/structure-query-language-sql-chapter-4-backup\/"},"modified":"2019-05-29T17:21:23","modified_gmt":"2019-05-29T20:21:23","slug":"structure-query-language-sql-chapter-4-backup","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-chapter-4-backup\/","title":{"rendered":"Structure Query Language (SQL): Chapter 4 – Backup."},"content":{"rendered":"
\n

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.<\/p>\n

There are two types of Backup:<\/p>\n

Full<\/b>: 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.<\/p>\n

Differential<\/b>: 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.<\/p>\n

1) Backup via graphic interface <\/b><\/u><\/p>\n

Most of the times, data can be backed up via a graphic interface, as seen below (right-click Database – Tasks – Back Up<\/b>):<\/p>\n

\"\"
\nFigure 1<\/b>: Back Up Database<\/span><\/div>\n

2) Restore Database <\/b><\/u><\/p>\n

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<\/b>):<\/p>\n

\"\"
\nFigure 2:<\/b> Restore Database<\/span><\/div>\n

3) Backup via commands<\/b><\/u><\/p>\n

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.<\/p>\n

For example, a query called Query1 with backup full SQL command to save on C:\\Backup<\/span> folder the ‘test.bak<\/i>‘ backup file:<\/p>\n

BACKUP DATABASE<\/span> [DatabaseName]<\/span> TO DISK<\/span> = ‘c:\\Backup\\test.bak’<\/span><\/span><\/p>\n

After that, you must create a script to run this Query via Execute()<\/i> method. For example, the script below is executed at the Click <\/i>event of a button on screen:<\/p>\n

Screen.Item(“Query1”).Execute()<\/span><\/p>\n

4) Restore via commands<\/b><\/u><\/p>\n

Once the SLQ DB is backed up, to restore the generated .BAK file, follow these guidelines:<\/p>\n

1. Open Management Studio<\/b>.
\n2. Click New Query<\/b> option or File-New-Query with current connection menu<\/b>.
\n3. On the query’s window, type this command:<\/p>\n

RESTORE FILELISTONLY
\nFROM DISK = ‘c:\\Backup\\testsql.bak’<\/span>
\nGO<\/span><\/p>\n

4. Click Execute<\/b>; the result will display the .MDF and .LDF files with each file’s respective logical name.
\n5. Create a new query by repeating step 2.
\n6. Type the following command:
\n
\n<\/span><\/p>\n

R<\/span>ESTORE DATABASE<\/span> test<\/span><\/span><\/span>
\nFROM DISK<\/span> = ‘c:\\Backup\\testsql.bak’<\/span><\/span>
\nWITH MOVE<\/span> ‘teste’ TO <\/span>‘c:\\Backup\\test.MDF’, MOVE <\/span>‘test_log’ TO <\/span>‘c:\\Backup\\test_Log.LDF’<\/span><\/span><\/div>\n

7. Click Execute <\/b>(the messages regarding your backup restore will be displayed).<\/p>\n


\n5) <\/u><\/b>Full <\/u><\/b>Backup Scheduling<\/u><\/b><\/p>\n

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.<\/p>\n

To schedule a Full Backup of a certain database every day at 01:00 AM, follow the guidelines below:<\/p>\n

1. Right-click Jobs folder, right below the SQL Server Agent, and select New Job<\/b>.<\/p>\n

\"\"
\nFigure 3<\/b>: Job<\/span><\/div>\n

2. Go to New Job<\/b>, General <\/b>tab, and type Backup Full<\/i> at Name<\/b>.<\/p>\n

\"\"
\nFigure 4:<\/b> New Job<\/span><\/div>\n

3. On Steps <\/b>tab, click New<\/b>.<\/p>\n

\"\"
\nFigure 5<\/b>: Steps tab<\/span><\/div>\n

4. On New Job Step<\/b> window, type Backup Full<\/i> at Step Name<\/b>.<\/p>\n

\"\"
\nFigure 6<\/b>: New Job Step<\/span><\/div>\n

5. In Command <\/b>field, write:<\/p>\n

BACKUPDATABASE DBNAME <\/span>TO DISK = ‘C:\\BACKUP\\DBNAME-FULL.BAK’<\/span> WITH INIT<\/span><\/span><\/p>\n

The INIT option overwrites the previous file; if it’s not used, the destination file will be appended to the previous file.<\/p>\n

6. Click OK<\/b>.<\/p>\n

7. On New Job<\/b> window, Schedules <\/b>tab, click New<\/b>.<\/p>\n

8. On New Job Schedule<\/b> window, write Backup Full<\/i> at Name<\/b>.<\/p>\n

\"\"
\nFigure 7:<\/b> New Job Schedule<\/span><\/div>\n

9. In Frequency<\/b>, go to Occurs <\/b>option and pick Daily<\/b>.<\/p>\n

10. In Daily Frequency<\/b>, go to Occurs once at<\/b> option and pick 01:00:00<\/b><\/p>\n

11. Click OK <\/b>twice.<\/p>\n

12. Go to SQL Server Agent | Jobs<\/b> folder and test the Full Backup scheduling by right-clicking the object and selecting Start At<\/b>.<\/p>\n

\"\"
\nFigure 8<\/b>: Start Jobs<\/span><\/div>\n

NOTE<\/b>: the SQL Server Express versions do not support SQL Server Agent. To check the SQL Server version, execute the command select <\/span>@@version<\/span>.<\/p>\n<\/div>\n

Related Articles<\/h3>\n
\n