GO -- Get Backup History for required database SELECT TOP 100 s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id =m.media_set_id WHERE s.database_name = DB_NAME() -- Remove this line for all the database ORDER BY backup_start_date DESC, backup_finish_date GO
Tag Archives: Backup types
Types of SQL Server Backup -1 : FULL Backup
The most common types of SQL Server backups are complete or full backups, also known as database backups. These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.
A full backup can be completed either using T-SQL or by using SSMS
Create a full backup of the AdventureWorks database to one disk file
T-SQL
BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK' GO |
SQL Server Management Studio
- Right click on the database name
- Select Tasks > Backup
- Select “Full” as the backup type
- Select “Disk” as the destination
- Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” an click “OK”
- Click “OK” again to create the backup
SQL Server Backup Types
The different types of backups that you can create are as follows:
- Full backups
- Differential backups
- File backups
- File group backups
- Partial backups
- Copy-Only backups
- Mirror backups
- Transaction log backups
we will discuss one by one in detail in next posts.