Interview Question of the Week #012 – Steps to Restore Bak File to Database

Here is the question, I was asked the other day at the conference.

Question: If I have a .bak file and I want to restore it database what are the various steps involved in it. Assume that I do not know much about that backup file.

Answer: I love this question, as it has a clear need, but there is ambiguity in the process.

Step 1: Retrieve the logical file name of the database

Step 2: Bring database to single user

Step 3: Restore database with the help of the logical file name retrieved earlier.

Step 4: Bring database to multi user mode.

Here are all the four steps together:

----Retrieve logical file name
RESTORE FILELISTONLY
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
GO
----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\DataYourLDFFile.ldf'
GO
----Make Database to multi user Mode
ALTER DATABASE YourDB SET MULTI_USER
GO

Here is the blog post I wrote earlier, which describes the same process SQL SERVER – Restore Database Backup using SQL Script (T-SQL).

Reference: Pinal Dave (https://blog.sqlauthority.com)