如何在同一SQL Server上每日自动克隆数据库？脚本问题求助

Let's walk through why you're hitting this error and how to fix it, plus clear up that confusion about the MOVE clause.

1. 为什么会出现“逻辑文件不属于数据库”的报错？ #

Your mistake here is using the physical file path (like D:\Microsoft SQL Server\...\MY_DB.mdf) as the argument for MOVE, but SQL Server expects the logical file name of the database files from the backup.

Logical file names are the internal names SQL Server uses to reference database files, separate from their physical location on disk. The error message is telling you exactly what to do: run RESTORE FILELISTONLY to get these correct logical names.

2. 如何获取正确的逻辑文件名？ #

Execute this query against the backup file you created:

RESTORE FILELISTONLY FROM DISK = 'D:\BACKUP\MY_DB.bak';

This will return a result set with columns like LogicalName, PhysicalName, and others. Look at the LogicalName column:

• The row with Type = D is your data file's logical name (e.g., it might just be MY_DB)
• The row with Type = L is your log file's logical name (e.g., MY_DB_log)

These are the values you need to use in the MOVE clauses.

3. 为什么需要使用MOVE语句？ #

I get the confusion—"move" makes it sound like you're relocating the original database files, but that's not what's happening here.

When you restore a backup as a new database:

• SQL Server can't use the same physical files as the original MY_DB (those files are locked by the running original database)
• You don't want to overwrite the original files anyway—you're trying to make a copy!

The MOVE clause tells SQL Server: "Take the logical file [X] from the backup, and restore it to this new physical file path [Y] for the new database." It's mapping the backup's internal file references to new physical files for your copied database. It's 100% necessary for creating a duplicate database on the same server.

4. 修正后的完整脚本 #

First, run the backup (your original backup command is fine):

BACKUP DATABASE MY_DB TO DISK = 'D:\BACKUP\MY_DB.bak' WITH INIT, STATS = 10;

Then, using the logical names you got from RESTORE FILELISTONLY, run the restore command. For example, if your logical data name is MY_DB and logical log name is MY_DB_log:

RESTORE DATABASE MY_DB_NEW 
FROM DISK = 'D:\BACKUP\MY_DB.bak' 
WITH 
    STATS = 10, 
    RECOVERY,
    MOVE 'MY_DB' TO 'D:\Microsoft SQL Server\MSSQL12.SQLDIVALTO\MSSQL\DATA\MY_DB_new.mdf',
    MOVE 'MY_DB_log' TO 'D:\Microsoft SQL Server\MSSQL12.SQLDIVALTO\MSSQL\DATA\MY_DB_new_log.ldf';

额外提示：自动化每日复制 #

To set this up as a daily automated task, you can create a SQL Agent Job:

• Add a step that runs the backup command
• Add a second step that runs the RESTORE FILELISTONLY (you can even use dynamic SQL to pull the logical names automatically if you want to avoid hardcoding)
• Add a third step that runs the restore command with the correct logical names

内容的提问来源于stack exchange，提问作者Walter Fabio Simoni