Database backup and restore are crucial tasks that all database administrators must be familiar with to protect data from loss and recover it when needed. T-SQL or Transact-SQL, a Microsoft extension to the SQL language, comes with inbuilt functions and commands to back up and restore databases. This knowledge is especially essential for those preparing for the DP-300 exam on Administering Microsoft Azure SQL Solutions. This article aims to provide a practical guide on how to perform these tasks using T-SQL.
BACKUP DATABASE USING T-SQL
T-SQL provides the BACKUP DATABASE command for creating backups. Here is a basic syntax of this command:
BACKUP DATABASE database_name
TO DISK = ‘path_to_backup_file’
Here, ‘database_name’ is the name of your database you want to back up, and ‘path_to_backup_file’ is the location where the backup file will be stored. For instance, if you wish to backup a database named ‘SampleDB’ to a file named ‘SampleDB.bak’, you would use:
BACKUP DATABASE SampleDB
TO DISK = ‘C:\backups\SampleDB.bak’
This will create a full backup of SampleDB.
BACKUP OPTIONS
T-SQL also offers several options to customize your database backups. Here are some:
- `WITH DIFFERENTIAL`: This creates a differential backup, which contains only the data that has changed since the last full backup.
- `WITH NORECOVERY`: This leaves the database in a restoring state after a backup.
- `WITH FORMAT`: This option is used to override any existing backup set located in the specified file.
RESTORE DATABASE USING T-SQL
Restoring a database is equally straightforward with the RESTORE DATABASE command. Here is the appropriate syntax:
RESTORE DATABASE database_name
FROM DISK = ‘path_to_backup_file’
Continuing with our previous example, if you wish to restore the ‘SampleDB’ from ‘SampleDB.bak’ you would use:
RESTORE DATABASE SampleDB
FROM DISK = ‘C:\backups\SampleDB.bak’
RESTORE OPTIONS
T-SQL provides several options when restoring databases. Here are some:
- `WITH RECOVERY`: Puts the restored database into a useable state after the restore is complete.
- `WITH NORECOVERY`: Leaves the database in a restoring state. This is useful when applying additional transaction log backups.
- `WITH REPLACE`: Overwrites the existing database with the same name.
Knowing how to backup and restore databases is crucial for any database administrator. It forms an integral part of the DP-300 exam. The ability to use T-SQL commands to perform these tasks makes the process efficient and dependable. Remember always to test your backups and keep them secure to protect your data.
Practice Test
True/False: The BACKUP DATABASE command is used to back up a database in T-SQL.
• True
• False
Answer: True
Explanation: The BACKUP DATABASE command is used in T-SQL to create a full backup of an entire database.
Which of the following is NOT a type of backup in SQL Server?
• a. Full backup
• b. Partial backup
• c. Incremental backup
• d. Differential backup
Answer: c. Incremental backup
Explanation: SQL Server supports full, partial, and differential backups, but it does not support incremental backups.
True/False: BACKUP LOG is the T-SQL command to backup the transaction logs of a database.
• True
• False
Answer: True
Explanation: The BACKUP LOG command is used to take a backup of the transaction logs in a SQL Server database.
When doing a RESTORE, what is the WITH RECOVERY option in T-SQL used for?
• a. To recover the database to the state at the time of the last log backup
• b. To leave the database ready for use by rolling back uncommitted transactions
• c. It’s used to indicate that a differential backup will be restored next
• d. It’s to indicate that an online backup was taken
Answer: b. To leave the database ready for use by rolling back the uncommitted transactions
Explanation: The WITH RECOVERY option is used when restoring a backup to leave the database ready for use by rolling back uncommitted transactions.
True/False: BACKUP DATABASE command can only be used with disk or tape backup devices, and does not support the URL from the Azure Blob Storage Service.
• True
• False
Answer: False
Explanation: From SQL Server 2012 onwards, the BACKUP DATABASE command also supports the URL from the Azure Blob Storage Service along with disk and tape backup devices.
In T-SQL, what command do you use to restore a database from a backup?
• a. RECOVER DATABASE
• b. REPLACE DATABASE
• c. RESTORE DATABASE
• d. RETURN DATABASE
Answer: c. RESTORE DATABASE
Explanation: The RESTORE DATABASE command is used to restore a database from a backup in T-SQL.
True/False: During a T-SQL database restore operation, the NORECOVERY option leaves the database in a restoring state after every restore operation.
• True
• False
Answer: True
Explanation: The NORECOVERY option leaves the database in a restoring state, which means more transaction logs or differential backups can be restored.
What does the REPLACE option in the RESTORE DATABASE command do in T-SQL?
• a. It replaces the existing database with a new one
• b. It replaces the old backup with a new backup
• c. It overwrites existing database files even if the RESTORE DATABASE command runs into issues
• d. None of the above
Answer: a. It replaces the existing database with a new one
Explanation: The REPLACE option is used in the RESTORE DATABASE command to overwrite the existing database files with the files in the backup.
True/False: Differential backup only backs up the changes made since the last full backup.
• True
• False
Answer: True
Explanation: Differential backup only includes changes made since the most recent full backup. It’s quicker and requires less storage than full backup.
What is the T-SQL command to backup a database to Azure Blob Storage Service?
• a. BACKUP TO URL
• b. BACKUP DATABASE TO URL
• c. BACKUP TO AZURE
• d. BACKUP DATABASE TO AZURE
Answer: b. BACKUP DATABASE TO URL
Explanation: The command “BACKUP DATABASE TO URL” is used to backup a database to Azure Blob Storage Service.
Interview Questions
What is T-SQL and how is it related to Azure SQL database backup and restore?
T-SQL (Transact-SQL) is an extension to the SQL language. It is used in Microsoft SQL Server and Azure SQL Database among others. It provides additional features focused on transaction control, exception and error handling, row processing and declared variables.
What command is used to back up a database in T-SQL?
The BACKUP DATABASE command is used to back up a database in T-SQL.
In T-SQL, how can you specify the backup type when backing up a database?
The WITH clause is used to specify the backup type, such as FULL, DIFFERENTIAL, or LOG.
What is the T-SQL command to restore a database?
The RESTORE DATABASE command is used to restore a database in T-SQL.
What is the purpose of the WITH NORECOVERY option in the RESTORE DATABASE command?
The WITH NORECOVERY option leaves the database non-operational but ready to receive further restore transactions.
What is the T-SQL command to retrieve information about the backup set and its contents from the backup device?
The RESTORE HEADERONLY command is used to retrieve information about the backup set and its contents in T-SQL.
What does the WITH REPLACE option do in the RESTORE DATABASE command in T-SQL?
The WITH REPLACE option in the RESTORE DATABASE command causes SQL Server to create the specified database and its files, if they don’t already exist.
What are the main different types of SQL Server backups?
The main types of SQL Server backups are complete backups, differential backups and transaction log backups.
In T-SQL, how can you specify to append the backup to the existing backup sets?
You can specify the backup to append to the existing backup sets by using the WITH NOINIT clause in the BACKUP DATABASE command.
How do you specify the backup media in the BACKUP DATABASE command in T-SQL?
In the BACKUP DATABASE command in T-SQL, you specify the backup media by using the TO DISK or TO TAPE options.
What is the purpose of the CHECKSUM option in the BACKUP DATABASE command in T-SQL?
The CHECKSUM option is used to verify the backup as it is being created. If this option is not provided, it doesn’t verify the backup, which may lead to the backup being corrupted.
What does the FILE option in the RESTORE DATABASE command in T-SQL specify?
The FILE option in the RESTORE DATABASE command allows you to specify the logical name of the file to be restored.
In T-SQL, what does the PAGE restore option do?
The PAGE restore option, in T-SQL, allows you to restore individual pages in the database.
How can you verify your backup in T-SQL?
You can verify your backup by using the RESTORE VERIFYONLY command in T-SQL.
How to use T-SQL to restore an Azure SQL Database to a point in time?
You can use the RESTORE DATABASE command with the POINT IN TIME clause to restore an Azure SQL Database to a specific point in time.
extutorials.com