Friday, September 28, 2012

How to Restore SQL Server Database with & without backup


It is quite common in real life that sometimes when you tried to open your SQL Server database either you are unable to open your database or it gives some error message. In most of the cases it happens due to database corruption. In this case either you have updated backup or you have not. If you have full backup then follow these steps to repair corrupt SQL Server database:

Step 1: Retrieve the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = 'D:BackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

Make Database to single user Mode

ALTER DATABASE YourFirstDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE YourFirstDB
FROM DISK = 'D:BackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

Without Backup:

I recently downloaded Stellar Phoenix SQL Database Recovery Software. I found many interesting things about this tool which caught my attention. The tool helps to repair SQL Server database when you don't backup. 

My Scenario:

Database get corrupt & you don't have any backup. The corruption can be just like these errors:  


Important Points to remember:

1. First Ensure that MS SQL Server is running during recovery process.
2. You should perform recovery process on the copy of your SQL Server database. 
3. First Try Demo Version of the software. 

I think this tool is very useful for SQL Server users, and it delivers a high level of performance way more than what I expected. If you are using this tool, I am eager to learn more about it and your experience while using it.

No comments:

Post a Comment

Post a Comment