Bart's Weblog

Just a blog…

Microsoft SQL Server: Backup database to a mapped drive

Posted by bartvdw on 0505/1010/2012

This is very useful in some cases: backup a Microsoft SQL Server database to a mapped drive… Resources at the bottom with more details.

You can do this by mapping the drive from within SQL, open a new query and use the commands below (examples):

> Map y: drive: EXEC xp_cmdshell ‘net use y: \\server\share /user:usr passw’
> Remove y: drive: EXEC xp_cmdshell ‘net use y: /d’

Interesting to know is that ‘xp_cmdshell’ is disabled by default (for sure SQL Server 2005, didn’t check for other versions). You can enable (or disable) using following query:

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE

(to disable, change value xp_cmdshell to 0 ofcourse)

How to backup SQL Server databases to a mapped drive
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/02/27/how-to-backup-sql-server-databases-to-a-mapped-drive.aspx

Enabling xp_cmdshell in SQL Server 2005
http://weblogs.sqlteam.com/tarad/archive/2006/09/14/12103.aspx

Advertisements

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: