• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • Last Modified:

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
0
Ben Campbell
Asked:
Ben Campbell
  • 4
  • 3
  • 3
  • +1
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Igor-KCommented:
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
 
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

Featured Post

Industry Leaders: 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!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now