I need to edit a SQL database .mdf how to ?

I have a database which I need to edit becuase the database is a 'settings' database and this database has some links within that point to a old server that I need to change to the new server name.

Im using SQL Express 2005 but do have access to the full version if needed.

So i just need to open up the database and change any links to the old server to the new one.

Here is the settings database attached, the link to the old server is called haybrooksbs and the new one is called hbit5\sqlexpress.

I have attached the database with the extension changed to .mdb from a .mdf so i could attach it, please change the extension to .mdf before viewing

Thanks
ColleagueSettings.mdb
Ben CampbellIT User, Manager, DiplomatAsked:
Who is Participating?
 
Igor-KConnect With a Mentor Commented:
You can use sp_setnetname to rename the phisical server name, the link server points to.
For example
exec sp_setnetname
@server = 'haybrooksbs',
          @netname = 'hbit5\sqlexpress'

This will change link server haybrooksbs to point to 'hbit5\sqlexpress' instead of original 'haybrooksbs'.
The name will stay same, 'haybrooksbs'.
This is the easiest way if you have hardcoded the link server name in your queries.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify what you mean by "links"?

normally, a "link" would be a linked server, which can be modified without having to change the relevant code?
0
 
RiteshShahCommented:
don't change extention, use original .MDF file .

If you want to attach a MDF without LDF you can follow the steps below
It is tested and working fine


1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus

9. Restart SQL server and see the database is online.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Yeah it a link to a linked server.

I basically need to change the link to point at the new server.

As i moved the database over from a old server
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the linked server links are stored in the master db, not in user databases.

so, if you move the db to another server, you just have to script and recreate the linked server on that new server...
0
 
RiteshShahCommented:
If you have successfully move the database than don't need to run the script I gave you in previous post, I though you are not able to attach the .MDF file without .LDF file and that is why you changed its extension.

0
 
RiteshShahCommented:
you can simply remove old linked server by couple of click, have a look at screen shot.

Moreover, for creating new linked server have a look at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

I have used Access as a linked server, you can use your new sql server, just change provider and credentials.


linked.JPG
0
 
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
and how can i do that angellll ?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think, RiteshShah covered that part meanwhile
0
 
RiteshShahCommented:
right click on "Linked Server" option, click on "New Linked Server" and than follow given screen shot to create new linked server to your new server.



link1.JPG
link2.JPG
0
 
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
yes Igor that seems to be the best way for me. How would i execute that (sorry, i have hardly ever touched sql befire and have been roped into it)

Cheers
0
 
Igor-KCommented:
Hi James,
 Open SQL Server Management Studio,  open new query, connect to your dateabase server, run the query.
0
 
Igor-KCommented:
If you are not sure of what you are doing, it would be adviceable to try it in test environement if you have one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.