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)