Backup MSSQL database via disk (no SSMS no command line)

Question

Our Windows server is having issues but before running disk repair, I want to take a backup. At the moment I'm using SystemRescure (the Linux utility) and I have successfully saved files.

The only problem I have is that I need to take a backup of MSSQL databases. Now for obvious reasons I can't use SSMS software nor command line to get it because MSSQL is not running and it can't be started since I'm on Linux just browsing the hard disk.

What options do I have? I was thinking of saving all database files (tables, indexes ecc.) but:

• I have no idea where MSSQL stores them
• I suspect that I can't do anything with such files. I mean probably I can't magically import them on a new server

Answer 1

By default MSSQL stores the database files in the following location:

C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\DATA\

Where XX depends on the Microsoft SQL Version. However its possible that this is changed in the past!

You should be able to copy database files and attach them to another Microsoft SQL Instance which has the same version or higher.

Per database you most likely have the following files:

• <db_name>.mdb: The primary data file
• <db_name>.ldf: Log File
• <db_name>.ndf: Secondary Data File (Not necessarily every database has a secondary data file)

Make sure you recover all of these files.

After recovery you can attach the databases on another Microsoft SQL server with the following query (or via the SSMS GUI)

USE [master]
GO
create database <DatabaseName> ON
(name='LogicalName of the Data file', FileName='Data File Name'),
(name='LogicalName of the Log file', FileName='Log File Name')
FOR ATTACH;