0

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?

migrated from stackoverflow.com Jul 10 at 6:16

This question came from our site for professional and enthusiast programmers.

  • I just removed a table and imported the updated one. After that, the SSMS didn't connect to database engine. I tried several ways to fix the problem but it didn't work for me. So, I had to reinstall SSMS. After reinstalling I am able to connect to the server but can not see my database. – Fardeen Safdari Jul 10 at 7:03
4

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.

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.