| Author |
Topic |
|
paull
Yak Posting Veteran
50 Posts |
 Posted - 2010-09-02 : 06:57:08
|
| Hi all. I have searched for a similar problems on the site and found various suggestions to remedy the problems but in my case I have whole series of errors stopping my SQL Express service from starting. Here is the relevant extract from the log...2010-09-01 16:41:43.02 spid7s Error: 15173, Severity: 16, State: 1.2010-09-01 16:41:43.02 spid7s Login '##MS_PolicyEventProcessingLogin##' has granted one or more permission(s). Revoke the permission(s) before dropping the login.2010-09-01 16:41:43.02 spid7s Error: 912, Severity: 21, State: 2.2010-09-01 16:41:43.02 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15173, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.2010-09-01 16:41:43.02 spid7s Error: 3417, Severity: 21, State: 3.2010-09-01 16:41:43.02 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.2010-09-01 16:41:43.02 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.I am relatively new to SQL and my experience lies solely in writing select/update queries so the above text might as well be in Greek!Obviously one of the suggestions above is to repair/rebuild the Db; if I do this will I lose the data in the existing tables? And if so, is there anything I can do with the .mdf and .ldf files to recover the data?Sorry for all the detail! The data within the database is urgently needed for a project delivery so any ideas/help will be more than gratefully received!Many thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-02 : 07:09:18
|
| Has the MASTER of MSDB database file(s) somehow been copied from elsewhere? Has an upgrade been performed (service pack or the like?) I suppose maybe that could be set to happen automatically / unattended?You could try copying the MDF and LDF files for your application database to another installation of SQL Server on another machine - it will need to be the same version, or later - and then ATTACH your database there. Make sure that you have backup copies you can go back to - i.e. don't mess with the original files without first taking a copy.Do you have backups of: Your application database(s)MASTER databaseMSDB database??There are knowledgeable people here who will be able to give you a better answer than I can - hopefully they will be along shortly! |
 |
|
|
paull
Yak Posting Veteran
50 Posts |
Posted - 2010-09-02 : 07:20:08
|
| Hi Kristen - I dont know why or how there was an upgrade performed - according to the log it was 10.30 at night so I assumed it was some sort of automatic restore??!! Unfortunately I dont have any back-ups of the dbs/tables I need - I only copy them once a project is finished. My last resort plan is to save the mdf/ldfs somwhere, scrub the SQL off the machine and then re-install from scratch but I dont know if I can use the Data files?! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-03 : 05:18:50
|
| You should always have backups, even if the project isn't finished (especially then)You should be able to copy off the mdf and ldf, providing SQL isn't running. You'll need to attach them to the new instance after install.--Gail ShawSQL Server MVP |
|
|
|
paull
Yak Posting Veteran
50 Posts |
Posted - 2010-09-03 : 05:36:31
|
| Thanks Gail.I have now managed to "save" the mdf files and am in the process of re-installing the full version of SQL 2008. This time around I am going to try to set up some sort of automatic back up (its all a bit of a learning curve!).For future reference, any ideas as to what might have happened to the master db; i.e. why it crashed in the first place?Many thanks again |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-03 : 09:55:28
|
| save the ldf files too |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-04 : 05:51:44
|
| You need the ldf files as well. The transaction log is not an optional file, without it you may not be able to reattach the DB.No idea what happened to master, not enough info on what lead up to this.--Gail ShawSQL Server MVP |
|
|
|
xavierk08
Starting Member
1 Post |
Posted - 2011-10-03 : 16:14:32
|
| LDF is not a must to attach a database. Once you attach MDF then automatically a new LDF will be created. Make sure you remove the LDF path in the attach options.Cheers, XCheers, X |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-03 : 16:41:59
|
quote:
Originally posted by xavierk08
LDF is not a must to attach a database. Once you attach MDF then automatically a new LDF will be created.
Nope. SQL can create a new log file only if the database was cleanly shut down at the time the old log was deleted. If it wasn't cleanly shut down, attaching without a log file will cause the attach to fail.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-04 : 01:16:04
|
quote:
Originally posted by xavierk08
LDF is not a must to attach a database. Once you attach MDF then automatically a new LDF will be created.Cheers, XCheers, X
Will only work if you mention attach_rebuild_log or attach_force_rebuild_log option.PBUH |
|
|
|
|
Database crashed!!