为什么要执行备份SQL Server 2008 Express数据库的任务? [英] Why is it such a mission to backup a SQL Server 2008 Express database?
问题描述
我不会形容自己害怕改变-但是害怕新技术?确实是的！从操作系统到数据库服务器的技术似乎越发漏洞，效率低下，并且随着它们的进步"而落后.
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 (在当今世界中它们可能被称为"SQL 2000 Express")
MSDE 2000 (what they might call "SQL 2000 Express" in today's world)
BACKUP [MyDatabase] TO FILE 'c:\backups\mybackup.backup'
SQL 2008 EXPRESS
等一下！它是一个用户实例"-要备份它，我们需要将其附加到服务器实例
等一下要附加它，我们需要SQL Management Studio Express(下载78MB)
等一下当我们登录到.\ SQLEXPRESS服务器实例并尝试附加数据库时，它给我们带来了一个错误，该错误实际上看起来像是我们的自制开发项目中的错误:
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:
标题:Microsoft SQL Server Management Studio
TITLE: Microsoft SQL Server Management Studio
无法显示请求的对话框.
Cannot show requested dialog.
------------------------------其他信息:
------------------------------ ADDITIONAL INFORMATION:
参数名称:nColIndex实际值 是-1. (Microsoft.SqlServer.GridControl)
Parameter name: nColIndex Actual value was -1. (Microsoft.SqlServer.GridControl)
有人可以解释如何用T-SQL代码备份SQL Server 2008 Express数据库的用户实例吗?
Can someone explain how to backup a user instance of a SQL Server 2008 Express database in T-SQL code?
(很抱歉，如果遇到类似ummmm的火焰，微软-我实际上是他们的忠实拥护者.只是对这样的事情感到非常生气！请不要对我投以反对票...)
(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...)
推荐答案
在尝试获得用户实例备份时需要注意的一些关键提示
您的连接字符串应如下所示:
Your connection string should look like this:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True;Database=MyDatabaseAlias
必不可少是您的连接字符串为连接赋予别名Database=MyDatabaseAlias-此别名不能在同一台计算机上同时复制，否则您的连接可能会失败.
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.
如上所述，用于备份数据库的Transact SQL在SQL MSDE/2000/2005/2008/R2上是相同的-一旦您附加了数据库并对其进行了别名！
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!
BACKUP DATABASE MyDatabaseAlias TO DISK = 'c:\backups\mydatabase_20101117.backup'
真正令人惊讶的是，由于连接字符串没有别名Database=MyDatabaseAlias部分，您会遇到bull $ h！t错误.
Whats truly amazing is the bull$h!t errors you can get because your connection string doesnt have the alias Database=MyDatabaseAlias part.
例如无法打开物理文件'c:\ Code \ MyProject \ App_Data \ MyDatabase.mdf'操作系统错误32:"32(该进程无法访问该文件，因为它正在被另一个进程使用.)".BACKUP DATABASE正在终止异常.
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.
USE [master]; RESTORE DATABASE MyDatabaseAlias FROM DISK = 'c:\backups\mydatabase_20101117.backup' WITH REPLACE
不要忘记该语句开头的所有必需USE [master];(请注意，对于从
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 a DataContext or 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.
这篇关于为什么要执行备份SQL Server 2008 Express数据库的任务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
