复制SQL Server数据库的问题 [英] questions on copying SQL Server database

问题描述

Subquestioning [2]

在复制Resource.mdf [1]时，我注意到：

1）

可以在不停止SQL Server实例的情况下复制Resource.mdf（我附加了一个从运行实例复制并且它在附加之后工作）。

1a）

我应该理解它作为所有只读数据库的一般可能性，或者它只在一些版本中（我的是2008 R2在Windows XP Pro Sp3上）？

1b）

从概念上来说，我不明白为什么在孤立的/工作组桌面上的开发（Dev Ed）SQL Server（在我的情况下 - XP Pro Sp3），以便复制用户定义的未使用（例如，示例数据库）数据库。

1c）

当应该将.LDF与.MDF文件一起复制，并且复制时可以跳过.LDF？

为什么只读数据库必须有（我理解的事务日志）文件LDF？

2）

Resource.mdf SQL Server 2008 R2默认未命名实例，Windows XP Pro SP3）位于

• ％ProgramFiles％\ Microsoft SQL Server \ MSSQL10_50。 MSSQLSERVER \MSSQL\Binn\

，而不是

• ％ProgramFiles％\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\ [2]

我很好奇 - 由于SQL Server版本，版本或Windows版本，位置是否不同？

[1]

我的问题如何查看/脚本系统视图定义？

http://stackoverflow.com/questions/3845438/how-to-see-script-

[2]

Martin Smith对[1]的回答

http://stackoverflow.com/questions/3845438/how-to-see-script- 如果数据库具有 auto_close 打开，则当数据库不再使用时，引擎将关闭文件句柄。 Express实例创建数据库为auto-close ON，并且当数据库附加到非Express实例时保留此属性。

这不是真的，你必须关闭实例复制数据库文件。你所要做的就是放弃数据库文件上的SQL Server实例所拥有的独占句柄。这可以通过简单地将数据库设置为脱机，执行复制，然后将其重新设置为联机来完成。 （如果我记得正确）对于只读的数据库，SQL Server持有的句柄允许另一个进程打开文件上的共享句柄。这是为什么
可以复制在线只读数据库，msqlsystemreource在正常操作模式下是只读的。

其他问题：

1c）从来没有。始终复制MDF，所有NDF和LDF。即使有单独复制MDF的推荐，也可以忽略它们并复制LDF。

2）资源数据库是二进制文件的一部分。它们将位于安装的binn文件夹中，位置与sqlservr.exe相同。

最后一个考虑因素：为什么你触摸资源db ？你是一个痛苦的世界，如果你以任何方式操纵资源数据库，你可能很容易损坏你的实例。

Subquestioning [2]

While copying Resource.mdf [1], I noticed that:
1)
It is possible to copy Resource.mdf without stopping SQL Server instance (I attached one having copied from running instance and it works after attaching) .

1a)
Should I understand that it as general possibility for all read-only databases or is it only in some versions (mine is 2008 R2 on Windows XP Pro Sp3)?
1b)
Conceptually, I cannot understand why a developing (Dev Ed) SQL Server on isolated/workgroup desktop Windows (in my case - XP Pro Sp3) should be stopped in order to copy a user-defined non-used (for ex., sample database) database.

1c)
When one should copy .LDF together with .MDF file and when copying .LDF can be skipped?
Why does read-only database have necessarily (transaction log? as I understood) file LDF?

2)
Resource.mdf (in my SQL Server 2008 R2 on default unnamed instance, Windows XP Pro SP3) is in

• %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\

and not in

• %ProgramFiles%\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\ [2]

I am curious - Is the location differing due to SQL Server version, edition or Windows edition, version?

[1]
My question "how to see/script definitions of system views?"
http://stackoverflow.com/questions/3845438/how-to-see-script-definitions-of-system-views
[2]
Martin Smith's answer to [1]
http://stackoverflow.com/questions/3845438/how-to-see-script-definitions-of-system-views/3845473#3845473

解决方案

If the database has auto_close turned on then the engine will close the file handle when the database is no longer in use. Express instances create databases as auto-close ON and this property is preserved when database is attached to a non-Express instance.

It is not true that you have to shutdown the instance to copy a database file. All you have to do is to relinquish the exclusive handle held by the SQL Server instance on the database files(s). This can be done by simply setting the database offline, do the copy, then setting it back online. (If I remember correctly) for databases that are read-only, the handle held by SQL Server allows for another processes to open a shared handle on the file. This is why you can copy an online read-only database, and msqlsystemreource is read-only under normal operating mode.

Other questions:
1c) never. Always copy the MDF, all NDFs and the LDF(s). Even when there are recomendations to copy the MDF alone, ignore them and copy the LDF as well.
2) the resource databases are part of your binaries. They will be in the binn folder of your installation, same location as sqlservr.exe. The path will differ for each installed instance and you can change it during Setup.

And one last consideration: why do you touch the resource db? You are up for a world of pain, you may easily corrupt your instance if you manipulate resource db in any way.

这篇关于复制SQL Server数据库的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！