Absolutely, you’ve got great options here—automating the copy of your MDF/LDF files after the monthly detach job is totally doable with SQL Agent Jobs, Windows Task Scheduler, or SSIS. Let’s walk through each approach with practical, actionable steps:

This is the most straightforward choice if you’re already using SQL Agent for your detach job—you can even chain the two jobs together.

• First, confirm the SQL Server Agent service account has:
  • Read permissions on the E:\ directory holding your MDF/LDF files
  • Write permissions on your network share (e.g., \\FileServer\DBBackupShare)
• Create a new SQL Agent Job, then add a step with one of these types:
  • CmdExec (Command Line): Use robocopy (more reliable than basic copy/xcopy for file transfers):

    robocopy "E:\YourDatabaseDirectory" "\\FileServer\DBBackupShare" *.mdf *.ldf /COPYALL /R:3 /W:5
    

    • /COPYALL preserves all file attributes (ownership, timestamps), /R:3 retries 3 times if a file lock occurs, /W:5 waits 5 seconds between retries.
  • PowerShell: If you prefer a more flexible scripting approach:

    Copy-Item -Path "E:\YourDatabaseDirectory\*.mdf", "E:\YourDatabaseDirectory\*.ldf" -Destination "\\FileServer\DBBackupShare" -Force
    

• Schedule the job to run right after your detach job—set the trigger to monthly on the 1st, with a 5-10 minute delay to ensure the detach completes and files are released.

Perfect if you want to keep the copy process separate from SQL Server, or if you don’t have SQL Agent available (e.g., SQL Server Express).

• Open Task Scheduler → Create Basic Task.
• Set the trigger to "Monthly", targeting the 1st of every month—add a delay here too, matching the expected completion time of your detach job.
• For the action, choose "Start a program":
  • You can run robocopy directly (same command as above), or
  • Point to a batch file for easier maintenance:

    @echo off
    REM Copy MDF/LDF files to network share
    robocopy "E:\YourDatabaseDirectory" "\\FileServer\DBBackupShare" *.mdf *.ldf /COPYALL /R:3 /W:5
    REM Exit with robocopy's error level for logging
    exit /b %errorlevel%
    

• Configure the task to run under an account with the necessary read/write permissions on both the local drive and network share.

Ideal if you need advanced features like error logging, notifications, or complex conditional logic (e.g., only copy files if they’re larger than a certain size).

• In SQL Server Data Tools (SSDT), create a new SSIS package:
  1. Add a File System Task to the control flow.
  2. Configure the task:
     • Operation: Copy Files (or Copy Directory if you want to move the entire folder)
     • Source Connection: Browse to your E:\ database directory, and set the filter to *.mdf;*.ldf
     • Destination Connection: Point to your network share
  3. Add error handling: Attach a Failure precedence constraint from the File System Task to a Send Mail Task (to alert you if the copy fails) or a Script Task to log errors to a table.
• Deploy the package to the SSIS Catalog, then create a SQL Agent Job to run it on your monthly schedule (again, after the detach job).

• File Locks: Never skip verifying the database is fully detached before copying. If your detach job’s runtime varies, add a check (e.g., a PowerShell script that tests if the files are in use with Test-Path -Path "E:\YourDB.mdf" -IsValid).
• Permissions: Double-check that the account running the job/task has explicit permissions—network shares often require domain accounts with write access, not just local machine accounts.
• Testing: Always run the job manually first to confirm files copy correctly and no errors pop up before relying on the monthly schedule.

内容的提问来源于stack exchange，提问作者Nani