Table of Contents<\/p>\n\t\t\t\t
- <\/ul>\n\t\t\t\t<\/div>\n
- Full backup contains all database data on the moment the backup has finished, so it doesn\u2019t allow point-in-time restoration. After a successful full backup runs, transaction logs are truncated.<\/li>\n
- SQL Server differential backup<\/a> is based on the most recent full data backup and contains only the data changed since the last full run. It requires less time to perform the back up.<\/li>\n
- Transaction logs (T-Logs) backup<\/a>\u00a0contains every transaction made in SQL Server since the last full or differential backup. Using log backups allows you to create a database backup every hour or even minute - that is important for core business databases.<\/li>\n<\/ul>\n
Please remember that SQL Server 2008 backup and recovery scenarios available are restricted by the database recovery model selected: it controls transaction logs management. There are generally three recovery models: Simple, Full, and Bulk-Logged. If choosing to create a T-Logs backup, you should set the Full recovery model for the given database.<\/p>\n
\n\nFREE WHITEPAPER<\/div>\nSQL Server Backup and Recovery<\/div>\nCheck out the ultimate guide with best practices for MSPs:<\/div>\n![\"CTA\"](\"https:\/\/no-cache.hubspot.com\/cta\/default\/5442029\/9ed03d9c-f81b-4305-917a-03488ba39bf3.png\")
<\/a><\/span><\/span>\n<\/div>\n![\"WP](\"https:\/\/www.msp360.com\/resources\/wp-content\/uploads\/2019\/07\/The-Ultimate-MSP-Guide-to-MS-SQL-Server-Backup-and-Recovery.png;\")
<\/div>\n<\/div>\nNow let\u2019s take a look at how to perform such backup types.<\/p>\n
<\/a>How to Perform Full and Differential Backup of a Database in SQL Server 2008<\/h2>\n
Using Query<\/h3>\n
Easiest way to perform any backup in Microsoft SQL Server is using query language. You should right-click root view in the navigation tree of SQL Server Management Studio, and select New Query. Then paste a script and click Execute on the toolbar.<\/p>\n
Here is the script to create a full database backup:<\/p>\n
USE TestDB; \u00a0\r\nGO\r\nBACKUP DATABASE TestDB\r\nTO full_backups\r\n WITH COMPRESSION, \r\n DESCRIPTION = 'Sample full backup', \r\n NAME = 'Set of FULL backups';\r\n GO\r\n<\/pre>\n
You may notice full_backups as a backup target. It is a logical \u201cbackup device\u201d representing a particular file on the HDD. You need to create one or more \u201cdevices\u201d for any backup type described below. Use this command to create one:<\/p>\n
BACKUP DATABASE TestDB \u00a0\r\n \u00a0\u00a0TO diff_backups \u00a0\r\n \u00a0\u00a0WITH DIFFERENTIAL; \u00a0\r\nGO<\/pre>\n
Add DIFFERENTIAL as an option next to the WITH operator if need to create a SQL Server 2008 differential backup.<\/p>\n
Using SQL Server Management Studio<\/h3>\n
If you prefer GUI, then open SQL Server Management studio, navigate to the target database and choose Tasks - Back Up\u2026 in the context menu:<\/p>\n
![\"SQL](\"https:\/\/www.msp360.com\/resources\/wp-content\/uploads\/2018\/08\/image1.png\")
<\/p>\nIn the Back Up database screen, choose Full as a backup type and click Add at the screen bottom to add a backup device:<\/p>\n
![\"Full](\"https:\/\/www.msp360.com\/resources\/wp-content\/uploads\/2018\/08\/image3-1024x613.png\")
<\/p>\nIf you need to create a SQL Server 2008 differential backup - use Differential in the same drop-down menu. We suggest to use separate backup devices for every backup type. It allows you to manage backup storage more efficiently and simplifies navigation over the set of files.<\/p>\n
After the backup job is completed, you can find resulting data in the Media Contents section of the particular backup device\u2019s context menu.<\/p>\n
Note: you can also automate such tasks using the SQL Server Agent feature - it allows you to create a repetitive task using a backup job dialog or query script\u00a0<\/p><\/blockquote><\/div>\n
To learn more about SQL Server backup automation, check out this article:<\/p>\n
Further reading<\/span> SQL Server Automatic Backup<\/a><\/p>\n
Transaction Logs Backup in SQL Server 2008<\/h2>\n
Before creating the first T-Logs backup, please check if your target database uses the Full Recovery model. Otherwise, your backups can not be consistent since the SQL engine will keep only a few log files at once by automatically deleting the oldest ones.<\/p>\n
Perform transaction logs backup using the query script below:<\/p>\n
BACKUP LOG TestDB \u00a0\r\n \u00a0\u00a0TO logs_backups; \u00a0\r\nGO<\/pre>\n
You can achieve the same using GUI by selecting Transaction Log type in the drop-down menu of the backup dialog seen above:<\/p>\n
![\"How](\"https:\/\/www.msp360.com\/resources\/wp-content\/uploads\/2018\/08\/image2.png\")
<\/p>\nDo not forget to add a separate backup device for the T-Logs backup type.<\/p>\n
Summary<\/h2>\n
Microsoft SQL Server has a flexible backup model, allowing you to keep the balance of time spent to backup and speed of data recovery. In most cases you should create a full backup weekly with a set of daily differential backups. If your company needs a better RTO\/RPO<\/a>, consider using T-Logs backup as well.<\/p>\n
- Transaction logs (T-Logs) backup<\/a>\u00a0contains every transaction made in SQL Server since the last full or differential backup. Using log backups allows you to create a database backup every hour or even minute - that is important for core business databases.<\/li>\n<\/ul>\n
Backup Types Overview<\/h2>\n
Microsoft SQL Server has five backup types supported: full and differential, transaction- and tail-logs backup, and copy backup. But the last two ones are quite specific, so we will shortlist three of them:<\/p>\n
- \n