question

mituljain9 avatar image
mituljain9 asked

Database Recovery

My Sql Server instance is down and I am not able to connect to it through Management Studio. Users are telling that they are not able to connect to their database. Now, I need to bring the database up and recover to the point in time. I am taking full backup every night and transaction log backup every hour. How should I approch now?
backup-restorerecovery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
Is the instance back up? This is your first and foremost issue (if not) because you won't be able to do anything until that is resolved. Any errors in the event viewer? What have you tried already? If you try to restart the service what happens? At some point, you will also need to determine when the server went down as that will tell you when you'll need to restore to. Also, have you verified that your backups are valid? Have you restored them somewhere else?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Reminds me of "Don't worry about a backup plan - worry about a recovery plan." Because that'll dictate your backup plan for you... Good point about testing the backups by restoring them elsewhere - there's no point in having untested backups, or backups that can't be recovered.
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Thanks! I like the quote. Hadn't heard it in that way before but I'll definitely remember it!
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
First step: Can the instance be brought back up? Is it something as simple as someone switching off the server, or has something gone more seriously wrong? If you can't bring the server back online (within a reasonable timeframe), then you need to recover your backups to a new server/instance. First restore the full backup, then all the transaction log backups in the order that they were taken. If you can bring the old server up for long enough, try generating a tail-log backup - http://msdn.microsoft.com/en-us/library/ms179314.aspx - and restore that on your new server to bring everything back to the most recently committed transactional state.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mituljain9 avatar image
mituljain9 answered
no the instance is not up yet, when I try to connect the instance, I got the error: that network drives are inaccesible. And this might be due to the datafiles residing over there..... What can be done?? Also, the backups are good and tested on other server......
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
If the instance is not running then you need to locate it in the Services on the server and start it
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

inder avatar image
inder answered
1. I think the first thing to check is service ? Is SQL Server Service running ? 2. If service is running,try to connect to master database ? 3. If service does not start,restore master database from last backup ? http://msdn.microsoft.com/en-us/library/ms190679.aspx 4. Restart the service in multiuser environment and restore the user databases.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bobby28 avatar image
bobby28 answered
I would recommend you recover corrupted database owing to following software repair database sql utility restores databases since Microsoft SQL Server 2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
You said "My Sql Server instance is down and I am not able to connect to it through Management Studio" Please check the SQL Service is up and running, if not try to start the service. If this fail to start, follow the basic troubleshooting startup techniques - - Check the SQL Server accounts has the privilege to start the service. - Check the service account password is not expired. - SQL account should have permission in the master database file folders. - you are not missing any system database files including resource database files. - make sure that you have proper startup parameters for SQL Server(in sql server configuration manager), this include check for master database and log file location, error log location etc. - If you are running Anti-virus softwares in your server(not recommended though), check the master database file is not locked by the antivirus, if so give exceptions to the database files in your anti-virus software. If the service is starting try to start in single user mode by adding the parameter -m in the sql server startups, if it fails again try to start it from the command prompt in minimal mode sqlservr.exe -f -s <"your_instance_name_here"> If this started successfully try connecting sql server if it fails to connect and the server is up, connect using dedicated admin connection. If this steps fails check your errorlog for more details, and post us the errors, we will guide you.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.