SQL Server Backup Restore

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.