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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 how to detach "master" database ?

Author  Topic 

learningsql00
Starting Member

28 Posts

Posted - 2012-07-10 : 10:23:45
My "master" database has been corrupted so what I am doing is, copying master database from other server and pasting it in my current server.

When I try to copy the "master" database from my other server, it gives me error saying "sql server is still using the database" so i guess i need to detach the "master" database first but i don't see any option in the "task" to detach the master database ?

please help ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-10 : 11:27:21
You cannot detach master as SQL Server cannot run at all unless master is available. You'd have to stop the SQL Server service to copy over files.

And what you're proposing is not a good idea either. For one thing, what kind of corruption did you encounter? Why aren't you restoring master from a backup? (You are backing up master regularly, right?) It's very likely that copying over files from another server will not fix your problem and could indeed make it worse.
Go to Top of Page

learningsql00
Starting Member

28 Posts

Posted - 2012-07-10 : 11:40:00
how to backup or restore master ? do you have any link or tutorial that I can follow ?
no i am not taking backup of master regularly, no backup plan
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-10 : 11:48:54
How to back up a database:

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups
http://msdn.microsoft.com/en-us/library/ms187510.aspx

How to restore master:

http://technet.microsoft.com/en-us/library/ms190679.aspx (there are additional links inside)

And you didn't answer my question about what kind of corruption you had. How do you know master is corrupted? Did you run DBCC CHECKDB? What was the output?
Go to Top of Page

learningsql00
Starting Member

28 Posts

Posted - 2012-07-10 : 11:59:59
but the problem is ..,master database is corrupted, so I can't even login to sql server then how am i supposed to take the backups ?

that is the reason i was thinking about replacing the master database from other server.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-10 : 12:46:17
Did you check the Windows Application log? My point is to get some kind of information as to what happened before you make permanent changes. You could have a hardware or disk problem that could end up corrupting a new master database.

Before you do anything else, stop the SQL Server service and make file copies of all your user database MDF and LDF files to another server. You can skip tempdb but make sure to get model and msdb. You may also want to copy the master files for later analysis.
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-07-10 : 13:58:31
If SQL Server is up and running - why do you think the master database is corrupted? Are you getting errors in the event logs that indicate corruption? Are you getting messages from SQL Server stating the database is corrupt?

You would only get those kinds of errors if SQL Server was not able to start - or you were running an integrity check on the database. Since you have no backup plan, I am assuming you also are not running any integrity checks.

It sounds like you are not able to login and believe that the reason you cannot login is because the database is corrupt. That is most likely not the case and is just a matter of a problem with your login.
Go to Top of Page

learningsql00
Starting Member

28 Posts

Posted - 2012-07-10 : 15:29:35
The reason I said, my master database is corrupted because i did check my event log, please see the screenshots below:





Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-10 : 16:04:52
Rob and Jeff, here's yesterday's thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176587

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

Subscribe to my blog
Go to Top of Page

learningsql00
Starting Member

28 Posts

Posted - 2012-07-10 : 16:29:28
thank "tkizer" for connecting both threads
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-10 : 16:33:07


Rob and Jeff, the issue is now resolved.

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

Subscribe to my blog
Go to Top of Page

learningsql00
Starting Member

28 Posts

Posted - 2012-07-10 : 16:47:10
thank you for all your help
Go to Top of Page
   

- Advertisement -