• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MS SQL Server/Restoring Backup via Scripts
Popular Search:Oracle, SQL Server, MongoDB

Restoring Backup via Scripts

70 views 0 September 7, 2019 admin

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

Tags:res

Was this helpful?

Yes  No
Related Articles
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Backup using T-SQL
  • Steps to Take Backup from GUI in SQL Server
Leave A Comment Cancel reply

MS SQL Server
  • Restoring Backup via Scripts
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Backup using T-SQL
View All 20  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org