November 25, 2024 at 12:00 am
Comments posted to this topic are about the item Renaming a Database
November 25, 2024 at 8:23 pm
MS needs to update the documentation.
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver16 says, "Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type." No mention of being able to change the database name itself. Later, when discussing the parameters, it does include "DATABASE" as an allowable value for the objtype parameter.
November 27, 2024 at 1:27 pm
In the praxis you want to rename the files / folder too, since it is usually very inconsistent to have a database ArchiveSales with OldSales.mdf / ldf
Therefore for a clean system you want to execute the following commands (including the manual task in the second comment)
ALTER DATABASE ArchivSales MODIFY FILE (NAME=OldSales, NEWNAME='ArchivSales', FILENAME='d:\SQL\ArchivSales\ArchivSales.mdf');
ALTER DATABASE ArchivSales MODIFY FILE (NAME=OldSalesLog, NEWNAME='ArchivSalesLog', FILENAME='d:\SQL\ArchivSales\ArchivSales.ldf');
-- repeat this for every other file in the database (usually with the file extension ndf)
ALTER DATABASE ArchivSales SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- now manually rename the files / folder in the directory on the server (or execute a bunch of ren commands or a PowerShell script if it is too much for manual processing)
ALTER DATABASE ArchivSales SET ONLINE WITH ROLLBACK IMMEDIATE;
God is real, unless declared integer.
November 27, 2024 at 2:42 pm
I was about to post the same comment but looks like Thomas beat me to it...
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply