Backing Up From Remote SQL Server to Local Drive

I have recently been challenged with the following issue:

1.I wanted to have a backup of one of our clients SQL Server database.

2.The only available information was the IP address of the SQL Server instance. The sql login and password. (the login has the system administrator role)

I researched for a while on the web with no success for easy solution. So I decided to work on my own.

The Solution:

1. Execute “net use” command to map a network drive (local computer):

   1: DECLARE @netUse nvarchar(4000)

   2: SET @netUse='net use n: \\10.10.xxx.xxx\backup /USER:domain\username password ' 

   3: --replace 10.10.xxx.xxx  with your local computer ip address

   4: EXEC master.dbo.xp_cmdshell @netUse

   5: --because the login has sys admin role it is ok to use xp_cmdshell

2. Execute the backup (via SQL Management Studio) and copy the file name in your clipboard for the next step.

3. Use the backup file name with the following T-SQL statement:

   1: DECLARE @SQL nvarchar(4000)

   2: DECLARE @backuplocation nvarchar(4000)

   3: SET @backuplocation ='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\backupfilename.bak' 

   4: ---use the location you have chosen for the backup

   5: SET @SQL = 'xcopy "' + @backuplocation + '" n:\'

   6: EXEC master.dbo.xp_cmdshell @SQL

If you have further questions feel free to comment below!

Leave a Reply

Your email address will not be published. Required fields are marked *