Skip to main content
Our Tech Ideas

 Is it possible to restore the database except specific tables in MSSQL

Story of innocent DBA

Once upon a time, there was a innocent Database Administrator (DBA) who had to restore a database but leave out certain tables. However, he didn’t know how to do it. So, he asked for help in a WhatsApp group for SQL DBAs. Someone in the group gave him a script to use for the restoration.

The script looked like this:

T-SQL
RESTORE DATABASE <database_name>
FROM DISK = '<backup_file_path>'
WITH MOVE '<logical_data_file_name>' TO '<physical_data_file_path>',
MOVE '<logical_log_file_name>' TO '<physical_log_file_path>',
EXCLUDE = '<excluded_objects>';
https://ourtechideas.com/

The innocent DBA tried to follow the script but didn’t have any success.

He began by attempting the same process on a test server, following these steps:

  • Created a database.
  • Generated several tables.
T-SQL
-- Create the 'sanjay' database if it doesn't already exist
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'sanjay')
BEGIN
    CREATE DATABASE sanjay;
    PRINT 'Database created: sanjay';
END
ELSE
BEGIN
    PRINT 'Database already exists: sanjay';
END

-- Switch to the 'sanjay' database
USE sanjay;

-- Create tables
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    ContactName VARCHAR(50),
    Address VARCHAR(100),
    City VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert demo data into the 'Customers' table
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City)
VALUES
    (1, 'John Doe', 'John', '123 Main St', 'New York'),
    (2, 'Jane Smith', 'Jane', '456 Elm St', 'Los Angeles'),
    (3, 'Bob Johnson', 'Bob', '789 Oak St', 'Chicago');

-- Insert demo data into the 'Orders' table
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (1, 1, '2023-05-01', 100.50),
    (2, 1, '2023-05-05', 75.25),
    (3, 2, '2023-05-10', 200.00),
    (4, 3, '2023-05-15', 50.75);

PRINT 'Tables created: Customers, Orders';
PRINT 'Demo data inserted into tables';
https://ourtechideas.com/
  • Conducted a full backup.
  • Attempted to restore the backup, excluding one of the tables.
T-SQL
RESTORE DATABASE sanjay_new
FROM DISK = 'K:\BACKUP_DEV\sanjay.bak'
WITH MOVE 'sanjay' TO 'L:\DEV_DATA\sanjay_new.mdf',
MOVE 'sanjay_log' TO 'N:\DEV_LOG\sanjay_log_new.ldf',
NOUNLOAD, REPLACE, STATS = 10,
EXCLUDE = 'dbo.Orders';
https://ourtechideas.com/

Result:

‘EXCLUDE’ is not a recognized RESTORE option.

He then searched on Google and found out that he couldn’t achieve his goal using the script provided. Instead, he had to restore the database first and then delete the specific tables.

In simpler terms, he had to bring back the database as a whole and then remove the tables he didn’t want. This meant following a two-step process: restoring the database first and then deleting the unwanted tables.

With this new understanding, the innocent DBA devised a plan. He used the provided script to restore the entire database without excluding any tables. Once the restoration was done, he carefully identified and deleted the specific tables he wanted to exclude.

SQL
-- Step 1: Take a backup of the original database
BACKUP DATABASE OriginalDB TO DISK = 'C:\Backup\OriginalDB.bak';

-- Step 2: Create an empty database
CREATE DATABASE RestoredDB;

-- Step 3: Restore the database backup into the new database
RESTORE DATABASE RestoredDB FROM DISK = 'C:\Backup\OriginalDB.bak'
WITH MOVE 'OriginalDB' TO 'C:\Data\RestoredDB.mdf',
MOVE 'OriginalDB_Log' TO 'C:\Data\RestoredDB_Log.ldf';

-- Step 4: Exclude specific tables from the restored database
USE RestoredDB;
DROP TABLE Table1, Table2;
SQL

Finally, the innocent DBA succeeded in his task. He had restored the database and removed the tables he didn’t need, thanks to the help he received from the WhatsApp group and his own research.

From that day on, the innocent DBA grew more knowledgeable and skilled in handling complex database restoration tasks. He learned that sometimes the solution to a problem requires a combination of approaches and creative thinking.

And so, the innocent DBA continued his journey in the world of databases, ready to face any challenge that came his way with confidence and determination.

Inspired from: https://dba.stackexchange.com