Backing up and restoring databases is a critical aspect of database administration. Regular backups ensure data availability and protect against data loss due to hardware failures, software corruption, or human error. SQL Server provides robust mechanisms for both creating backups and restoring databases to a previous state.
SQL Server supports several types of backups, each serving a different purpose in your data protection strategy.
A full backup contains all the data in the database at the time the backup was completed. It also contains enough transaction log information to recover the database to that point in time. A full backup is the foundation for all other backup types.
A differential backup backs up only the data that has changed since the last full backup. This significantly reduces backup time and storage space compared to full backups, but requires the last full backup to be present for a restore.
Transaction log backups capture all the transaction log records that have not yet been backed up. These are essential for achieving point-in-time recovery and are only available for databases in the Full or Bulk-Logged recovery model.
You can create backups using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) commands.
Using SSMS: Navigate to the database, right-click, select Tasks > Back Up. Configure the backup type, destination, and options.
Using T-SQL:
-- Full Backup
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Full.bak'
WITH COMPRESSION, STATS = 10;
-- Differential Backup
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;
-- Transaction Log Backup
BACKUP LOG [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName_Log.trn'
WITH COMPRESSION, STATS = 10;
COMPRESSION: Compresses the backup, reducing file size.STATS: Displays progress messages at specified intervals.DIFFERENTIAL: Specifies a differential backup.INIT / NOINIT: Overwrites or appends to existing backup sets.NAME: Provides a name for the backup set.DESCRIPTION: Adds a description to the backup set.For a complete list of options, refer to the BACKUP Transact-SQL statement documentation.
Restoring a database involves recovering it from one or more backup files.
Using SSMS: Right-click Databases, select Restore Database. Choose the backup source and configure restore options.
Using T-SQL:
-- Restore a Full Backup
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak'
WITH REPLACE, RECOVERY, STATS = 10;
-- Restore a Differential Backup (after a full backup)
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\Backup\YourDatabaseName_Diff.bak'
WITH REPLACE, RECOVERY, STATS = 10;
-- Restore Transaction Log Backups (after full and differential backups)
RESTORE LOG [YourDatabaseName]
FROM DISK = 'C:\Backup\YourDatabaseName_Log1.trn'
WITH RECOVERY, STATS = 10;
RESTORE LOG [YourDatabaseName]
FROM DISK = 'C:\Backup\YourDatabaseName_Log2.trn'
WITH RECOVERY, STATS = 10;
REPLACE: Overwrites an existing database with the same name.RECOVERY: Leaves the database ready for use after restore (default).NORECOVERY: Leaves the database in a restoring state, allowing further log restores.STANDBY = <undo_file_name>: Leaves the database ready to restore further logs, but also allows read-only access.MOVE 'LogicalFileName' TO 'PhysicalFileName': Moves the data or log file to a new location.For a comprehensive list of options, see the RESTORE Transact-SQL statement documentation.
To perform a point-in-time restore, you need a full backup, at least one differential backup (optional but recommended for performance), and a sequence of transaction log backups. You restore the full backup with NORECOVERY, then the differential backup with NORECOVERY (if used), and finally the transaction log backups up to the desired point in time using the STOPAT clause.
-- Example of Point-in-Time Restore
RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak' WITH NORECOVERY;
RESTORE LOG [YourDatabaseName] FROM DISK = 'C:\Backup\YourDatabaseName_Log1.trn' WITH NORECOVERY;
RESTORE LOG [YourDatabaseName] FROM DISK = 'C:\Backup\YourDatabaseName_Log2.trn' WITH NORECOVERY;
RESTORE LOG [YourDatabaseName] FROM DISK = 'C:\Backup\YourDatabaseName_Log3.trn' WITH STOPAT = '2023-10-27 10:30:00', RECOVERY;