How to Backup and Restore a SQL Server Database Using Scripts

 If you're managing SQL Server databases, it's crucial to know how to create database backups and restore them properly using T-SQL scripts. In this blog, we’ll walk you through the step-by-step process using a practical example: backing up the database db_Name_phase3_v5 and restoring it as a new database named db_Name_phase3_v6.

---

✅ Step 1: Backup the Existing Database

Before restoring a database, we need to take a backup of the source database.

1. BACKUP DATABASE db_Name_phase3_v5  
2. TO DISK = 'D:\MSSQL\db_Name_phase3_v5.bak';  

📌 This command creates a .bak file of the db_Name_phase3_v5 database and stores it in the D:\MSSQL\ directory.

---

🔍 Step 2: Check Logical File Names

Before restoring a backup to a new database name, it’s essential to know the logical file names used in the original database. This helps in mapping them properly during the restore.

1. RESTORE FILELISTONLY  
2. FROM DISK = 'D:\MSSQL\db_Name_phase3_v5.bak';  

📋 This query returns a list of logical names of the data and log files inside the backup. You’ll need this for the MOVE statement in the next step.

In this example, let's assume the logical names are:

• db_Name_phase3_v2 (for .mdf)

• db_Name_phase3_v2_log (for .ldf)

---

🔁 Step 3: Restore the Database with a New Name

Now, restore the backup to a new database name (db_Name_phase3_v6), using the logical file names and specifying new physical file paths.

RESTORE DATABASE db_Name_phase3_v6  
FROM DISK = 'D:\MSSQL\db_Name_phase3_v5.bak'  
WITH   
   MOVE 'db_Name_phase3_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\db_Name_phase3_v6.mdf',  
   MOVE 'db_Name_phase3_v2_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\db_Name_phase3_v6_log.ldf',  
   REPLACE;  

🛠️ Explanation:

• MOVE allows you to relocate the data/log files.

• REPLACE will overwrite the existing database if one with the same name exists.

---

🧠 Tips & Best Practices

• Always verify the destination paths and available space.

• Keep backups on a different drive or network location to avoid data loss in case of disk failure.

• Use WITH STATS = 10 during backup/restore for progress tracking.