SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 sql server backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rowter
Yak Posting Veteran

70 Posts

Posted - 05/03/2010 :  15:59:06  Show Profile  Reply with Quote
Hi,

I am using sql server 2005 Express Edition.
In sql server management studio, when i try to connect to a server, i am not allowed to login. The sqlserver service is stopped and this is the reason for login failure.
I am planning to uninstall the server and reinstall it.
My question now is how to take a backup.
Can i just copy the .mdf and .ldf files under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data ?

Thanks in Advance

Edited by - tkizer on 05/03/2010 15:59:41

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/03/2010 :  16:00:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes, however there are times when it doesn't work on the attach.

Why don't we try to fix the service issue rather than reinstalling it? What error is in the Application Log in Event Viewer for the service failure?

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

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/03/2010 :  16:02:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
Please do not post your question in multiple forums here. I will be deleting the duplicates. Let's use this thread for your problem.

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

Subscribe to my blog
Go to Top of Page

rowter
Yak Posting Veteran

70 Posts

Posted - 05/03/2010 :  17:35:30  Show Profile  Reply with Quote
Hi tkizer,

When i checked the services, sql server express is not running. It is stopped.
When i tried to restart it, i am getting the following error:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.


There were 3 different solutions posted at this link:
http://www.tomrafteryit.net/sql-server-2005-express-error-3417/

But none of them worked for me.
1) right click sqlserver under administrative tools -> services and click properties and click on lgon tab and select local and the check box below.
This did not work
2) Problem may be due to compressed files in mssql->data . I checked here and the files were not compressed.

3) Some one suggested to run the sqldiag.exe under 90->tools->Binn-> SQLDiag.exe
None of these worked for me.
Any idea why i am not able to start the service?

I also checked the permissions for the user. The user has required permissions.

About reposting: sorry about that.
I did not see my post so, i reposted it earlier.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/03/2010 :  17:38:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
You haven't posted what was found in the Event log. The error mentions checking it plus I mentioned checking it too. Getting the error from the service is not enough.

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

Subscribe to my blog
Go to Top of Page

rowter
Yak Posting Veteran

70 Posts

Posted - 05/03/2010 :  17:43:20  Show Profile  Reply with Quote
Hi Tkizer,

The event log that you mentioned , how can i access it?
From the mangement studio, i cannot access it as i cannot log into it.
Any other way to getit?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/03/2010 :  17:59:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
It is not part of SQL Server. It's part of the OS. Go to Control Panel..Admin Tools..Event Viewer. Find the stop error associated with the service failure. The error should be in the AppLog, although check the SysLog too.

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

Subscribe to my blog
Go to Top of Page

rowter
Yak Posting Veteran

70 Posts

Posted - 05/05/2010 :  15:40:37  Show Profile  Reply with Quote
Hi tkizer,

This issue was see on one of the test systems which i do not have access to and so the delay.
I found these 2 Event ID's in the event viewer.

Event ID: 3417
Cannot recover 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.


Event ID: 9003
The Log scan number (225:376:1) 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, recreate the publication.
Otherwise, restore from backup if the problem results in a failure during startup.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/05/2010 :  15:48:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yikes, sounds like your system is corrupt. Hopefully you have backups and can restore them.

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

Subscribe to my blog
Go to Top of Page

rowter
Yak Posting Veteran

70 Posts

Posted - 05/05/2010 :  16:08:07  Show Profile  Reply with Quote
Hi tkizer,

This is a test system which we placed at one of our client places to test our software.
I am not sure how fequently they used the software. They definitely have some data in it(in the range of 200 - 500 records)
I am not sure if they have backups or not.
Is there any way to recover the data?
If not, i will uninstall the whole thing and reinstall it.

Thanks.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/05/2010 :  16:14:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
Recovery would happen from backups. You could try copying the database files to another location and then attempting to attach them after the reinstall, but that's unlikely to work.

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

Subscribe to my blog
Go to Top of Page

rowter
Yak Posting Veteran

70 Posts

Posted - 05/05/2010 :  16:21:05  Show Profile  Reply with Quote
ok. Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 05/05/2010 :  16:39:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
Sorry I don't have a better answer for you, but glad to help.

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

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000