You should always have proper backup plan in place to protect your database from failures. SQLServer have four types of backups
1. Full Database backup – Complete database backup at the time of backup.
2. Transaction Log backup – Backups up the transaction log file
3. Differential backup– Backs up the parts of the database that is changed since the last Full Database backup
4. File and file group backup – Backs up the database files that you specify in the FILE or FILEGROUP option.
In SQLServer either you can mention the backup path or you can create a backup device use the device name during the backup. You can either use T-SQL or SQLServer Management Studio to backup SQLServer Database.
Full Database backup: You can backup whole database, this includes part of transaction log which is needed to recover the database using full backup.
BACKUP DATABASE DB_Name
TO Backup_Device
WITH FORMAT;
GO
or
BACKUP DATABASE DB_Name
TO DISK = ‘D:\SQLBackup\FullBackup\DB_Name.bak’
GO
Transaction Log backup: You must backup the transaction log, if SQLServer database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full. Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.
BACKUP LOG DB_Name
TO Backup_Device
GO
or
BACKUP LOG DB_Name
TO DISK = ‘D:\SQLBackup\LogBackup\DB_Name.trn’
GO
If transaction log is full users will receive error “Log files are running out of space”
Differential backup:- The database must have full back up in order to take a differential backup, it only backups the changes since last full backup.
BACKUP DATABASE DB_Name
TO Backup_Device WITH DIFFERENTIAL
GO
or
BACKUP DATABASE DB_Name
TO DISK = ‘D:\SQLBackup\Differ\DB_Name.bak’ WITH DIFFERENTIAL
GO
Full/Differential/Transaction Log Backup using SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential" or” Transaction Logl”
• Select the appropriate Backup Destination and click "OK"
File and file group backup:- By default each database has PRIMARY file group which is tied to one data file. You can create additional filegroups and add data files to filegroup. You can perform both FILE and FILEGROUP backups.
BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO Backup_Device
GO
or
BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO DISK = ‘D:\SQLBackup\Filegroup\DB_Name.bak’
GO
File or File Groups Backup using SQL Server Management Studio• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential"
• Select Backup component as "Files and filegroups"
• Select the appropriate filegroup
• Select the appropriate Backup Destination and click "OK"
Note that from SQLServer 2008 Enterprise edition and later supports backup compression.
Restrictions on Backup Operation1. You cannot backup offline Database
2. You cannot run two backups concurrently
Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com
Posts
