Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

How to backup SQL Server database to remote machine

leave a comment »

Ok, so here is scenario : you need to backup quite big database but there is no enough space on your server. Moreover your sql server is configured to run on “Local system” (no access to network drives) account and as this is production installation you can not just simply switch that to some other account and restart machine. How to solve that ? Here is a way = you need to map network drive in SQL session and use the account who have access to destination network location.

1. Map network drive in SQL user account session

Open “SQL managment studio” and use “exec xp_cmdshell ‘net use z: \\{YourRemoteServer}\{SomePath}\{SomePath} {Password} /user:{Domain\userName}” and hit F5

This should map network path under “Z:” drive in SQL session. Please notice that if you get message “” then you need to enable “xp_cmdshell” command. This can be done easily with below script:

exec sp_configure ‘show advanced options’,1
go
reconfigure
go
exec sp_configure ‘xp_cmdshell’,1
go
reconfigure
go

Once operation of mapping drive complete with success then you will get:

2013-07-10 11-15-36

2. Do backup to mapped drive

Now you just need to use mapped drive:

backup database {YourDataBaseName}
to disk = ‘z:\{database_backupname.bak’
with copy_only

3. Unmap network drive

For the sake of pace on the end unmap the drive you mapped in point 1. In order to do it you need to run command

“exec xp_cmdshell ‘net use z: /delete”

 

Advertisements

Written by Mariusz Gorzoch

10 July 2013 at 11:19

Posted in Bez kategorii

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: