如何将SQL Server 2012数据库复制到localdb实例?

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...

我希望将SQL Server 2012标准数据库复制到我的localdb实例。我尝试过一个向导，它抱怨localdb不是一个SQL Server 2005或以后的express实例。我也做了备份/恢复，但是在我的localdb中恢复后，我得到以下错误……

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.

处理了数据库“CSODev”的8752页，文件1上的“CSOdev_Data”文件。处理了数据库“CSODev”的5页，文件1上的“CSOdev_Log”文件。

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.

无法找到逻辑数据库文件“CSOdev_Log”。指定文件的完整路径。Msg 3167, 16级，状态1，第1行恢复无法启动数据库“CSODev”。Msg 3013, 16级，状态1，第1行恢复数据库异常终止。

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.

数据库以“恢复挂起”模式结束。看起来日志文件有问题。我已经尝试了两种不同的备份，以防其中一种被损坏。

8 个解决方案

#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.

localDB有一个已知的限制(实际上是一个真正的bug)。当您的数据库文件位于不同的文件夹中时，它将失败任何移动恢复。

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

您必须在原始文件夹中恢复(不移动)。如果需要伪造驱动器:/路径，请使用cmd工具，如SUBST。

#2

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

我遇到了同样的问题。最终起作用的是:

Trying to restore the database (getting the error in the OP)
尝试恢复数据库(在OP中获取错误)
Detaching the database
分离数据库
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!

在最后一步中发生的事情是SSDT执行了数据文件的升级，这显然是一种旧的格式。完成之后，数据库开始工作，没有任何问题!

#3

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:

我也遇到了同样的问题，在网上做了一些调查之后，我发现了一种巧妙的方法，可以让它发挥作用(尽管相当陈腐)。基本上,你:

Create a SqlLocalDb instance (SqlLocalDb c tmp -s).
创建一个SqlLocalDb实例(SqlLocalDb c tmp -s)。
Restore the database as you did above (e.g., SqlCmd -E -S <localdb connection string> -Q "RESTORE DATABASE ...").
如前所述还原数据库(例如，SqlCmd -E -S -Q“Restore database…”)。连接字符串>
Stop the SqlLocalDb instance (SqlLocalDb p tmp).
停止SqlLocalDb实例(SqlLocalDb p tmp)。
Delete the SqlLocalDb instance (SqlLocalDb d tmp).
删除SqlLocalDb实例(SqlLocalDb d tmp)。
Create a new SqlLocalDb instance (SqlLocalDb c persistent -s).
创建一个新的SqlLocalDb实例(SqlLocalDb c持久化-s)。
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".
在新实例中通过附加数据库(SqlCmd -E -S <持久连接字符串> -Q "创建数据库 On (Filename = ' ' For Attach))来创建数据库。

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

希望它能起作用。请看这里的原始想法。

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

编辑:添加了Jason Brady修改的create命令。

#4

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

尝试将数据库脚本化为模式和数据，然后在本地运行脚本。

#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

#6

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

我遇到了同样的问题。尝试以管理员的身份运行visual studio，并尝试执行以下命令

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.

虽然上面的语句不会产生任何错误并成功完成，但是数据库仍然处于“PENDING RECOVERY”状态，并且不能以任何方式访问。当我尝试“用recovery恢复恢复”将数据库联机时，我得到了与上面问题相同的错误。

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.

所以在我的情况下我最终恢复备份DEV服务器我使用该软件2008 R2然后选择:任务- >生成脚本- >选择对象脚本&下- >单击“高级”按钮- >选择“类型的数据脚本”:模式和数据。现在针对本地db运行生成的脚本。

#7

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

同样的问题，谢谢你的帮助。我的本地数据库是MS SQL 2014。打开“SQL Server 2014 Management Studio”

Right click the database, go to "Tasks", click "Take Offline"
右键点击数据库，进入“任务”，点击“Take Offline”
Detach the database
分离数据库
Attach the database
附加数据库

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

它为我工作。在备份数据库之后，可以毫无错误地恢复数据库。谢谢。

#8

-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.
你可以手动操作。这可以通过使用dot net来完成，并且打开两种连接，并将数据从其中一个转发到另一个。但这需要在本地列中创建相同类型的列。
You can check the importing options of MS Access 2007
您可以查看MS Access 2007的导入选项。

#1