SQL Server Backup Restore

SQL Server supports three kinds of backup methods

• Full backup;
• Differential backup
• Transaction log Backups

The general backup method is full backup/weekly, differential backup/daily, transaction log backup/per minute, which ensures the efficiency and recoverability of the backup.

1. Full backup

• Backup scripts

BACKUP DATABASE  to DISK = ' D:\Company\Data\SQL Server\backup\pct.bak ';

• Restoring a backup

RESTORE DATABASE PCT  from DISK = ' D:\Company\Data\SQL Server\backup\pct.bak '  with RECOVERY;

2. Differential backup

• Backup scripts

BACKUP DATABASE  to DISK = ' D:\Company\Data\SQL Server\backup\pct_differential.bak '  with differential; GO

• Restoring a backup

To restore a differential backup in SQL Server, you need to first restore a full backup before the differential backup point in time, and add the NORECOVERY parameter when restoring the full backup with the following SQL statement:

RESTORE DATABASE  from  DISK='D:\Company\Data\SQL server\backup\pct.bak '  with  FILE = 1 ,  Nounload,  =ten, NORECOVERYGO

When restoring a differential backup, if only one differential backup file needs to be restored without using the NORECOVERY parameter, the SQL statement is as follows:

RESTORE DATABASE  from   DISK='D:\Company\Data\SQL server\backup\pct_ Differential.bak'  withFILE=1,  Nounload,  =tenGO

If there are multiple differential backup files that need to be restored, except for the last differential backup file, all others need to be added with the NORECOVERY parameter

Transaction log Backups

• Transaction log Backups

BACKUP LOG  to DISK = ' D:\Company\Data\SQL Server\backup\pct_log.bak ';

• Tail-Log backup

BACKUP LOG  to DISK = ' D:\Company\Data\SQL Server\backup\pct_taillog.bak '  with NORECOVERY;

• Full recovery to point of failure

 UseMaster;BACKUP DATABASEPCT to DISK ='D:\Company\Data\SQL Server\backup\pct.bak' withINIT;GO--Perform A transaction log backup of the Test databaseBACKUP LogPCT to DISK ='D:\Company\Data\SQL Server\backup\pct_log.bak' withINIT;GO--...... <failure occurs here>--Back up the tail of the log to prepare for restoreBACKUP LogPCT to DISK ='D:\Company\Data\SQL Server\backup\pct_taillog.bak' withNORECOVERY, INIT;GO--Restore The full backupRESTORE DATABASEPCT from DISK = 'D:\Company\Data\SQL Server\backup\pct.bak' withNORECOVERY;--Apply The transaction log backupRESTORE LOGPCT from DISK = 'D:\Company\Data\SQL Server\backup\pct_log.bak' withNORECOVERY;--Apply the tail log backupRESTORE LOGPCT from DISK = 'D:\Company\Data\SQL Server\backup\pct_taillog.bak' withNORECOVERY;--Recover the databaseRESTORE DATABASEPCT withRECOVERY;GO

• Restore to the point in time of the last correct log backup

--Full BACKUP at 9:00am UseMaster;BACKUP DATABASEPCT to DISK = 'D:\Company\Data\SQL Server\backup\pct.bak' withINIT;GO--LOG BACKUP 1 at 2:15 AM UseMaster;BACKUP LOGPCT to DISK = 'D:\Company\Data\SQL Server\backup\pct_log.bak' withINIT;GO--LOG BACKUP 2 at AM UseMaster;BACKUP LOGPCT to DISK = 'D:\Company\Data\SQL Server\backup\pct_log2.bak' withINIT;GO--It is sometimes regrettable that full recovery cannot be performed: for example, because of a disaster the current transaction log is not available. In this case, we will need to restore the database to the end of the recent log backup--If a catastrophic failure occurs at 2:30, we will need to restore the tail-log backup status of the database to 2:30. --since we cannot make a tail-log backup, we can only revert to a specific point and we will use the STOPAT option. --RESTORE Full BackupRESTORE DATABASEPCT from DISK = 'D:\Company\Data\SQL Server\backup\pct.bak' withNORECOVERY;--RESTORE Log file 1RESTORE LOGPCT from DISK = 'D:\Company\Data\SQL Server\backup\pct_log.bak' withNORECOVERY, STOPAT= 'Jan, 2020';--RESTORE Log File 2RESTORE LOGPCT from DISK = 'D:\Company\Data\SQL Server\backup\pct_log2.bak' withNORECOVERY, STOPAT= 'Jan, 2020';--Recover the databaseRESTORE DATABASEPCT withRECOVERY;GO

SQL Server Backup Restore