CODEWITHSUNDEEP
sql-servervisual-studio
Jon
Jon

Reputation: 40032

Restoring SQL2012 to LocalDb

I am trying to restore a *.bak file from a SQL 2012 DB and am having a lot of trouble. There appears to be similar questions that are unanswered so I'm hoping the below elaborates on the issue.

Use the below to get the logical names for the database and log

RESTORE FILELISTONLY 
FROM disk = 'C:\Users\jonathan\Desktop\mydb_Full.bak'

Run the below to restore

RESTORE DATABASE mydb
   FROM disk = 'C:\Users\jonathan\Desktop\mydb_Full.bak'
   WITH  RECOVERY, 
      MOVE 'mydb' TO 
'C:\Users\jonathan\mydb.mdf', 
      MOVE 'mydb_log' 
TO 'C:\Users\jonathan\mydb_log.ldf'

Get the error:

Processed 448 pages for database 'mydb', file 'mydb' on file 1.
Processed 2 pages for database 'mydb', file 'mydb_log' on file 1.
Msg 1853, Level 16, State 1, Line 5
The logical database file 'mydb_log' cannot be found. Specify the full path for the file.
Msg 3167, Level 16, State 1, Line 5
RESTORE could not start database 'mydb'.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Suggestion from SO/Google is to try and detach then attach, the attach may initiate a upgrade process of some sort.

exec sp_detach_db mydb, 'true'

Error:

Msg 3707, Level 16, State 2, Line 17
Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

Blogs suggest setting it to emergency mode:

ALTER DATABASE smsr4000 SET EMERGENCY;

This results in

Msg 823, Level 24, State 1, Line 20
The operating system returned error 5(Access is denied.) to SQL Server during a read at offset 0x00000000012000 in file 'C:\Users\jonathanchannon\mydb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

So now I'm lost. How can I get this sql backup into LocalDB?

Thanks

UPDATE: There is an open MS issue here on connect and this blog post also look promising but when using sqlcmd to connect to an instance I get and error:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2].

UPDATE 2: Finally gave up and installed SQL Express and Management Studio, worked first time.

Upvotes: 1

Views: 2160

Answers (2)

MikeDev
MikeDev

Reputation: 377

I ran into a similar issue and did the following to resolve it.

Steps I took:

  • restore from the backup (BAK) file to localdb

  • see where the mdf and ldf files went to (probably went to c:\users\yourid)

  • create new folders in a new area like C:\sql-data-files\data and C:\sql-data-files\log

  • copy the mdf to the data folder and ldf to the log folder

  • delete the new database that just appeared in your Databases list as a result of the above restore

  • then run this script in SQL Server Management Studio

    CREATE DATABASE MyDatabase ON (FILENAME = 'C:\sql-data-files\data\MyDatabase.mdf'), (FILENAME = 'C:\sql-data-files\log\MyDatabase.ldf') FOR ATTACH;

All of the above permitted me to bypass the many steps other sites were recommending.

Upvotes: 0

Johnny Kauffman
Johnny Kauffman

Reputation: 3683

I ran into the same issue when trying to script our use of LocalDB. However, there is one small detail that got me out of this rut.

First, you should attempt to restore the database and supply the new filenames (using the command RESTORE DATABASE mydb... ). After this, the correct MDF and LDF are mysteriously available in the proper location, despite the error.

After this, you have a few options. The first problem is that SQL will show the database stuck in a "Restoring..." state. This could be solved a few ways, probably. In my solution, I delete and recreate my LocalDB instance using the SqlLocalDb utility. If that's too heavy-handed, you could probably copy the MDF and LDF files, then drop the database instead. This command line stops, drops, and recreates the LocalDB instance named "v11.0":

sqllocaldb p v11.0 && sqllocaldb d v11.0 && sqllocaldb c v11.0 -s

At that point, I had an MDF and LDF that I could use. So, I used this to force it across the finish line:

CREATE DATABASE X ON (Name=X, Filename=X) LOG ON (Name=X, Filename=X) FOR ATTACH;

Upvotes: 1

Related Questions