Backup SQL Server database

views:

answers:

+1 Q:

Backup SQL Server database

Hi.

I can connect to remote SQL Server database and I want to create a local database copy on my system. How can I do it? When i try backup through Management studio I cannot copy backup file on my hard drive only on remote system hard drive. Copy database command also doesn't work because I have express version of SQL Server. And export command doesn't transfer stored procedures, only tables data. Any suggestion?

Does T-SQL BACKUP command can help me? if it does then how to use that command?

I havn't access to remote computer. Only mssql connection.

+2 A:

If your local drive is accessible by the remote server, you can create your backup directly to your drive. Not to be able to create backups on a network share is just a restriction of the GUI. Use the T-SQL BACKUP command and it will work.

Greets Flo

Florian Reischl 2010-07-25 10:31:37

well, does that command backup database on MY hard drive?BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'F:\db.bak';

Neir0 2010-07-25 11:25:31

@Neir0: only if the remote server has access to your local harddisk - which is **highly** unlikely...

marc_s 2010-07-25 13:57:53

@Neir0 Could you describe "remote" more specific, please? Do you mean a remote server in your local area network or anything else?

Florian Reischl 2010-07-25 14:00:03

@Florian Reischl i mean remote server on internet and it hasn't access to my harddrive. So do you know any alternative ways?

Neir0 2010-07-25 14:19:10

@Neir0 so I got you wrong. In this case make a backup (over T-SQL or SSMS). After that, you have to transfer it to a FTP server, over a VPN, or any other protocol.

Florian Reischl 2010-07-25 15:01:08

@Florian Reischl but i doesnt have ftp or any other access to remote computer. Only mssql connection.

Neir0 2010-07-25 15:13:14

Though, if there is a SQL Connection, there must be some protocol beyond. However, without any possibility to transfer the backup there is no very professional solution.

Florian Reischl 2010-07-25 15:27:13

use sqlbackupandftp

Ghost 2010-07-25 10:38:37

-1: MS SQL Server is not MySQL.

Richard 2010-07-25 10:45:55

sorry i misunderstood.

Ghost 2010-07-25 11:36:24

This utility doesnt working for meBackup of dbname failed: Cannot open backup device 'F:\WINDOWS\TEMP\dbname201007251652.bak'. Operating system error 3(Системе не удается найти указанный путь.).BACKUP DATABASE is terminating abnormally.

Neir0 2010-07-25 12:55:35

You can use the Export tool of the SSMS to copy the tables of your database and use the Generate Scripts tool to get scripts that create procedures, views, triggers and functions of the database and run these scripts on your new database.

SubPortal 2010-07-26 06:32:55

ansaurus

tags:

views:

answers:

Backup SQL Server database

related questions