How to copy database from server to local machine in SQL Server Management studio? [closed]

Question

I am pretty much new to SQL Server Management Studio. I connect to SQL Server using Windows authentication. But not sure if my work affect the server database. How can I make sure not to mess with the updates and commits to the data?

Answer 1

You probably want a local development copy of the database so that your queries and schema changes don't affect production data. Please check with your management first to make sure you don't have policies against developers having access to (or their own copies of) production data, which may be sensitive for a variety of reasons depending on your industry.

Assuming you are okay to do so, back up the database from the server, and restore it on your workstation. Make sure to use COPY_ONLY for the backup, to avoid interfering with any existing backup/recovery plans.

BACKUP DATABASE dbname TO DISK = 'E:\somefolder\file.bak'
  WITH INIT, COPY_ONLY;

Copy that file from the server to your machine.

Then restore it on your machine; you will likely need to use WITH MOVE in order to put the files in a valid location:

RESTORE DATABASE dbname FROM DISK = 'C:\temp\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
  MOVE 'dbname_log' TO 'C:\...\dbname.ldf';