?
Solved

SQL Server broke after Computer name change

Posted on 2009-04-17
11
Medium Priority
?
1,822 Views
Last Modified: 2012-05-06
Hi I have a problem with SQL Server 2008: I have installed (inc SP1) - running fine on a new machine then I changed the Computer Name.
SQL Server still ran fine but I can't access the Relationship diagrams - it came up with error message saying Go to DB Properties/options and Change Owner, and thats where the problem starts:
 I cannot change it because the It only 'knew' about user of the old computer  ( stand alone SQL Installation - workgroup computer)
So Re-installed SQL Server and fine the Computer name is correct, but now I am having a problem attaching to the original database: it comes up with: "An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details"
so I click hyperlink and: Error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'JUPITER'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2520.0+((Katmai_PCU_Main).090214-0043+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Unable to open the physical file "C:\Data\Dev\CMS\3.0.0.0\db\DATA\CMS.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2520&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476
------------------------------
BUTTONS:
OK
-----
Follow that link and a Were sorry no additional information....

I have searched around and I reckon I should not have reinstalled, but now I have ... and I have a problem !

Can any body help?  If I can just attach that will be fine.
0
Comment
Question by:10Pints
  • 6
  • 4
11 Comments
 
LVL 6

Accepted Solution

by:
bcsql earned 1500 total points
ID: 24170895
"I reckon I should not have reinstalled" - correct you did not need to reinstall

"C:\Data\Dev\CMS\3.0.0.0\db\DATA\CMS.mdf" -is this the correct name and path to the file .mbf file for the database you are trying to attach? and does the account that runs msssql have permissions to the file
0
 

Author Comment

by:10Pints
ID: 24171326
Hi!  thanks for your reply: I reckon you are right it could well be to do with the permissions: the file defienitely exists because I tried to attach it using the GUI and browsed for it form SQL server Management studio.

Not sure about what I need to do set the permissions - is this a windows file  system thing or part of SQL server?
0
 
LVL 6

Expert Comment

by:bcsql
ID: 24181743
it seems like it could be a permissions issue with you or the instances.
Are you an sysadmin on the instance? did you re add yourself as a sysadmin after the install.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:10Pints
ID: 24182791
hi, sorry for delay, yes I should I instsalled it ....
0
 
LVL 6

Expert Comment

by:bcsql
ID: 24197898
not sure what your replay means. so
1) Are you an sysadmin on the database instance?

0
 

Author Comment

by:10Pints
ID: 24199443
Yup, sorry for confusion:
just checked security/logins/me/roles: includes sysadmin

0
 
LVL 6

Expert Comment

by:bcsql
ID: 24207639
OK now does the account that runs the mssql service have write access to the database file location?
0
 

Author Comment

by:10Pints
ID: 24208884
Question is this the same as in Control Panel/Admin Tools/Services/ MSSQLSERVER service?
If so then yes it is the same i.e. me
0
 

Author Comment

by:10Pints
ID: 24213175
Mornin!  Any thoughts wise one?
0
 

Author Closing Comment

by:10Pints
ID: 31571573
Thanks this is to do with permissions of the SQL ServerManagement exe - if you right click an go properties/security and adjust permissions - it works - the glories of Vista/Windows 7
0
 
LVL 3

Expert Comment

by:mail2sanjays
ID: 25229851
Hi,

Could you again rename your computer to the old name ..
then restart the computer & check the sql server services...
Then try to attach the files..again ..
If get succeded then...
Run following
Use DBName
EXEC sp_changedbowner '<Your login Name Here>'  --or use 'sa'
GO


now take a full Back up of the DB...

Now again rename to the current Name (The name you are using now)
start sql server...

restore the database from the backup file (before this put the old mdf & ldf files to new location..)

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question