| Author |
Topic |
|
Tanquen
Starting Member
5 Posts |
 Posted - 2014-04-08 : 11:46:29
|
| I get the error below when the SQL server tries to start. I think its the master files as I can not start the server or Management Studio. They have backups of the SQL server files but not BAK files just Symantec files backups of the SQL Server folder. I’ve tried replacing the master.mdf and mastlog.ldf files from the backups but I still get the same error with different log scan numbers. Seen this before, any ideas?The log scan number (137:128:37) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-08 : 12:19:35
|
| Yikes. Backups of the SQL Server database files are not the same thing as backups of the databases. I would probably try to rebuild master. If that doesn't work, then I would reinstall SQL Server and then stop the service and try replacing the database files for everything except for the master database.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Tanquen
Starting Member
5 Posts |
Posted - 2014-04-08 : 12:56:25
|
| Yikes indeed. I’ve not massed with SQL Server in ages and the last time I set one up we used NT backup to make (I think) proper backups of the SQL databases. I do see some BAK files in the MSSQL\Backup folder but it looks like they are of the project databases and not anything else. How should they be scheduling BAK backups of the project databases and others needed like master and model? Anyway...I’ve seen some stuff on rebuilding the master database and it’s all over the place. With reinstalling SQL server somewhere else and coping the good files (wouldn't the backups of the files be the same as this?) or getting the master files from the template folder that I don’t see on this system. Even some suggesting that the error is caused by bad/changed user credentials. One of the rebuild guides starts out with running the SQL server in single user mode with the –m modifier and that did not work. What is the best or correct way to try and rebuild or repair the master database? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-08 : 13:15:20
|
| I would open a case with Microsoft as this will be a very involved task. It could be days of back and forth on this thread.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Tanquen
Starting Member
5 Posts |
Posted - 2014-04-10 : 13:46:43
|
| This is what happened. Not sure why all the logins came back though. ???After making a number of attempts to correct the “master is not valid” error and start the SQL server I used the Symantec NetBackup software on the server to restore the complete Microsoft SQL Server program folder from 3-28-2014. This was the last full backup of the SQL server install folder and this finally allowed the SQL server to start but Wonderware Information Server and IndustrialSQL Server still would not start. While the SQL server was running any attempt to connect failed with bad login credential errors. Running the SQL Server Manager I was also unable to login with the normal Windows user name and password. I did not know the standard SQL server account “sa” password but the account added by (I think) the Wonderware IndustrialSQL install (wwAdmin) did work. Normally there would be a hand full of accounts in the Logins section under Security in the SQL server required for Information Server, IndustrialSQL Server and any other application that was reading from or writing to a SQL database but there were only two, “sa” and wwAdmin. While I was able to login with the wwAdmin account it was under the Public server role and did not have rights to add the other accounts back in and I had no real way to know what all the accounts had been. I was able to find some information on adding accounts to different server roles using console commands while the SQL server is in single user mode. Using the procedure below I was able to add the wwAdmin account to the sysAdmin SQL server role. After this the other 29 SQL server accounts were back in the Logins section and I was able to test the Information Server web page and the trend data and confirm they now worked.1.Configure SQL Single-User Mode- Open SQL Server Configuration Manager - Stop the SQL Server Instance you need to recover the SA password - Open the properties on the SQL Server Instance and click on the Advanced tab - Change the Startup parameter by adding -m; at the begging of the line and click OK - Start the SQL Service Instance 1.Use SQLCMD to add a Sysadmin Account- Open the command prompt - Run sqlcmd and press enter - Run a Transact-SQL (Below) command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add. *NOTE: if you receive errors, I’ve listed some common issues at the end of this article.EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin'; GO 1.Un-configer SQL Single-User Mode- Open SQL Server Configuration Manager - Stop the SQL Server Instance - Open the properties on the SQL Server Instance and click on the Advanced tab - Change the Startup parameter by removing the -m; at the begging of the line and click OK - Start the SQL Service Instance 1.Reset sa Password- Open Microsoft SQL Server Management Studio and login with the account you added - Under the DB, expand Security, then Logins - Open the properties for the sa account, and reset the password |
|
|
|
elliswhite
Starting Member
36 Posts |
Posted - 2014-05-07 : 02:34:48
|
| Have u checked your credentials before connecting to the sql server, were that filled correctly, if yes then it might be possible that your database have been damaged. U can not replace files like this way. U can recover ur damaged database by using any software. |
|
|
|
Tanquen
Starting Member
5 Posts |
Posted - 2014-05-07 : 10:32:49
|
| Sorry, I'm not sure what you are saying. I did what I said I did and that worked. Are you saying it did not really work and I can't really connect? ?:)? |
|
|
|
elliswhite
Starting Member
36 Posts |
Posted - 2014-05-08 : 01:48:30
|
| I have answered of your query didn't u got understand here's the link which will help u more http://www.sqlrecoverysoftware.net/blog/sql-login-error-18546.html |
|
|
|
Tanquen
Starting Member
5 Posts |
Posted - 2014-05-08 : 11:07:23
|
| Sorry, I answered my own question a few posts back as no one had an answer for me. Your post and link don’t really help. At one point there was an issue logging in but that was fixed almost a month before your post. ??? Do you think I still can’t log in? I can. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-08 : 11:46:18
|
quote:
Originally posted by elliswhite
I have answered of your query didn't u got understand here's the link which will help u more http://www.sqlrecoverysoftware.net/blog/sql-login-error-18546.html
elliswhite, please refrain from replying to old posts. This one wasn't too old, but you didn't read the entire thread to see the issue has been resolved. The other ones you have replied to have been very old posts.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Master database corrupted?