Backup remote SQL Server database to local

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开发者_StackOverflow 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!

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.

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.

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