Sql-server – How to we recover/restore lost/overwritten data in our MSSQL 2008 table

sql serversql-server-2008

I am in serious trouble and I am seeking professional advices here.

We are using MSSQL server 2008. We removed primary key, replaced exiting data with new data resulted losing our critical business data in its child tables on MSSQL Server. It was completely human mistake and we didn't have disk failure.

1) The last backup file was a month ago which means it is useless.

2) We created Maintenance Plans to backup our database at 12AM everyday but those files are nohwere to be found

3) A friend of mine said we can recover from Transaction Logs. When I go to Task>Restore> Transaction log is dimmed/disabled.

4) I checked Management>Maintenance Plans. I can't find any restored point there. It seems that our maintenance plan hasn't been working.

Is there any third party tool to recover lost/overwritten data from MSSQL table? Thanks a lot.

Best Answer

If your database is in full recovery mode and transaction chain is not broken (you have backup + transaction log since last full backup) then it might be possible to recover some or even all data by analyzing transaction log.

If transaction log is in full recovery mode then transactions are logged and can be read from t-log but unfortunately this is not an easy task.

Your options are to try and read this yourself using DBCC LOG command or third party tools such as ApexSQL Log or Quest Toad

Related Solutions

Sql-server – How to recover a MSSQL user’s password

Typically, this is done by starting SQL Server in single user mode and inserting a new sql admin user that you can then use to gain full access. I would avoid changing the sa password until you know if the application is using it or not.

Here is a link with instructions and screen shots for doing this with normal SQL Server. To put SQL Express in single user mode and open a query window goes something like this:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn> Sqlservr.exe -s SQLEXPRESS -m
C:\Program Files\Microsoft SQL Server\90\Tools\Binn> sqlcmd -S.\SQLExpress

Edit (in case of link rot): Once you have a query window open in single user mode you should be able to add a server admin login that you can use:

CREATE LOGIN MYADMIN WITH PASSWORD='myadminpassword'
SP_ADDSRVROLEMEMBER 'MYADMIN', 'SYSADMIN'

In your case the SQL Logins are enabled or you wouldn't be able to use your read only login, but for others they may need to ensure SQL Logins are enabled by checking or editing your registry (replacing MSSQL.1 with whichever instance you are after, but most likely it is .1). put this in a .reg file and run it (or use RegEdit):

-------------COPY BELOW---------------------------------
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000002
-------------COPY TILL THIS---------------------------------

And finally if your sa login is disabled, and you want to enable it (but don't do this if your application is using sa or you may break the whole thing):

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

Sql-server – restore a SQL 2008 database to a SQL 2005 server

No, you can't. The databse formats are incompatible. About the best you can do is script out the database and export the data. Note, though, that some things (like certain datatypes) that are new to SQL 2008 will throw your 2005 instance for a spin.

Best Answer

Related Solutions

Sql-server – How to recover a MSSQL user’s password

Sql-server – restore a SQL 2008 database to a SQL 2005 server

Related Topic