Sql-server – lost all the databases after reinstalling sql server management studio

sql serversql server 2014ssms

SQL server management studio was not working so, after googling this issue, I found that I have to reinstall it. So, after re installation, I lost my database and I didn't get a backup of it. So, is there any way to recover it back?

Best Answer

Background

Q1

Did you reinstall SSMS or your actual database engine? Can you add a picture with what you are seeing?

OP - A1 enter image description here

Q2

Did you use the same login as before? If you reinstalled your database engine the database files might still be there. Can you check your default Data / Log folders to see if there are any database files there? If yes, you could try Attaching these databases again. What does this return? SELECT IS_SRVROLEMEMBER ('sysadmin')

OP - A2

Yes, I use the same login as I was using before. I pasted "SELECT IS_SRVROLEMEMBER ('sysadmin')" and run it on SQL query window and the result is " no column name (1) nothing else. I don't know where my database was saved to look on it if I can find any thing related to my lost database. Could you please tell us where our databases would be saved by default in C drive?

Q3£

This means that you are a sysadmin on the instance and that you should be able to see the database if it exists. You can try finding the system databases by using select physical_name from sys.master_files where database_id = 1 and find the default data path by executing SELECT SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]

OP - A3

Yes, I found two files: LostDatabaseName.mdf and LostDatabaseName_log.ldf. Now, how can I move them back to SSMS?

Solution

You can try attaching the database to your new instance.

EXEC sp_attach_db @dbname = N'LostDatabaseName',   
    @filename1 =   
N'C:\FoldersHere\LostDatabaseName.mdf',   
    @filename2 =   
N'C:\FoldersHere\LostDatabaseName_log.ldf';

An example from Microsoft docs

EXEC sp_attach_db @dbname = N'AdventureWorks2012',   
    @filename1 =   
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf',   
    @filename2 =   
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf';

Source

I would also advise to not keep all your database files onC:\ drive. If your drive fills up your server will crash and corruption can occur. It is also important to take backups of your databases, and putting these on separate locations.

Related Question