How to backup and restore Microsoft SQL Server database

If you are someone who works with Microsoft SQL Server a lot, knowing how to backup and restore your SQL Server database is a must required skill. In this tutorial, I will be teaching you two methods to backup and restore SQL Server database.

Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

T-SQL script to generate sample database and sample table.

USE master
GO

IF DB_ID('TestDB') IS NOT NULL
    DROP DATABASE TestDB;
GO

CREATE DATABASE TestDB;
GO

USE TestDB
GO

CREATE TABLE CustomerTable (
    CustomId INT NOT NULL PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(50)
);
GO

INSERT INTO CustomerTable
VALUES
    (1, 'Joe', 'Chicago'),
    (2, 'Jay', 'San Francisco'),
    (3, 'Mary', 'New York'),
    (4, 'Susan', 'Boston'),
    (5, 'Tom', 'Phoenix')
GO


SELECT *
FROM CustomerTable
GO

USE master
GO

T-SQL script to drop backup database.

USE TestDB
GO

BACKUP DATABASE TestDB
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TestDB.bak'
    WITH FORMAT,
    NAME = 'Full Backup of TestDB ', -- Backupname
    MEDIANAME = 'TestDB1', -- Optional. But good to have since helps identify the correct backup set.
    DESCRIPTION = 'This is a backup file of TestDB database'
GO

T-SQL script to restore backup.

RESTORE DATABASE TestDB_v2
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\TestDB.bak';
GO

USE TestDB_v2
GO

SELECT *
FROM CustomerTable;
GO