Nov 13
Basis Tricks

Basis Tricks: SQL Server Logical File Names

I frequently restore SQL Server databases while executing homogeneous system copies in SAP systems.  SQL Server lets me change the database name as I’m restoring, but not the logical file names.  It’s not technically necessary to change logical file names, but I prefer to see the names match the database name.

After the restore completes, you can change the SQL Server Logical file names with a graphical input in SQL Server Management Studio, but if  you want to use T-SQL commands, you can use the template below.  Run this on the database server of the target of the database copy.  Replace all instances of <TGT> with the name of the target database.  Replace all instances of <SRC> with the database name of the database you restored.

If your database has more than four data files or more than one log file, simply add more instances of the ALTER DATABASE command with the appropriate source and target file names.

use [<TGT>]
ALTER DATABASE [<TGT>] MODIFY FILE ( NAME = '<SRC>DATA1', NEWNAME = '<TGT>DATA1' )
GO
ALTER DATABASE [<TGT>] MODIFY FILE ( NAME = '<SRC>DATA2', NEWNAME = '<TGT>DATA2' )
GO
ALTER DATABASE [<TGT>] MODIFY FILE ( NAME = '<SRC>DATA3', NEWNAME = '<TGT>DATA3' )
GO
ALTER DATABASE [<TGT>] MODIFY FILE ( NAME = '<SRC>DATA4', NEWNAME = '<TGT>DATA4' )
GO
ALTER DATABASE [<TGT>] MODIFY FILE ( NAME = '<SRC>LOG1', NEWNAME = '<TGT>LOG1' )
GO

About The Author

David is a Senior SAP Basis Consultant at DataXstream

Leave a reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

This site uses Akismet to reduce spam. Learn how your comment data is processed.