Backup and Restore SQL Server Databases Using T-SQL Scripts

In this blog, I will guide you through the process of backing up and restoring a SQL Server database using T-SQL scripts. This can be particularly useful for database administrators and developers who need to ensure data integrity and availability.

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

• BACKUP DATABASE [hotel_01_02]: Specifies the database to back up.
• TO DISK = 'D:\hotel_01_02.bak': The location and filename of the backup file.
• WITH FORMAT: Creates a new media set.
• MEDIANAME = 'SQLServerBackups': Names the media set.
• NAME = 'Full Backup of hotel_01_02': Provides a description of the backup.

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

• IF EXISTS (SELECT * FROM sys. databases WHERE name = 'hotel'): Checks if the database already exists.
• BEGIN DROP DATABASE [hotel]; END: Drops the existing database if it exists.
• RESTORE DATABASE hotel FROM DISK = 'D:\hotel_01_02.bak': Specifies the backup file to restore from.
• WITH MOVE 'hotel_01_02' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\hotel.MDF: Relocates the main database file.
• MOVE 'hotel_01_02_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\hotel.pdf: Relocates the log file.