将开发 SQL Server 复制到拇指驱动器的最简单方法

发布于 2024-12-14 12:19:55 字数 96 浏览 1 评论 0原文

我有一个小型 SQL 数据库，仅位于我的开发 PC 上。我想经常将其备份到拇指驱动器上。如果我能让它在检测到拇指驱动器时自动按计划执行，那就更好了。有什么好的方法可以做到这一点？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绝情姑娘 2024-12-21 12:19:56

我这样做是为了让数据库和其他文件夹在每次插入 USB 驱动器时自动同步到 USB 驱动器：

安装 SyncToy 由 Microsoft 制作。
- 添加文件夹对以从 DB 文件夹复制到 USB 驱动器。
- 过滤文件，仅显示您要复制的文件。
使用以下内容创建一个名为 SyncMe.bat 的批处理程序，并将其保存到 USB 驱动器上
从控制面板打开事件查看器。
导航至应用程序和服务日志 >微软> Windows DriverFrameworks-用户模式>操作
清除日志以便更轻松地找到正确的事件。
插入 USB 驱动器。
刷新日志，找到特定于该 USB 驱动器的最新事件，右键单击并将任务附加到此事件。
添加一个操作来运行您创建的 SyncMe.bat 程序

SyncMe.bat 内容（更新以反映您的路径和文件名）：

@echo off

if exist "G:\SyncMe.bat" goto fileexists

goto nofile

:fileexists
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -S computername\instancename -E -n -Q "master..sp_detach_db 'DatabaseName'"
"C:\Program Files\SyncToy 2.1\SyncToyCmd.exe" -R
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -S computername\instancename -E -n -Q "master..sp_attach_db 'DatabaseName', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName\MSSQL\DATA\DatabaseName.mdf','C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName\MSSQL\DATA\LogName_log.ldf'"
goto end

:nofile
echo SyncMe.bat not found on G:\
goto end

:end

This is what I did to get the database and other folders to automatically sync to my USB drive every time I plug it in:

Install SyncToy made by Microsoft.
- Add a folder pair to copy from the DB folder to the USB drive.
- Filter files to only the files you want to copy.
Create a batch program with the content below called SyncMe.bat and save it on your USB drive
Open Event Viewer from the Control Panel.
Navigate to Applications and Services Logs > Microsoft > Windows DriverFrameworks-UserMode > Operational
Clear the log to make it easier to find the right events.
Plug in the USB drive.
Refresh the log, find the latest event that is specific to that USB drive, right-click, and Attach Task To This Event.
Add an action that runs the SyncMe.bat program you created

SyncMe.bat contents (update to reflect your paths and file names):

@echo off

if exist "G:\SyncMe.bat" goto fileexists

goto nofile

:fileexists
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -S computername\instancename -E -n -Q "master..sp_detach_db 'DatabaseName'"
"C:\Program Files\SyncToy 2.1\SyncToyCmd.exe" -R
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE" -S computername\instancename -E -n -Q "master..sp_attach_db 'DatabaseName', 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName\MSSQL\DATA\DatabaseName.mdf','C:\Program Files\Microsoft SQL Server\MSSQL10_50.InstanceName\MSSQL\DATA\LogName_log.ldf'"
goto end

:nofile
echo SyncMe.bat not found on G:\
goto end

:end

回复收藏 0 原文

眼眸里的快感 2024-12-21 12:19:56

我认为您无法让 SQL Server 为您完成这一切。我最终会编写一个使用 SMO 来执行操作的小控制台应用程序对我来说的工作：

Server sqlServer = new Server(@"MYMACHINE\INSTANCENAME");
sqlServer.DetachDatabase("MyDatabaseName", false);

// copy the mdf and ldf to your thumb drive

StringCollection databaseFiles = new StringCollection();
databaseFiles.Add(@"C:\MyDBLoc\MyDatabase.mdf");
databaseFiles.Add(@"C:\MyDBLoc\MyDatabase.ldf");
sqlServer.AttachDatabase("MyDatabaseName", databaseFiles);

I don't think you can get SQL Server to do this all for you. I'd end up writing a little console app that used SMO to do the work for me:

Server sqlServer = new Server(@"MYMACHINE\INSTANCENAME");
sqlServer.DetachDatabase("MyDatabaseName", false);

// copy the mdf and ldf to your thumb drive

StringCollection databaseFiles = new StringCollection();
databaseFiles.Add(@"C:\MyDBLoc\MyDatabase.mdf");
databaseFiles.Add(@"C:\MyDBLoc\MyDatabase.ldf");
sqlServer.AttachDatabase("MyDatabaseName", databaseFiles);

回复收藏 0 原文