Is it possible to restore the database except specific tables in MSSQL
67 views 17:47 0 Comments31 May 2023
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.
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.
-- Step 1: Take a backup of the original databaseBACKUPDATABASE OriginalDB TODISK='C:\Backup\OriginalDB.bak';-- Step 2: Create an empty databaseCREATEDATABASERestoredDB;-- Step 3: Restore the database backup into the new databaseRESTOREDATABASE RestoredDB FROMDISK='C:\Backup\OriginalDB.bak'WITHMOVE'OriginalDB'TO'C:\Data\RestoredDB.mdf',MOVE'OriginalDB_Log'TO'C:\Data\RestoredDB_Log.ldf';-- Step 4: Exclude specific tables from the restored databaseUSE RestoredDB;DROPTABLE Table1, Table2;
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.