KEMBAR78
Backup and Recovery Lab Practice | PDF
Scribd
Upload
25 views2 pages

Backup and Recovery Lab Practice

The document provides a practical guide for performing SQL Server database backup and restore operations using a sample database named 'StudentDB' with three tables. It includes step-by-step instructions for creating the database, changing the recovery model, backing up and restoring the database, copying, scripting, detaching, and attaching the database using both SSMS and T-SQL. This comprehensive approach ensures users can effectively manage their SQL Server databases.

Uploaded by

ITACHI UCHIHA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
25 views2 pages

Backup and Recovery Lab Practice

The document provides a practical guide for performing SQL Server database backup and restore operations using a sample database named 'StudentDB' with three tables. It includes step-by-step instructions for creating the database, changing the recovery model, backing up and restoring the database, copying, scripting, detaching, and attaching the database using both SSMS and T-SQL. This comprehensive approach ensures users can effectively manage their SQL Server databases.

Uploaded by

ITACHI UCHIHA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Practical Lab Exercises on SQL Server Database Backup and Restore Operations

Database Overview
Create a simple database named 'StudentDB' with three tables:
- Students (StudentID, FirstName, LastName, Department)
- Courses (CourseID, CourseName, Credits)
- Enrollments (EnrollmentID, StudentID, CourseID, Grade)

1. Create the Database and Tables


Use T-SQL or SSMS to create the 'StudentDB' database and the three tables.

2. Change Recovery Model


a. Using SSMS
1. Right-click 'StudentDB' > Properties > Options
2. Change Recovery Model to Full, Simple, or Bulk-Logged
b. Using T-SQL

ALTER DATABASE StudentDB SET RECOVERY SIMPLE;


ALTER DATABASE StudentDB SET RECOVERY FULL;
ALTER DATABASE StudentDB SET RECOVERY BULK_LOGGED;

3. Backup the Database


a. Using SSMS Wizard
1. Right-click 'StudentDB' > Tasks > Back Up...
2. Choose backup type: Full
3. Select destination and click OK
b. Using T-SQL

BACKUP DATABASE StudentDB TO DISK = 'C:\Backups\StudentDB.bak' WITH FORMAT;

4. Restore the Database


a. Using SSMS Wizard
1. Right-click 'Databases' > Restore Database...
2. Choose 'Device' > Browse > Add backup file
3. Select the backup and restore
b. Using T-SQL

RESTORE DATABASE StudentDB FROM DISK = 'C:\Backups\StudentDB.bak' WITH


REPLACE;
5. Copy the Database
a. Using SSMS Wizard
1. Right-click 'StudentDB' > Tasks > Copy Database
2. Use SQL Server Object method or detach/attach
3. Follow the wizard to create 'StudentDB_Copy'

6. Script the Database


a. Using SSMS
1. Right-click 'StudentDB' > Tasks > Generate Scripts
2. Choose objects and save script to file

7. Detach the Database


a. Using SSMS

1. Right-click 'StudentDB' > Tasks > Detach


2. Select options and click OK

b. Using T-SQL

USE master;
EXEC sp_detach_db 'StudentDB';

8. Attach the Database


a. Using SSMS
1. Right-click 'Databases' > Attach
2. Browse and select .mdf file
b. Using T-SQL

USE master;
CREATE DATABASE StudentDB ON
(FILENAME = 'C:\Data\StudentDB.mdf'),
(FILENAME = 'C:\Data\StudentDB_log.ldf')
FOR ATTACH;

You might also like