All geek questions in one place

Backing up a remote SQL Server database to a local

I am trying to back up a live database to my computer and I cannot find an option for it. I connect to it using Microsoft SQL Server Management Studio 2008 R2. I am a monkey of MySQL, so I'm used to backing up .sql files and moving them.

Does anyone know how to back up a database? I found a backup option that only supports server to server or export, which seems to only allow one table or SQL query code, which I'm not too sure, instead of inserting something like SHOW TABLES;

Does anyone have any ideas? I have limited read-only access for various reasons, nothing wrong, I promise!

+6
source share
3 answers

You can back up the database only in the place that the SQL service account has access to. If you have access to a central resource on a server / network that you can access, and the service can, you can make a backup at this place and then go from your computer to pull it out.

If you just want the database structure, you can script to load the database into a file. This will allow you to save it locally. If you also need data, although a full backup is the fastest way I know of.

EDIT

I would use the T-SQL BACKUP command and WITH COPY_ONLY to back up the database, since you have stated that it is a "live" database. If the scheduled task backs up the database and you switch to another, you will create a database backup restore chain. Using COPY_ONLY will allow you to get a backup copy of the database without requiring it if you need to restore it.

+2
source

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

+2
source

You can also create sql dumps using Management Studio.

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

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

+1
source

Source: https://habr.com/ru/post/898838/

More articles:


All Articles