Attaching A File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Attaching A File

Author

Topic

Spherix
Starting Member

4 Posts

Posted - 2010-12-24 : 01:31:14

Hey... For the life of me I can't figure out why I get an error when trying to re name this file so I can attach a new one.

^^^ This is the error I get ^^^

My first theory was that the file was in-use somewhere. It isn't.
I figured out if I attempt to delete this before I attach the new file then I get kicked out of the DB and cant re-connect till I re-install the files.

If anyone knows what my problem is or how to help me then please comment :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-24 : 01:32:37

What command are you running when you get that error?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Spherix
Starting Member

4 Posts

Posted - 2010-12-24 : 01:35:43

I'm just right clicking on the file and attempting to re name it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-24 : 01:37:46

It's a database, not a file, just to be clear.

You can not perform the action if anyone else or even yourself is connected to the database. Disconnect all users to that database and then try it again.

You can use master..sysprocesses (table) or sp_who/sp_who2 (stored procedures) to view current activity. There's also the activity monitor in the GUI.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Spherix
Starting Member

4 Posts

Posted - 2010-12-24 : 01:42:27

Im a bit on the, well stupid side when it comes to SQL I've been working with it for all of 6 hrs now and I have no clue what to open or what command to throw at it to re name this database, could you tell me ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-24 : 03:35:16

Use query analyser and run this

use master
Go
EXEC sp_renamedb 'Old_name', 'New_name'

If you get an error, run

EXEC sp_who

and see who are all connected to your old db

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-12-24 : 03:46:12

Well ... here is a belt & braces approach:


USE master
GO
ALTER DATABASE MyOldDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MyOldDatabaseName MODIFY NAME = MyNewDatabaseName
GO
ALTER DATABASE MyNewDatabaseName SET MULTI_USER 
GO

Note that this will only change the name of the database, not the physical names of the files, nor their logical names, which if left unchanged may be confusing in the future

Spherix
Starting Member

4 Posts

Posted - 2010-12-26 : 18:54:39

It worked for the re-name but I am now un able to connect to my database, and I need to attach a new database for it to work again :S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-27 : 00:29:34

What error are you getting? Are you able to connect to it via SSMS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources