June 1, 2006 at 2:24 pm
This is embarrassing, but while I was restoring a database to a SQL Server 2005 database from a .bak file, I accidently restored it to overwrite the master database. Worse, is that I did this over two weeks ago and at the time I didn't even realize I had done it.
I need to restore the master database now. I have googled for a while and I am not finding what I need. If anyone knows how to do this, I would greatly appreciate any hints or advice! Thanks.
Neil
June 1, 2006 at 3:15 pm
Hi Neil,
I'm assuming you have a backup of the master database.
Stop the SQL Server services and go to the command prompt.
Change the directory to the following (assuming this is a default installation):
cd\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
You'll then need to start SQL Server in single user mode using the following command:
sqlservr.exe -m
You can then log onto the SQL Server and run your restore database command:
restore database master from disk = '.....'
Once you're done, go back to the command window and press Ctrl-C to stop the SQL Server service and restart the services as per usual.
If you don't have a backup of master you'll have to rebuild the master databases using the SQL Server 2005 setup utility.
Hope that helps. And good luck.
June 1, 2006 at 3:41 pm
Well it's only a test box, so we didn't worry about that. We of course backup everything on our production servers. We don't have any SQL 2005 production servers yet. We only have a test server that we are working with to develop a couple new projects.
June 2, 2006 at 1:56 pm
I am curuous. How can you over-write the master database when you are not in single-user SQL Server startup mode ? Has SQL 2K5 changed this ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 2, 2006 at 4:36 pm
That's a really good question Rudy. It never even occurred to me.
Now I'm curious too.
June 2, 2006 at 10:12 pm
I actually dont remember when I did this. All I know is that a .bak file comes with this content management system software and I must have accidently restored the backup to the master database. So all of the tables were in the master. I didn't realize I did that until a couple of weeks later. I used the overwrite option. I was logged in as sysadmin. It's only a test server so it wasn't the end of the world, and we experienced no negative effects on either of the other two databases in development. I am not all that experienced on either 2000 or 2005 and I am at the point I learn a lot every day. I've only been working full time for almost 2 years now and I also develop ASP.NET web apps VB.NET windows apps, and do other odd jobs like install content management systems. So perhaps I did no harm, I have no idea.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply