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)
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..
Thanks for adding your comment eelcodrost.
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.
I generally look at profiler to find the source of the error.
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.
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.
Small correction, the DBMS is SQL Server 2012 Standard.
I was expecting some feedback by now…