Backup remote SQL Server database to local

Question

I'm trying to backup a live database to my computer and I can't find the option to do it. I'm connecting to it using Microsoft SQL Server Management Studio 2008 R2. I'm a MySQL monkey, so I'm used to being able to backup to .sql files and move them around.

Anyone have any idea how I can create a file backup of the database? I've found the backup option which only backs up on the server, or the export, which seems to only allow a single table, or code an SQL query, which I'm not too sure on, short of putting in something like SHOW TABLES;

Anyone have any ideas? I'm limited to readonly access for various reasons, nothing bad, promise!

I don't think you can backup to client. And that would be a huge security break. — Patrick Honorez
– Patrick Honorez, Commented Oct 7, 2011 at 15:02
@iDevlop, I don't think is such a huge security risk; if you can query it you can copy it. The only thing is that you don't automate it. — Javier Mr
– Javier Mr, Commented Jan 28, 2014 at 10:10

score 2 · Accepted Answer · 2011-10-07 15:13:52Z

2

You will only be able to backup the database to a location the service account for SQL has access to. If you have access to a central share on the server/network that you can access and the service can, you might backup to that location and then browse from your computer to pull it down.

If you are just wanting the database structure you could script the database out to a file. This would let you save it locally. If you also want the data though doing a full backup is the quickest way I know of.

EDIT

I would use the T-SQL BACKUP comand and include WITH COPY_ONLY to backup the database, since you stated this is a "live" database. If a scheduled job is performing backups against the database and you break in to do an additional one you will effect the backup recovery chain of the database. Using the COPY_ONLY will allow you to get a backup of the database without requiring it in the event of a recovery need.

edited Oct 7, 2011 at 15:13

answered Oct 7, 2011 at 15:05

user847990

Sign up to request clarification or add additional context in comments.

1 Comment

David Yell Over a year ago

Thanks, I didn't know about the backup. I've taken a copy of the schema and dumped some tables to csv to have same sample data. Then I can recreate it locally and play about.

pfigel · Accepted Answer · 2011-10-07 15:41:28Z

1

You can also create sql dumps with Management Studio.

Right-click the database and select Tasks - Generate Scripts. This will open a wizard that allows you to select what the dump should include (e.g. tables, indices, views, ...).

Make sure you set "Script Data" to true if you want your dump to include inserts.

answered Oct 7, 2011 at 15:41

pfigel

962 bronze badges

Comments

Karel · Accepted Answer · 2012-07-04 23:32:39Z

1

You can enter a valid UNC path in the Backup option.

edited Jul 4, 2012 at 23:32

user142162

answered Oct 7, 2011 at 15:00

Karel

2,2221 gold badge16 silver badges25 bronze badges

Collectives™ on Stack Overflow

Backup remote SQL Server database to local

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related