opening a database

Question

I am new to SQL.
I successfully installed SQL server on a win10 PRO box at home.
I connected to a SQL server instance on my home machine.
I can create a database and work on it as described here:
https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver15

However, I want to investigate an already created database from another machine at my office.
I want to be able to create custom reports from that data.
This other database is from an application that creates and manages a schedule.
It runs on a winPro box as well.

I have copied the DATA folder from that other machine running SQL server to the desktop of my home machine.

How do I open it ?

I tried a real noob thing--
Turned off sql server (in services) , replaced the DATA folder in here:
C:\Program Files\Microsoft SQL Server\MSSQL15.LYTEC_SQL\MSSQL\DATA
with the DATA folder I copied to the desktop.
Restarting sql server gives me an error.

Clearly I dont know what I am doing.
Is there a link somewhere that will explain to me what to do so I can start this other database to explore it (names of the fields,etc) and write reports?

Answer 1

The correct action is to run a command like this on the source machine:

BACKUP DATABASE db TO DISK = 'C:\temp\db.bak' WITH INIT, COMPRESSION, COPY_ONLY

The actual path is your own choice of course. "db" here is a placeholder for the actual name of the database name.

Then you copy the .bak file to your machine where first run:

RESTORE FILELISTONLY FROM DISK = 'C:\temp\db.bak'

You pay attention to the names in the first column, LogicalName. Presumably, they are db and db_log, but you cannot take this for granted.

Then you run this command:

RESTORE DATABASE db FROM DISK = 'C:\temp\db.bak'
WITH MOVE 'db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.LYTEC_SQL\MSSQL\DATA\db.mdf',
      MOVE 'db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.LYTEC_SQL\MSSQL\DATA\db.ldf'

Since you already have the database files on your computer,, there is an alternate way by attaching these files to the server. However, BACKUP/RESTORE is the civilised way to copy a database, and I don't want to encouraging copying of database files, which is error prone and can lead to accidents - not only on the target server but also on the source instance.

Answer 2

1. Fix your local install, which may require uninstalling and reinstalling SQL Server. Overwriting the complete contents of your DATA folder was a mistake.
2. Use Backup and Restore to create a local copy of the database.

Answer 3

anonymous usere-msft thank you for taking the time to respond. I may be stupid, but the one redeeming quality of being stupid is learning from your mistakes. I stopped SQL server from services.app, returned the original DATA folder there (which I had saved) and then restarted SQL server. IT restarted fine. Now I have this other DATA folder from my work machine sitting on my desktop. IT has a bunch of files in it like
19,136,512 Lytec SharedData.dat
2,883,584 Lytec SharedData_log.ldf
19,398,656 Lytec Tutorial.dat
1,048,576 Lytec Tutorial_log.LDF
4,194,304 master.mdf
786,432 mastlog.ldf
1,310,720 model.mdf
524,288 modellog.ldf
14,024,704 model_msdbdata.mdf
524,288 model_msdblog.ldf
524,288 model_replicatedmaster.ldf
4,653,056 model_replicatedmaster.mdf
15,466,496 MSDBData.mdf
5,308,416 MSDBLog.ldf
517 MS_AgentSigningCertificate.cer
1,253,572,608 me.dat
786,432 me_log.LDF
2,097,152 tempdb.mdf
524,288 templog.ldf

I tried to restore according to this:
https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver15
I right click on 'Databases' in object explorer in MS SQL Server Management Studio and select Restore.
The 'Restore Database' window opens.
I select the 'Device' radio button from the general page.
I hit the button labeled '...' and browse for the DATA folder on my desktop with the 'Add' button.
The 'locate backup file' window appears.

Guess what...I am not restoring from a backup. Rather I have the real, actual DATA folder from the other machine that is running SQL server (Of course, I had to temporarily turn off SQL server in services to copy it because SQL server is a service that is always running so that folder is always marked in use.)

This is not a backup so I cannot restore from it. How can I replace the DATA folder from my virgin SQL server install with this DATA folder on my desktop?

Answer 4

Thank you. I did a backup of the original database, copied the backup to a USB stick, and copied that 4 gig file to the new machine. Initially I could not find it because there was no 'bak' extension. Added that then restored the database to the home machine. It worked fine. You may close this question.