2

I am a SQL Server DBA. I recently had the misfortune of losing a database due to drive failure. I was able to restore the data but for whatever reason, it stopped at a certain time and not all of the data was restored.

I restored the database again to a test server only to find and restore ALL of the data but the problem is, I now have to get the rest of the data that did not restore on to the server where it is supposed to be. Can that be done?

Given all the primary key contraints and uniqueness of those keys involved, imagine we will have to renumber some of the rows that were affected because those keys have been reused once the database came back up and was operating.

  • "It stopped at a certain time" seems fishy. Maybe you restored from an older backup? Any particular error message? You will have to decide between losing new data on the production server and correctly restoring the backup, or doing ETLs to bring the missing data (which can take very long depending on how complex your model is). You can also backup the current state, restore the previous backup and do the with the new data instead. – EzLo Apr 9 '19 at 6:37
  • I fully restored the database but it took a very, very long time initially. When it came up, that's when we noticed the data was missing. I will have to manually import the data that is missing back to production and append a number to all the primary keys. It has been quite bizarre. – dba Apr 10 '19 at 1:33
0

So you attempted to restore the database in production but it didn’t finish? Can you elaborate more, did it finish completely, we’re there any error messages, etc.

Then you were able to successfully restore the database to a test server?

Have you verified if the backup file is good? See this article if not

https://www.mssqltips.com/sqlservertutorial/113/checking-to-make-sure-a-sql-server-backup-is-useable/

You may want to check that and if ok try to restore in production again.

  • This should be in the comments - it's not an answer. – George.Palacios Apr 9 '19 at 7:46
0

If you were able to restore the backup fully to the test server you could:

  • Make a backup from test server and try to restore it to the production server.
  • Detach the database from the test server, delete the database on the production server and copy all of the data and log files to the production server and reattach on the production server.

This obviously won’t work if you’ve put the production server back into service and the data has been modified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.