How do I copy SQL Server 2012 database to localdb instance?

Question

I'm looking to copy a SQL Server 2012 Standard database to my localdb instance. I've tried the wizard which complains that localdb isn't a SQL Server 2005 or later express instance. I also did a backup/restore but upon the restore in my localdb I get the following error...

Running this...

RESTORE DATABASE CSODev
FROM DISK = 'C:\MyBckDir\CSODev.bak'
WITH MOVE 'CSOdev_Data' TO 'C:\Users\cblair\CSOdev_Data.mdf',
MOVE 'CSOdev_Log' TO 'C:\Users\cblair\CSOdev_Log.ldf',
REPLACE

Error message I get...

Processed 8752 pages for database 'CSODev', file 'CSOdev_Data' on file 1.
Processed 5 pages for database 'CSODev', file 'CSOdev_Log' on file 1.

Msg 1853, Level 16, State 1, Line 1
The logical database file 'CSOdev_Log' cannot be found. Specify the full path for the file.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'CSODev'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The database ends up in "Recovery Pending" mode. It seems like it has issues with the log file. I have tried 2 different backups in case one was just corrupted.

Clearcole

Maybe try putting your files somewhere other than your profile folder. What happens if you move the data/log files to C:\Data\ for example? – Cymogene

I tried doing that and still get the same error message. – Clearcole

I was able to do this successfully - I created a database on a normal instance, backed it up, and restored it on a localdb instance just fine: i.sstatic.net/rOOhq.png I'm not sure what information is missing from your question, but clearly you are doing something differently. I don't know what to ask in order to ferret that out. Does this happen for any database or just this specific database? – Cymogene

Are you sure the logical name for your LOG file is CSOdev_Log? Can you run restore filelistonly from disk = 'C:\MyBckDir\CSODev.bak to see the list of logical database files in the backup file? – Cannular

Running RESTORE FILELISTONLY FROM DISK='C:\MyBckDir\CSODev.bak' shows...CSOdev_Data Q:\MSSQL\CSOdev_Data.MDF D PRIMARY CSOdev_Log R:\MSSQL\CSOdev_Log.LDF L NULL – Clearcole

Hmn, the logical name looks fine. Anything interesting in the SQL error.log file? It should be in %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\<instance-name>, where <instance-name> is likely to be v11.0. – Cannular

Answer 1

There is known limitation (a real bug, in fact) for localDB. It will fail any RESTORE with MOVE whenever your database files are located in different folders.

You have to restore in the original folders (no MOVE). Use cmd tool such as SUBST if you need to fake a drive:/path.

Anisaanise

Answer 2

I had the same issue, and after doing a little online research I came across an ingenious way to get it to work (albeit quite hacky). Basically, you:

1. Create a SqlLocalDb instance (SqlLocalDb c tmp -s).
2. Restore the database as you did above (e.g., SqlCmd -E -S <localdb connection string> -Q "RESTORE DATABASE ...").
3. Stop the SqlLocalDb instance (SqlLocalDb p tmp).
4. Delete the SqlLocalDb instance (SqlLocalDb d tmp).
5. Create a new SqlLocalDb instance (SqlLocalDb c persistent -s).
6. Create the database in the new instance by attaching it (SqlCmd -E -S <persistent connection string> -Q "Create Database <dbname> On (Filename = '<Mdf file location'), (Filename = '<Ldf Filename'>) For Attach".

And hopefully it should work. See here for original idea.

Edit: Added Jason Brady's correction of the create command.

Rectilinear

This worked for me. Although, the Create Database syntax was wrong. It's literally 2 extra characters, and Stack Overflow tells me I can't make an edit unless it's at least 6. It needs to be Create Database <dbname> On (Filename = '<Mdf file location'), (Filename = '<Ldf Filename'>) For Attach – Sampson

Answer 3

I had the same problem. What eventually did work was this:

1. Trying to restore the database (getting the error in the OP)
2. Detaching the database
3. Reattaching the database

What happened in the last step was that SSDT performed an upgrade of the data files, that apparently was in an older format. When that was finished, the database started working without any problem!

Glassware

Answer 4

Try scripting your database as schema and data and then running the script locally.

Quarry

I think scripting the database is the way to go for some of us. I think my issue is maybe that the SQL Server instances are different (like 2005 versus 2008). – Frenchy

Answer 5

RESTORE FILELISTONLY
FROM DISK = 'D:\SQLBackups\yourdatabase.BAK'


ALTER DATABASE yourdatabasename
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE yourdatabasename
FROM DISK = 'D:\SQLBackups\yourdatabase.BAK'
with replace,
move 'logical name from file stream' to
'C:\yourdatabase.mdf',
move 'logical name from file stream' to 'C:\Yourdatabase.ldf'
ALTER DATABASE Qatar_DB SET MULTI_USER

Canzonet

This solution worked correctly for me as well. Just as a note, make sure you create the databases empty first then execute the query above adjusting for your proper case in LocalDB for this to work. Schemas from different versions seem to work as backwards Compatible (2012 --> 2010) – Diathesis

Answer 6

Same problem, thanks for the help. My local database is MS SQL 2014. Open "SQL Server 2014 Management Studio"

1. Right click the database, go to "Tasks", click "Take Offline"
2. Detach the database
3. Attach the database

It work for me. After you backup the database, you can restore the database without error. Thanks.

Pernambuco

Answer 7

I had the same problem. Try running visual studio as Administrator and try the following command

RESTORE DATABASE CSODev
FROM DISK = 'C:\MyBckDir\CSODev.bak'
WITH NORECOVERY, MOVE 'CSOdev_Data' TO 'C:\Users\cblair\CSOdev_Data.mdf',
MOVE 'CSOdev_Log' TO 'C:\Users\cblair\CSOdev_Log.ldf',

UPDATE: This did not work exactly!

Although the above statement does not produce any errors and completes successfully, the database remains in "PENDING RECOVERY" state and cannot be accessed in any way. When I tried to 'RESTORE WITH RECOVER' to bring the database online I got the same error as in the question above.

So in my case I ended up restoring the backup to a DEV server I have running with MSSQL 2008 R2 and then chose: Tasks -> Generate Scripts -> chose objects to script & Next -> click on "Advanced" button -> select "types of data to script" : Schema & data. Now run the generated script against the local db.

Faceless

Answer 8

Try these scripts (example with adventureworks2012 that I personally tested):

RESTORE FILELISTONLY
FROM DISK = 'c:\temp\adv2012.bak'

This will bring up the filenames as:

AdventureWorks2012      C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf
AdventureWorks2012_log  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf

Use these filenames to cinstruct your final script as this:

RESTORE DATABASE AdventureWorks2012
FROM DISK = 'C:\temp\adv2012.bak'

WITH MOVE 'AdventureWorks2012' TO 'C:\cnom_WS\Local-Databases\AdventureWorks\AdventureWorks2012.mdf',
MOVE 'AdventureWorks2012_log' TO 'C:\cnom_WS\Local-Databases\AdventureWorks\AdventureWorks2012_log.ldf',
REPLACE;

BTW I run these through Visual Studio (SQL Server Object explorer), but I strongly suspect this could be run on SSMS easily ;-)

Blossomblot

Answer 9

1. You can do it manually. this can be done by using dot net and opening two kinds of connections and forwarding data from one of them to the other. but this needs to create the same types of columns in the local one.
2. You can check the importing options of MS Access 2007

Teevens