Backup the Database

The first step in safeguarding your data is to create a backup. Here’s a simple T-SQL script to back up a database named hotel_01_02.

--------------------- Script Backup Database -------------------------
BACKUP DATABASE [hotel_01_02]
TO DISK = 'D:\hotel_01_02.bak'
WITH FORMAT,
     MEDIANAME = 'SQLServerBackups',
     NAME = 'Full Backup of hotel_01_02';

Explanation

Restore the Database

Once you have a backup, you can restore it whenever necessary. Here’s a script to restore the database from the backup we created.

--------------------- Script Restore Database -------------------------

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'hotel')
BEGIN
    DROP DATABASE [hotel];
END

RESTORE DATABASE hotel 
FROM DISK = 'D:\hotel_01_02.bak'
WITH MOVE 'hotel_01_02' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\hotel.mdf',
     MOVE 'hotel_01_02_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\hotel.ldf';

Explanation