7

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?

1
  • 1
    You cannot 'mess' with the updates/inserts by logging into SQl server management studio. Is this your only question or you want o copy data. Your Q and description both are different
    – Shanky
    Sep 10 '14 at 14:25
10

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';
1
  • 1
    good suggestion here about first asking OP to consult whether actually he is allowed to use prod data on local machine.
    – Shanky
    Sep 10 '14 at 14:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.