locked
Restoring SQL server after hard-drive crash RRS feed

  • Question

  • Our SQL server database has severe hard-drive failure. The RAID has been rebuilt and OS has been installed. The 2008 R2 installation has been on D:\Program Files where all files are intact (restored from backup) but C: drive image based restore had failed and hence OS has been installed fresh. How can I get all instances of previous installation back. (Just FYI: SQL server was installed first with SP2 and later SP3 was applied - but all this prior to crash)

    Here is my thinking: Install SQL server again (so that all registery keys are proper), Apply patch and restore (using mgmt. studio) each database from backup. I do have .bak for all databases in backup folder of files and it needs I can get it again from backup. I will have to do this including master, model.msdb ReportServer, ReportServerTempDB, as will right? Is my thinking correct? or Is there a better way to just say during reinstall that repair my previous installation as all installation files on D: drive is intact.

    Thanks in advance for you answer.

     

    rmrrdesai

    Thursday, October 13, 2016 11:17 PM

All replies

  • You will not be able to do a repair - that would be an excellent idea if you could as you would be able to bring SQL Server back.

    You will need to do a fresh install and then try to restore the system database and then the user databases. Here is a link on how to do that:

    https://msdn.microsoft.com/en-us/library/ms190190.aspx

    https://www.mssqltips.com/sqlservertip/2571/restoring-sql-server-system-databases-msdb-and-model/

    https://msdn.microsoft.com/en-us/library/ms190679.aspx

    If you are able to restore the master database all your user databases should be mounted and accessible after a restart of the SQL Server service.

    Thursday, October 13, 2016 11:38 PM
  • Hi Desai,

    As per Hilary said, Do fresh installation

    Restore mater database 

     SQL SERVER – Start SQL Server Instance in Single User Mode.

    Once SQL Server instance is running in single user mode, immediately connect  and run following command to restore the master database.

    RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.bck' WITH REPLACE;

    http://blog.sqlauthority.com/2009/02/14/sql-server-restore-master-database-an-easy-solution/

    Ensure exclusive access --> mention in <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7a19284d-8e31-41a4-a7f7-4d3a2ec11208" id="4a9f7290-ef46-44bf-bda0-c061df93e669">link</gs> for <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7a19284d-8e31-41a4-a7f7-4d3a2ec11208" id="fa54102c-2e4d-4043-a8a4-b926dbeeee75">msdb</gs> restore

    https://www.mssqltips.com/sqlservertip/2571/restoring-sql-server-system-databases-msdb-and-model/



    Please click Mark As Answer if my post helped.




    • Edited by AV111 Friday, October 14, 2016 12:39 AM
    Friday, October 14, 2016 12:34 AM
  • It's like you completely new build and most of the binaries of sql related to gone & as other said you need to to fresh install of  how the previous was here for sql server(i, you are sql server +Service pack(before your crash the applied SP was in good means the sql server & db's didnt find any issues) then you need to create a similar directory structure to place the files i,e for your system & user dbs files for the same.

    but it is good that you have known backups!

    but ensure you have uninstalled if any any components exists,before install .

    Uninstall SQL Server 2012
    https://technet.microsoft.com/en-us/library/hh231731(v=sql.110).aspx
    How to manually uninstall SQL Server.
    http://www.sqlcoffee.com/Tips0026.htm

    repair dont go for it,as it mismatches lot ,go for fresh install.

    but note that some of the things you may loss before the crash & that should be accepted based on our backup strategy.


    Regards, S_NO "_"

    • Proposed as answer by Teige Gao Friday, October 14, 2016 7:31 AM
    Friday, October 14, 2016 3:15 AM
  • Thanks for superb support from forum. My guess was right, i.e., I will need a fresh install (BTW, there is nothing uninstall as it is a fresh OS install after hard-drive crash). Here are the steps:

    1. Install SQL Server first with SP2 and apply SP3.

    2. Restore master db first, followed by msdb, model, ReportServer, ReportServerTempDB, and all of my other databases. I have verified that I do have good back up of all databases. BTW: Thanks to Hilary for providing links to perform these steps cleanly - which will definitely save my time!

    3. I will verify that all my databases are fine with access. It appears that I might have give all required rights again. Also, verify that all my scheduled jobs will run also fine.

    I have developed many DTS (aka SSIS) packages to perform various things (using VS2008). Also, Windows App as well as Web based app (using VS2010) and all of these are accessing the above mentioned SQL databases. I know all projects of VS2010 are under my 'Users\Documents' directory. I should be able to restore them after installation of required VS software as well. right?


    rmrrdesai

    Friday, October 14, 2016 2:54 PM
  • I have finally finished rebuilding server with brand new all drives. Installed OS. SQL server and VS2010. Recovered all SQL server databases. But now I am getting error 15466 (An error occurred during decryption) while executing previously running stored procedure. Many of the scheduled jobs are failing also with this error. Is there a way to get rid of this error? My prelim efforts has not helped me so far. Thinking am I the first one to restore SQL server databases..please help! (No exported keys are available as the server was crashed with hard-drive failure) Thanks in advance for your help!

    If I have to start a new post, please let me know! Thanks!


    rmrrdesai

    Thursday, October 27, 2016 5:09 PM
  • Hopefully the master key and its password were saved in the now gone file system and also somewhere else.

    Without it you will be unable to decrypt encrypted columns.

    Thursday, October 27, 2016 5:20 PM
  • I have file based backup prior to failure but no idea where master key would have been saved. No attempt was made to explicitly save the master key..


    rmrrdesai

    Thursday, October 27, 2016 7:03 PM