INF: Using Logical Names in SQL Server 7.0 (251281)

SUMMARY

SQL Server 7.0 introduces a new concept termed logical name. There exists one logical name for every file (.mdf, .ndf, .ldf) and the system can use the logical name to identify a file. The definition of a logical name is stored inside the file and is generated automatically by SQL Server. The default logical name is the filename without a file extension. The logical name is used only for administrative functions and generally will not need to be changed when a database name is changed.

MORE INFORMATION

Logical names must be unique within a database. Two different databases can have a file in each database with the same logical name but the physical names are different.

Here is an example:

Create a backup on disk for the pubs database using the following code:
```
BACKUP DATABASE pubs to disk = 'e:\pubs.dat'
```
Verify the logical names. You can see pubs and pubs_log, the two logical names associated with the two physical files in the pubs database:
```
RESTORE FILELISTONLY from disk = 'e:\pubs.dat'
```
Next, create a new database named new_pubs by using the RESTORE command. The new_pubs database is created with the same logical names as those of the pubs database. However, note that the physical names are different.
```
RESTORE DATABASE new_pubs from disk = 'e:\pubs.dat' with replace,
MOVE 'pubs' to 'e:\mssql7\data\newpubs.mdf',
MOVE 'pubs_log' to 'e:\mssql7\data\newpubs_log.ldf'
					
```

NOTE: While there is rarely the need to change the logical file name, SQL Server 2000 does introduce the functionality to do so. For more information, see the "ALTER DATABASE" topic in SQL Server 2000 Books Online.