为什么备份 SQL Server 2008 Express 数据库是一项艰巨的任务？
我不会说自己害怕改变——而是害怕新技术？是的，确实如此！从操作系统到数据库服务器的技术似乎随着它们“进步”的进一步而变得有缺陷、低效和落后
MSDE 2000（在当今世界他们可能称之为“SQL 2000 Express”）
BACKUP [MyDatabase] TO FILE 'c:\backups\mybackup.backup'
SQL 2008 EXPRESS
等等！它是一个“用户实例” - 为了备份它，我们需要将它附加到服务器实例
等等！要附加它，我们需要 SQL Management Studio Express（78MB 下载）
等等！当我们登录到我们的 .\SQLEXPRESS 服务器实例并尝试附加我们的数据库时，它给我们一个错误，看起来像是我们的自制开发项目中的错误：
标题：Microsoft SQL Server Management Studio
无法显示请求的对话框。
------------------------------------------ 其他信息：
参数名称：nColIndex 实际值 为-1。 (Microsoft.SqlServer.GridControl)
有人可以解释如何在 T-SQL 代码中备份 SQL Server 2008 Express 数据库的用户实例吗？
（很抱歉，如果这对微软来说就像火焰一样 - 我实际上是他们的超级粉丝。只是对这样的事情感到非常生气！请不要投票给我......）
I wouldn't describe myself as afraid of change - but afraid of new technologies? YES INDEED! Technologies from operating systems, to database servers just seem to become bugged, inefficient and backward the further they "progress"
MSDE 2000 (what they might call "SQL 2000 Express" in today's world)
BACKUP [MyDatabase] TO FILE 'c:\backups\mybackup.backup'
SQL 2008 EXPRESS
Wait up! Its a 'user instance' - to back it up we need to attach it to a server instance
Wait up! To attach it we need SQL Management Studio Express (78MB download)
Wait up! When we login to our .\SQLEXPRESS server instance and try to attach our database it gives us an error that literally looks like a bug in our homebrew dev project:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
------------------------------ ADDITIONAL INFORMATION:
Parameter name: nColIndex Actual value
was -1.
(Microsoft.SqlServer.GridControl)
Can someone explain how to backup a user instance of a SQL Server 2008 Express database in T-SQL code?
(sorry if this comes across like a flame at ummmm, Microsoft - I'm actually a huge fan of theirs. Just really angry about things like this! please don't vote me down...)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
嗯，如果是用户实例，那么最简单的备份策略就是复制文件。 （虽然它没有连接到 SQL Server）。
如果您需要更全面的备份策略（例如事务备份），那么您确实应该考虑一种更全面的数据库（例如附加到完整 SQL Server 实例的“普通”数据库）
Um, if it's a user instance, then the simplest backup strategy is to copy the file. (whilst it's not connected to SQL Server).
If you need a more comprehensive backup strategy (e.g. transactional backups), then you really should be looking at a more comprehensive database (e.g. a "normal" one attached to a full SQL Server instance)
尝试实现用户实例备份时需要注意的一些关键提示
a.) 连接
您的连接字符串应如下所示：
重要您的连接字符串为连接提供别名
Database=MyDatabaseAlias- 此别名不能在同一台计算机上同时复制，否则连接可能会失败。b.) 备份
如上所述，在 SQL MSDE/2000/2005/2008/R2 上备份数据库的 Transact SQL 是相同的 - 一旦您附加了数据库并别名！
真正令人惊奇的是，您可能会遇到 bull$h!t 错误，因为您的连接字符串没有别名
Database=MyDatabaseAlias部分。例如，无法打开物理文件 'c:\Code\MyProject\App_Data\MyDatabase.mdf' 操作系统错误 32：“32（该进程无法访问该文件，因为该文件正在被另一个进程使用。）”。备份数据库为异常终止。
c.) 恢复
不要忘记所有必需的
USE [master];位于此语句的开头（并注意其对于那些从DataContext或类似的执行命令的人来说，全部在一行）如果这样做，它将无法覆盖现有数据库，因为您仍然会连接到它。再一次，由于连接字符串无效，您可能会在这里收到各种各样的完全误导性错误。
SOME KEY TIPS TO NOTE WHEN TRYING TO ACHIEVE USER INSTANCE BACKUP
a.) Connecting
Your connection string should look like this:
It is essential that your connection string gives the connection an alias
Database=MyDatabaseAlias- this alias cannot be duplicated concurrently on the same machine or your connection may fail.b.) Backing Up
As pointed out above, the Transact SQL to backup a database is the same on SQL MSDE/2000/2005/2008/R2 - once you have your database attached and aliased!
Whats truly amazing is the bull$h!t errors you can get because your connection string doesnt have the alias
Database=MyDatabaseAliaspart.e.g. Unable to open the physical file 'c:\Code\MyProject\App_Data\MyDatabase.mdf' Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".BACKUP DATABASE is terminating abnormally.
c.) Restoring
Do not forget the all essential
USE [master];at the beginning of this statement (and note that its all on one line for those executing the command from aDataContextor similar) If you do, it wont be able to overwrite the existing database because you'll still be connected to it.Once again, the assortment of totally misleading errors you might receive here, due to an invalid connection string, is endless.