I'm trying to backup a live database to my computer and I can't find the option to do it. I'm connecting to it using Microsoft SQL Server Management Studio 2008 R2. I'm a MySQL monkey, so I'm used to being able to backup to .sql files and move them around.
我正在尝试将实时数据库备份到我的计算机上,但我找不到这样做的选项。我正在使用Microsoft SQL Server Management Studio 2008 R2连接到它。我是MySQL的猴子,所以我习惯于能够备份到.sql文件并移动它们。
Anyone have any idea how I can create a file backup of the database? I've found the backup option which only backs up on the server, or the export, which seems to only allow a single table, or code an SQL query, which I'm not too sure on, short of putting in something like SHOW TABLES;
任何人都知道如何创建数据库的文件备份?我发现备份选项只备份在服务器上,或导出,这似乎只允许一个表,或代码一个SQL查询,我不太确定,没有像SHOW这样的东西TABLES;
Anyone have any ideas? I'm limited to readonly access for various reasons, nothing bad, promise!
有人有主意吗?由于各种原因,我只限于只读访问,没什么不好,许诺!
3 个解决方案
#1
2
You will only be able to backup the database to a location the service account for SQL has access to. If you have access to a central share on the server/network that you can access and the service can, you might backup to that location and then browse from your computer to pull it down.
您只能将数据库备份到SQL的服务帐户可以访问的位置。如果您可以访问服务器/网络上可以访问且服务可以访问的*共享,则可以备份到该位置,然后从计算机进行浏览以将其下拉。
If you are just wanting the database structure you could script the database out to a file. This would let you save it locally. If you also want the data though doing a full backup is the quickest way I know of.
如果您只是想要数据库结构,则可以将数据库编写为文件脚本。这样可以让你在本地保存。如果您还希望通过完整备份来获取数据,这是我所知道的最快捷方式。
EDIT
I would use the T-SQL BACKUP comand and include WITH COPY_ONLY to backup the database, since you stated this is a "live" database. If a scheduled job is performing backups against the database and you break in to do an additional one you will effect the backup recovery chain of the database. Using the COPY_ONLY will allow you to get a backup of the database without requiring it in the event of a recovery need.
我会使用T-SQL BACKUP命令并包含WITH COPY_ONLY来备份数据库,因为你声明这是一个“实时”数据库。如果预定作业正在对数据库执行备份,并且您要进行另外一个备份,则将影响数据库的备份恢复链。使用COPY_ONLY将允许您在需要恢复时不需要它来获取数据库的备份。
#2
2
You can enter a valid UNC path in the Backup option.
您可以在“备份”选项中输入有效的UNC路径。
#3
1
You can also create sql dumps with Management Studio.
您还可以使用Management Studio创建sql转储。
Right-click the database and select Tasks - Generate Scripts. This will open a wizard that allows you to select what the dump should include (e.g. tables, indices, views, ...).
右键单击数据库,然后选择“任务” - “生成脚本”。这将打开一个向导,允许您选择转储应包含的内容(例如表,索引,视图等)。
Make sure you set "Script Data" to true if you want your dump to include inserts.
如果希望转储包含插入,请确保将“脚本数据”设置为true。
#1
2
You will only be able to backup the database to a location the service account for SQL has access to. If you have access to a central share on the server/network that you can access and the service can, you might backup to that location and then browse from your computer to pull it down.
您只能将数据库备份到SQL的服务帐户可以访问的位置。如果您可以访问服务器/网络上可以访问且服务可以访问的*共享,则可以备份到该位置,然后从计算机进行浏览以将其下拉。
If you are just wanting the database structure you could script the database out to a file. This would let you save it locally. If you also want the data though doing a full backup is the quickest way I know of.
如果您只是想要数据库结构,则可以将数据库编写为文件脚本。这样可以让你在本地保存。如果您还希望通过完整备份来获取数据,这是我所知道的最快捷方式。
EDIT
I would use the T-SQL BACKUP comand and include WITH COPY_ONLY to backup the database, since you stated this is a "live" database. If a scheduled job is performing backups against the database and you break in to do an additional one you will effect the backup recovery chain of the database. Using the COPY_ONLY will allow you to get a backup of the database without requiring it in the event of a recovery need.
我会使用T-SQL BACKUP命令并包含WITH COPY_ONLY来备份数据库,因为你声明这是一个“实时”数据库。如果预定作业正在对数据库执行备份,并且您要进行另外一个备份,则将影响数据库的备份恢复链。使用COPY_ONLY将允许您在需要恢复时不需要它来获取数据库的备份。
#2
2
You can enter a valid UNC path in the Backup option.
您可以在“备份”选项中输入有效的UNC路径。
#3
1
You can also create sql dumps with Management Studio.
您还可以使用Management Studio创建sql转储。
Right-click the database and select Tasks - Generate Scripts. This will open a wizard that allows you to select what the dump should include (e.g. tables, indices, views, ...).
右键单击数据库,然后选择“任务” - “生成脚本”。这将打开一个向导,允许您选择转储应包含的内容(例如表,索引,视图等)。
Make sure you set "Script Data" to true if you want your dump to include inserts.
如果希望转储包含插入,请确保将“脚本数据”设置为true。