Problems renaming a database. 1

[SantaMufasa]

We backup a database and then restore it to a database with a different name using "restore with move". Now when we backup database-2 and try to restore it, it get errors that the database is not found. We used "restore filelistonly" to discover that database-2 still shows the name from database-1. We need this name to be changed; we assumed (incorrectly) that the "restore with move option" took care of naming the restored database to database-2.

Could someone please advise us on how to properly restore a "database-1" to "database-2"?

Thanks

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mrdenny]

When you are doing the backup of database-2 are you backing up to the same file or a different file?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SantaMufasa]

We are backing up to a different file name of our choosing.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mrdenny]

Are you talking about the logical names of the datafiles within the database, or the database names?

The with move option will only change the physical filename not the logical file name. You have to change that using an ALTER DATABASE command before backing up database-1.

You have Database-1 with logical files named Database-1_data and database-1_log. Those logical files point to D:\MSSQL\MSSQL\Data\Database-1_data.mdf and D:\MSSQL\MSSQL\Data\Database-1_log.ldf respectivaly.

When you backup the database and restore it using the WITH MOVE options you now have a database named Database-2 with logical files name Database-1_data and Database-1_log. Those logical files point to D:\MSSQL\MSSQL\Data\Database-2_data.mdf and D:\MSSQL\MSSQL\Data\Database-2_log.ldf.

In order to change the logical names to Database-2_data and Database-2_log you would need to use code along these lines.

ALTER DATABASE [Database-2]
MODIFY FILE (Name='Database-1_data', NewName='Database-2_data')
go
ALTER DATABASE [Database-2]
MODIFY FILE (Name='Database-1_log', NewName='Database-2_log')
go

The reason for this is that while the logical files are typically the same as the physical file names, there is no requirment that they be the same. Because of this we can easily move the files to a different physical name (path and file name), but if you want them to match you have to then change the logical file names.

Fornitually just about the only times you need to use the actual logical file names is when restoring a database or manually expanding a datafile (or a few other commands via the ALTER DATABASE n MODIFY FILE command.

When in question you can query for the list of files via:

SELECT *
FROM sysfiles

SELECT *
FROM sys.database_files

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SantaMufasa]

Denny,

Your explanation was excellent. From it, we were able to sort out our problem. Please accept from our team this

for your help.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mrdenny]

Not a problem. Welcome to the fun of SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com