将 SQL Server Express 数据库复制到另一台计算机 [英] Copy SQL Server Express Database to Another Computer
问题描述
我明天要升级客户的 Access 应用程序的后端，需要准备好针对此客户的特定问题的计划.老板需要能够定期(通常是每月)将数据文件从 SQL Server 所在的办公室带走，并对数据进行统计分析.
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.
我看过将整个 SQL 服务器数据库从服务器复制到本地 SQL Express 的最简单方法，并且那里的解决方案在这种情况下不起作用，因为:
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.
不能使用任何依赖于联网的两台计算机的方法，因为这不是一种选择——数据必须通过 USB 拇指驱动器传输(因此，没有复制).
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).
不能使用任何依赖于从服务器控制台或工作站运行 SQL Server 管理工具的方法进行备份.
can't use any method that depends on running SQL Server management tools from the server console or from the workstation for the backup.
无法直接连接到 SQL Server 数据库进行分析，因为数据必须可以移植到其他位置.
can't connect to the SQL Server database directly for the analysis because the data has to be portable to other locations.
我认为我需要的是某种方法来调用创建备份文件的脚本，然后将结果复制到 USB 驱动器.然后我需要第二个脚本来从 USB 驱动器复制备份文件并将其恢复到另一个 SQL Server.
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.
正在传输的数据是只读的(或者，所做的任何更改都不需要返回主服务器)，并且不会在第二个位置更新数据.它目前使用普通的旧批处理文件编写脚本来复制后端 MDB 文件，我需要一些对用户来说同样简单的东西.
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.
它不能有任何依赖，比如说，Powershell(SQL Server Management Studio)，因为我不希望它必须安装在用户运行脚本的计算机上(有一半脚本需要从几十个工作站运行，我不想在所有这些工作站上安装任何东西).
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.
想法、建议、指点?
推荐答案
你绝对应该能够创造出类似的东西.
You should definitely be able to create something like that.
其中一部分是作为 .sql 脚本的 T-SQL CREATE BACKUP 脚本，并从标准 Windows 批处理 (*.bat) 或使用 sqlcmd 命令行工具的命令 (*.cmd) 文件.
One part would be a T-SQL CREATE BACKUP script as a .sql script, and execute that from a standard Windows batch (*.bat) or command (*.cmd) file using the sqlcmd command line tool.
应该是这样的:
backup.sql
BACKUP DATABASE YourDatabase
TO DISK = 'Z:\Backup\YourDatabase.bak'
WITH FORMAT;
第二部分是一个带有 T-SQL RESTORE 脚本的 .sql 文件，基本上是从磁盘上的给定位置读取数据并将其恢复到那里的 SQL Server 实例.
The second part would be a .sql file with a T-SQL RESTORE script, basically reading the data from a given location on disk and restoring it to that SQL Server instance there.
restore.sql
RESTORE DATABASE YourDatabase
FROM AdventureWorks2008R2Backups
WITH
MOVE 'YourDatabase_Data' TO 'C:\MSSQL\Data\YourDatabase.mdf',
MOVE 'YourDatabase_Log' TO 'C:\MSSQL\Data\YourDatabase_Log.ldf';
GO
当然，您需要根据自己的实际需求调整这些名称和路径 - 但这应该只是提示您如何开始这项工作.
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.
要使用 sqlcmd 执行这些 .sql 脚本之一，您需要类似:
To execute one of those .sql script using sqlcmd, you need something like:
sqlcmd -S (name of server) -U (login) -P (password) -I (name of script file)
例如
sqlcmd -S (local) -U someuser -P top$secret -I backup.sql
资源:
这篇关于将 SQL Server Express 数据库复制到另一台计算机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
