Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
See corresponding blog post at http:/natethedba.wordpress.com/adventures-in-database-renaming .
/* NJohnson9402 / natethedba.wordpress.com
An example of how to rename a database by work-around involving backup, restore, detach, attach.
Basically the idea here is that 'OriginalDB' is in heavy use, and can't tolerate much "downtime",
but "readonly time" is OK for a bit.
Drive letters for clarification in this case: 'D' for slow data, 'L' for slow tlog,
'E' for fast data, 'M' for fast tlog. The 'fast' storage system is our destination.
*/
USE master;
EXEC OriginalDB.sys.sp_helpfile;
ALTER DATABASE OriginalDB SET READ_ONLY WITH ROLLBACK AFTER 3;
GO
BACKUP DATABASE OriginalDB TO DISK = 'E:\OriginalDB_for_Flash.bak'
WITH COMPRESSION, INIT, CHECKSUM, STATS = 10;
GO
RESTORE DATABASE TemporaryDB FROM DISK = 'E:\OriginalDB_for_Flash.bak'
WITH CHECKSUM, RECOVERY
, MOVE 'OriginalDB_Data' TO 'E:\Data\OriginalDB_Faster_Data.mdf'
, MOVE 'OriginalDB_Log' TO 'M:\Log\OriginalDB_Faster_Log.ldf'
, STATS = 10;
GO
--wait for the green-light
USE master;
SET DEADLOCK_PRIORITY HIGH; --this helps avoid being a deadlock victim if the DB is still "busy"
ALTER DATABASE OriginalDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC master.dbo.sp_detach_db @dbname = N'OriginalDB'
EXEC master.dbo.sp_detach_db @dbname = N'TemporaryDB'
GO
CREATE DATABASE [OriginalDB] ON
( FILENAME = N'E:\Data\OriginalDB_Faster_Data.mdf' ),
( FILENAME = N'M:\Log\OriginalDB_Faster_Log.ldf' )
FOR ATTACH
GO
CREATE DATABASE [OriginalDB_old] ON
( FILENAME = N'D:\Data\OriginalDB_Data.mdf' ),
( FILENAME = N'L:\Log\OriginalDB_Log.ldf' )
FOR ATTACH
GO
ALTER DATABASE OriginalDB_old SET READ_ONLY WITH ROLLBACK IMMEDIATE;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.