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)

Quest

SQL Scripts
Previous Post
SQL SERVER – Trivia – Days in a Year
Next Post
SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

Related Posts

8 Comments. Leave new

  • there is no need to set the database in multiuser again. The restore proces does that for you..and im missing the “replace” keyword. You need that to overwrite the existing database..

    Reply
  • Now i am getting the mentioned error in DR Server 2012 web edition,Service pack : RTM.
    Initially i have taken the full backup from primary and restored in DR with replace,standby=’dbname.tuf’ it is in sync till threshold(45 mins) limit after that restore job getting succeeded with the following error ” *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
    *** Error: ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed.(System.Data) *** ”

    1.Backup job getting success–Full admin rights given
    2.Copy job getting success—Full rights given to folder
    3.Restore job getting success with above mentioned errors.

    Agent running on same id primary & DR.

    Server is running on Work group not in domain

    I am suspecting that latest service pack will clear this above mentioned error . Correct me if i am wrong..

    Please review the recent post and reply me ASAP.

    Reply
  • Msg 3180, Level 16, State 1, Line 2
    This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    Reply
  • Hi. Thank you for this post. I followed your instructions, but I got stuck in the end with something unexpected.
    I have administrative access to a web server in which I have deployed an ASP.NET application which uses SQL Server for data storage. The DBMS is SQL Server 2014 and it’s based in a different machine in the LAN. All I have to access the DBMS is a SQL user which owns the dbo schema on the database ‘mydb’.
    I can’t execute

    RESTORE FILELISTONLY
    FROM DISK = ‘D:\BackUpYourBaackUpFile.bak’
    GO

    because it gives me the error “CREATE DATABASE permission denied in database ‘master'”.

    I resorted to using “exec sp_helpdb ‘mydb'” to get the logical name of the mdf and the ldf file.
    I needed to restore a backup of ‘MyDb’ taken from a local instance. In order to do this I created a shared folder on the web server, shared with Everyone with Read/Write permissions and I executed the following script

    —-Make Database to single user Mode
    ALTER DATABASE MyDb
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    GO
    —-Restore Database
    RESTORE DATABASE MyDb
    FROM DISK = ‘\\webserver\everyone\mydbbackup.bak’
    With Replace,
    MOVE ‘MyDbMdfLogicalName’ TO ‘D:\Data\MyDb.mdf’,
    MOVE ‘MyDbLdfLogicalName’ TO ‘D:\Data\MyDb_log.ldf’
    GO
    —-Make Database to multi user Mode
    ALTER DATABASE MyDb SET MULTI_USER
    GO

    The script completed successfully, but after it had finished I realized that it had overwritten the user rights on the database locking me out! I had to call the DBA to give me back the access to the db.
    Is there a way to restore a backup without loosing user rights?

    Thank you.

    Reply
  • Small correction, the DBMS is SQL Server 2012 Standard.

    Reply
  • I was expecting some feedback by now…

    Reply

Leave a Reply