Description: Following are the steps to perform restore via different scenarios.
1. Create a Database for testing:
CREATE DATABASE [BANK_DATABASE]
ON PRIMARY
( NAME = N’BANK_DATABASE’, FILENAME = N’D:\MSSQL\SQL_HOME\SQL_2017\db_home1\user_db\BANK_DATABASE.mdf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [ACCOUNTS_FILEGROUP]
( NAME = N’ACCOUNTS_FILE’, FILENAME = N’D:\MSSQL\SQL_HOME\SQL_2017\db_home1\user_db\ACCOUNTS_FILE.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP [INSURANCE_FILEGROUP]
( NAME = N’INSURANCE_FILE’, FILENAME = N’D:\MSSQL\SQL_HOME\SQL_2017\db_home1\user_db\INSURANCE_FILE.ndf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N’BANK_DATABASE_log’, FILENAME = N’D:\MSSQL\SQL_HOME\SQL_2017\db_home1\user_log\BANK_DATABASE_log.ldf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
USE BANK_DATABASE
GO
2. Create table under this new database.
CREATE TABLE tblACCOUNTS (AccID int, AccStatus bit, AccBal float) on ACCOUNTS_FILEGROUP
INSERT INTO tblACCOUNTS DEFAULT VALUES
CREATE TABLE tblINSURANCE(InsrID int, InsrStatus bit, InsrBal float) on INSURANCE_FILEGROUP
INSERT INTO tblINSURANCE DEFAULT VALUES
3. Insert Data
INSERT INTO tblINSURANCE DEFAULT VALUES
INSERT INTO tblACCOUNTS DEFAULT VALUES
INSERT INTO tblINSURANCE DEFAULT VALUES
INSERT INTO tblACCOUNTS DEFAULT VALUES
INSERT INTO tblINSURANCE DEFAULT VALUES
INSERT INTO tblACCOUNTS DEFAULT VALUES
SELECT COUNT(*) FROM tblACCOUNTS
SELECT COUNT(*) FROM tblINSURANCE
4. Backup database
BACKUP DATABASE [BANK_DATABASE] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’ with FORMAT
INSERT INTO tblINSURANCE DEFAULT VALUES
INSERT INTO tblACCOUNTS DEFAULT VALUES
BACKUP DATABASE [BANK_DATABASE] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_1_Backup.BAK’ with FORMAT,DIFFERENTIAL
INSERT INTO tblINSURANCE DEFAULT VALUES
INSERT INTO tblACCOUNTS DEFAULT VALUES
BACKUP DATABASE [BANK_DATABASE] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_2_Backup.BAK’ with FORMAT,DIFFERENTIAL
INSERT INTO tblINSURANCE DEFAULT VALUES
INSERT INTO tblACCOUNTS DEFAULT VALUES
BACKUP DATABASE [BANK_DATABASE] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_3_Backup.BAK’ with FORMAT,DIFFERENTIAL
BACKUP LOG [BANK_DATABASE] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\LogBackup.trn’ with FORMAT
6. ASSUME DATABASE CRASHED.
USE MASTER
GO
DROP DATABASE [BANK_DATABASE]
REQUIREMENT 1: HOW TO RESTORE (RECOVER) THE COMPLETE DATABASE ?
— STEP 1: VERIFY THE BACKUP FILE VALIDITY [BACKUP HEALTH CHECK]
RESTORE VERIFYONLY FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’
RESTORE VERIFYONLY FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_2_Backup.BAK’
RESTORE VERIFYONLY FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\LogBackup.trn’
— STEP 2: VERIFY THE BACKUP FILE RESTORE INFORMATION
— means, to check where exactly the data files and log files are to be created?
RESTORE FILELISTONLY FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’
— STEP 3: PERFORM THE ACTUAL RESTORE
RESTORE DATABASE [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’ with NORECOVERY
RESTORE DATABASE [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_2_Backup.BAK’ with NORECOVERY
RESTORE LOG [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\LogBackup.trn’ with RECOVERY
USE [BANK_DATABASE]
SELECT COUNT(*) FROM tblACCOUNTS
SELECT COUNT(*) FROM tblINSURANCE
ASSUME DATABASE CRASHED.
USE MASTER
GO
DROP DATABASE [BANK_DATABASE]
REQUIREMENT 2: HOW TO RESTORE (RECOVER) ONLY THE ACCOUNTS DATA ASAP ?
— MEANS, I WOULD LIKE TO RESTORE ONLY A PART OF THE DATABASE.
— THIS IS CALLED “PARTIAL RESTORE” ALSO CALLED “PIECE-MEAL RESTORE”.
RESTORE DATABASE [BANK_DATABASE]
FILEGROUP = ‘PRIMARY’ FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’ with NORECOVERY, PARTIAL
RESTORE DATABASE [BANK_DATABASE]
FILEGROUP = ‘ACCOUNTS_FILEGROUP’ FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’ with NORECOVERY
RESTORE DATABASE [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_2_Backup.BAK’ with NORECOVERY
RESTORE LOG [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\LogBackup.trn’ with RECOVERY
USE [BANK_DATABASE]
SELECT COUNT(*) FROM tblACCOUNTS
SELECT COUNT(*) FROM tblINSURANCE — ERROR
— HOW TO RESTORE THE REMAINING PART OF THE DATABASE ?
— DATABASE IS CURRENTLY ONLINE. SO, IT IS NOT READY FOR FURTHER RESTORES.
— HENCE WE NEED TO CONVERT THE ONLINE DATABASE TO “RESTORING” STATE.
— THIS IS POSSIBLE BY USING “TAIL LOG BACKUPS”.
USE MASTER
GO
BACKUP LOG [BANK_DATABASE] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Backup_tail.trn’ with FORMAT, NORECOVERY
— To convert online database to restoring state : use BACKUP statement with NORECOVERY
— To convert restoring database to online state : use RESTORE statement with RECOVERY
RESTORE DATABASE [BANK_DATABASE]
FILEGROUP = ‘INSURANCE_FILEGROUP’ FROM DISK =’D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\FullBackup.BAK’ with NORECOVERY
RESTORE DATABASE [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Diff_2_Backup.BAK’ with NORECOVERY
RESTORE LOG [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\LogBackup.trn’ with NORECOVERY
RESTORE LOG [BANK_DATABASE] FROM DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\RESTORE\Backup_tail.trn’ with RECOVERY
USE [BANK_DATABASE]
SELECT COUNT(*) FROM tblACCOUNTS
SELECT COUNT(*) FROM tblINSURANCE — NO ERROR