Alter Database

Question

Alter Database

Minnu

SSCertifiable

Points: 6451
More actions
September 16, 2015 at 12:02 am

#304512

Hi Team,
I have a database with Name 'Marketing' and i have created another database 'Marketing1' in same instance.
i want to bring the 'Marketing' database offline and alter 'Marketing1' to 'Marketing' .
Please suggest

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

Minnu (9/16/2015)
Hi Team,
I have a database with Name 'Marketing' and i have created another database 'Marketing1' in same instance.
i want to bring the 'Marketing' database offline and alter 'Marketing1' to 'Marketing' .
Please suggest

Quick suggestion

😎

USE master;

GO

/* Set in single user mode to terminate any connections (just in case) */

ALTER DATABASE [database_name] SET SINGLE_USER WITH NO_WAIT

GO

/* Take the original database off line, this step is not necessary as the

database must be detached in order to do the renaming of the new

one

*/

ALTER DATABASE [database_name] SET OFFLINE WITH

ROLLBACK IMMEDIATE;

GO

/* Detach the database, attempting to rename will fail even if

the database is off line, it must be either dropped or detached

*/

EXEC sp_detach_db @dbname='[database_name]', @skipchecks='skipchecks';

GO

/* Rename the new database */

ALTER DATABASE [database_name]

Modify Name = [new_database_name] ;

GO

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 2

Perhaps change the logical names of, what was, the Marketing1 database too (after the rename)

Personally I'd just use the

ALTER DATABASE [Marketing] SET OFFLINE WITH ROLLBACK IMMEDIATE

rather than first setting it to SINGLE_USER (or maybe the SINGLE_USER command was for the Marketing1 database? Maybe that needs to happen before it can be renamed, in which case I think it will need setting back to MULTI_USER afterwards)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You'll have to change the name of Marketing before you can rename Marketing1. Even offline, it's still a database on the instance and database names have to be unique.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 4

GilaMonster (9/16/2015)
You'll have to change the name of Marketing before you can rename Marketing1. Even offline, it's still a database on the instance and database names have to be unique.

🙂

In which case perhaps just rename "Marketing" or "Marketing_OLD" and then "Marketing1" to "Marketing"? 🙂

I think I would still want to sort out the Logical names ... actually thinking about that: what about the physical files? Assuming they are, currently, called Marketing1.mdf / Marketing1.ldf isn't that going to be confusing, going forwards, when the database name is changed to "Marketing"?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

You'll want to use MODIFY FILE in your ALTER DATABASE command. See https://msdn.microsoft.com/en-us/library/bb522469%28v=sql.110%29.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Minnu (9/16/2015)
i want to bring the 'Marketing' database offline and alter 'Marketing1' to 'Marketing' .
Please suggest

This is not possible as the offline database is still recognised by the server and the database names have to be unique. You' need to either drop the database or detach it first to rename the other database

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉