将 SQL Server Express 数据库复制到另一台计算机
我明天要扩大客户 Access 应用程序的后端规模，需要准备好针对该客户的特定问题的计划。老板需要能够定期（通常每月）从SQL Server所在的办公室取出数据文件并对数据进行统计分析。
我看过 将整个 SQL Server 数据库从服务器复制到本地 SQL Express 的最简单方法，其中的解决方案在这种情况下不起作用，因为：
无法使用一次性解决方案（数据库发布向导），因为这需要可编写脚本。
无法使用任何依赖于联网的两台计算机的方法，因为这不是一个选项 - 数据必须通过 USB 拇指驱动器传输（因此，无法复制）。
无法使用任何依赖于从服务器控制台或工作站运行 SQL Server 管理工具的方法进行备份。
无法直接连接到 SQL Server 数据库进行分析，因为数据必须移植到其他位置。
我认为我需要的是某种方法来调用创建备份文件的脚本，然后将结果复制到 USB 驱动器。然后，我需要第二个脚本来从 USB 驱动器复制备份文件并将其恢复到另一个 SQL Server 上。
正在传输的数据是只读的（或者，所做的任何更改都不需要返回到主服务器），并且数据不会在第二个位置更新。目前它使用普通的旧批处理文件编写脚本来复制后端 MDB 文件，并且我需要对用户来说简单的东西。
它不能对 Powershell（SQL Server Management Studio 的）有任何依赖，因为我不希望它必须安装在用户运行脚本的计算机上（有六个工作站）脚本需要可以运行，我不想在所有这些上安装一些东西）。
我将设置备份代理以每晚创建备份，因此我也许可以复制该文件，而无需在复制之前启动备份。因此，我可能只需要在目标计算机上编写恢复脚本。
想法、建议、指示？
I am upsizing the back end of a client's Access app tomorrow and need to be ready with a plan for a particular problem with this client. The boss needs to be able to take the data file away from the office where the SQL Server is on a regular basis (usually monthly) and run statistical analyses on the data.
I've looked at Easiest way to copy an entire SQL server Database from a server to local SQL Express, and the solutions there don't work in this scenario because:
can't use a one-time solution (Database Publishing Wizard), as this needs to be scriptable.
can't use any method that depends on the two computers being networked, as that is not an option -- data has to be transferred via USB thumb drive (so, no replication).
can't use any method that depends on running SQL Server management tools from the server console or from the workstation for the backup.
can't connect to the SQL Server database directly for the analysis because the data has to be portable to other locations.
What I think I need is some way to call a script that creates a backup file, then copy the result to the USB drive. I then need a second script to copy the backup file from the USB drive and restore it onto the other SQL Server.
The data being transported is read-only (or, any changes made don't need to get back to the main server), and data won't be updated in the second location. It's currently scripted with a plain old batch file to copy the back-end MDB file, and I need something that is as simple for the user.
It can't have any dependencies on, say, Powershell (of SQL Server Management Studio), because I don't want it to have to be installed on the computer the user is running the script from (there are a half dozen workstations the script needs to be runnable from, and I don't want to have to install something on all of those).
I'm going to be setting up the backup agent to create a backup every night, so I could perhaps copy that file without needing to initiate the backup before copying. Thus I might only need to script the restore on the target computer.
Thoughts, suggestions, pointers?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你绝对应该能够创建类似的东西。
其中一部分是 T-SQL
CREATE BACKUP脚本作为.sql脚本，并从标准 Windows 批处理 (*.bat）或使用sqlcmd命令行工具的命令 (*.cmd) 文件。这将是这样的：
backup.sql
第二部分是一个带有 T-SQL
RESTORE脚本的 .sql 文件，基本上从给定位置读取数据磁盘并将其恢复到该 SQL Server 实例。restore.sql
当然，您需要根据自己的实际需求调整这些名称和路径 - 但这应该只是给您一个提示，告诉您如何开始这项工作。
要使用
sqlcmd执行这些.sql脚本之一，您需要类似：例如
资源：
You should definitely be able to create something like that.
One part would be a T-SQL
CREATE BACKUPscript as a.sqlscript, and execute that from a standard Windows batch (*.bat) or command (*.cmd) file using thesqlcmdcommand line tool.That would be something like this:
backup.sql
The second part would be a .sql file with a T-SQL
RESTOREscript, basically reading the data from a given location on disk and restoring it to that SQL Server instance there.restore.sql
Of course, you need to adapt those names and paths to your own actual requirements - but that should just give you a hint how to get started with this endeavor.
To execute one of those .sql script using
sqlcmd, you need something like:e.g.
Resources:
我在生产（服务器）和测试开发（另一个位置的本地）之间传输数据库以及将完成的数据库传输到托管服务器时遇到了同样的问题。
事实证明，我可以自己运输 .MDF。
我一直这样做；工作完美——省去了运输大.ldf 的麻烦。 （我经常使用 dropbox.com 而不是拇指驱动器，并首先使用 PKZIP/SecureZip 加密文件。）
我不确定如果在服务器启动的情况下复制 MDF 会发生什么，尽管我在复制之前不会停止它。我不确定如何使其可编写脚本 - 附件可以编写脚本，但我不确定在继续之前是否删除 .LDF。
您可以编写服务器脚本来制作数据库的快照副本，然后传输该 MDF（知道它没有更新）。
另一个想法 - 编写一个程序将所有记录插入到 SQL-Server 精简版文件中并传输该文件？我没有尝试过，但读过相关内容。
I had this same issue transporting db between production (server) and test-development (local in another location) and also transporting finished db to hosted server.
As it turned out I can just transport the .MDF by itself.
I do this all the time; works perfectly -- saves the trouble of transporting a large .ldf. (I often use dropbox.com instead of a thumb drive and encrypt the file with PKZIP/SecureZip first.)
I'm not sure what happens if you copy the MDF with the server started, though I do not stop it before copying. I'm not sure how to make this scriptable -- the attach can be scriptable but I'm not sure about deleting the .LDF before proceeding.
You could script the server to make a snapshot copy of the database, then transport that MDF knowing that it was not being updated.
Another idea - write a program to insert all the records into a SQL-Server compact edition file and transport that? I've not tried it, but read about it.
只是一个想法，但如果他目前只为自己保留了一份包含所有数据的 MDB 文件副本，那么为什么不继续这样做呢。您可以通过对每个表的查询在访问中完成所有操作。
如果您使用链接的表和名为“tblFoo_LINKED”的表以及名为“tblFoo”的本地表来设置“报告”MDB，那么您只需运行一些 VBA 代码即可循环遍历所有表并执行类似
SQL Server 备份的 操作恢复仍然是我会选择的选项，但只是提供可能适合您需求的不同旋转
Just a thought but if he currently goes away with a copy of a MDB file with all the data in just for himself then why not carry on like that. You could just do it all in access with a query for each table.
If you setup your “reporting” MDB with the tables linked and called “tblFoo_LINKED” and a local table called “tblFoo” you could then just run a bit of VBA code that would loop through all the tables and execute something like
The SQL server backup restore would still be the option I would go for but just offering a different spin on things which might fit your needs
为什么不继续复制整个数据库文件？这听起来像是一个足够小的操作，可以避免短暂的暂停：您可以收缩数据库、分离并直接复制文件。在目标系统上，您可以通过以下方式附加 SQLExpress DB：连接字符串中的文件名。
Why not continue copying the entire database file? This reads like a small enough operation to get away with a momentary pause: You could shrink the database, detach, and copy the files directly. On the target system, you could attach the SQLExpress DB by file name in the connection string.