• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 18
  • Last Modified:

Rebuild the whole SQL Server from scratch

Just wanted to understand the process of rebuild the SQL Server in the below situation

windows server 2008 r2 was crashed and rebuilt with scratch and the administrator has given access to me windows admin right.
the currently windows administrator does not have any detailed info about SQL server versions, running, objects, database,etc,  and this was connected to ERP

so I found out in the D: Drive it has all the SQL Server folder, which has backup (system/user database, log,etc)
so I need to rebuild it

My Questions here
1. Which Version i need to install the SQL
2. After installation of SQL server
shall i restore the system databases (Master, model,msdb) one by one.. after that shall i move to restore the user database?

After restore the above system database, all my user logins, permission, roles,users,  server objects, configuration mg, Ram being used, Sql Configuration mgt port used, etc.. will it be back my original state?

Thanks..
0
bsarahim
Asked:
bsarahim
  • 3
  • 3
  • 2
4 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you if there's a C:\Program Files\Microsoft SQL Server folder? If so, post here the subfolders from that folder, as it will show the SQL Server version.
If there's nothing on C: drive, search in D: drive for the sqlservr.exe file. Right-click on it and go to Properties and check for the version of the file.
0
 
bsarahimAuthor Commented:
great.. i can able to find that the version of file.. what next?
0
 
Mark WillsTopic AdvisorCommented:
Detailed instructions at : https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql

Make sure you get the right version (including patches) before you attempt to restore - sql will complain (error) otherwise

Also worth reading : https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases

Apologies for the links, but, they have all the information you need - including some troubleshooting ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bsarahimAuthor Commented:
thanks definitely will install all missing patches..

but after new install of system databases I will restore the system db wiith replace/overwritte option and after that I will restore  user database

with this above approach all will be normal?? right all users login, permissions, objects, index's, backup schedules, connections, port will work right????
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, now that you found the SQL Server files, are you sure that SQL Server isn't installed? Perhaps it's installed and just not started? You can check in the Services for the SQL Server service. If exists then try to start it.

Rebuilding a SQL Server instances is mostly a reinstall, so you'll need to configure the instance, attach databases, create the proper logins and any other server objects (Linked Servers for example) and if you had SQL Agent jobs before then you'll need to recreate them also. Oh, I'm not even mentioning things like Replication, Log Shipping or Mirroring as they aren't usual in all SQL Server instances.

Can you find the system databases (master, msdb, model, tempdb)?
Which more databases can you see? Check for these type of files: *.mdf, *.ndf, *.ldf.
0
 
Mark WillsTopic AdvisorCommented:
>> with this above approach all will be normal??

The two most important will be MASTER and MSDB, but should be backing up all system DB's after every change. If you have been doing that, and restoring all system DB's, then yes, it should be back to 'normal', with a couple of caveats...

Go through, check linked servers, check connections, make sure you have the right versions of service providers installed (odbc ado native client, office interfaces, mail interfaces, etc - whatever you use). It is those 'add-ons' and front end apps that may require a separate install or setup / configuration that are often overlooked.

Having said that, you will find a few 'gotchas' here and there. and will need to monitor very closely and be in a position to respond quickly to any hiccups. Expect some.

We have all done a few tweaks here and there without necessarily taking the backups or recording them with a view of having to recover from a catastrophic fail. Until of course, you have gone through such an event, and boy, does that sharpen the processes. That's why a lot of the very good DBA's seem so rigid/ inflexible/ a-retentive  with their DB environment.
0
 
bsarahimAuthor Commented:
Thanks a lot for guidance..one quick check

after fresh installation, why cant i just attach the both databases (system & users) instead of restore.. Pls suggest
0
 
Mark WillsTopic AdvisorCommented:
Because with system databases, they will exist as a result of the install.

So, you have use the "WITH REPLACE" option.   RESTORE DATABASE master FROM <backup_device> WITH REPLACE

User databases, you probably can attach, but that means it probably wasnt a catastrophic failure per se, and in any case, you want to make sure there is referential integrity between all the components, so restore  is the best option. If you do attach the user databases, you will have to go through DBCC repair anyway and fix errors. So, again, better to bite the bullet and restore.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now