How to make a backup (.bak) file with desired physical / logical names

Transact-SQL
1

Hello,
I have a Database called AxDB.
The physical file names for it is "AxDB_XX.mdf" and "AxDB_XX_Log.ldf".
And the Logical names "AxDBBuild_data" and "AxDBBuild_Log".

Is it possible to write a backup script which will create a .bak file with different names?

For example, after, when I want to restore the .bak file, I want that it has different physical and logical names by default...
AxDB.mdf instead of AxDB_XX.mdf and AxDB_Log.ldf instead of AxDB_XX_Log.ldf.
And
AxDB instead of AxDBBuild_data and AxDB_Log instead of AxDBBuild_Log.

If you could provide the script will be much appreciatted.
Thanks

2

image
image797×182 8.15 KB

3

Hi,
if I do like this:

BACKUP DATABASE [AxDB]
FILE = 'AxDBBuild_Data', 'AxDB',
FILE = 'AxDBBuild_Log', 'AxDB_Log'

it gives error message: "Incorrect syntax near 'AxDB' "

4

FILE = 'AxDBBuild_Data', 'AxDB.mdf',
FILE = 'AxDBBuild_Log', 'AxDB_Log.ldf'

5

No, it isn't.

You could generate a RESTORE command with the proper WITH clauses to adjust the new physical names to whatever you wanted. And, after the RESTORE, you could rename the logical names to anything you wanted (and you could of course pre-generate the commands to do those renames as well).

1 Like