Friday, August 14, 2015

Work Adeventure: Backup database to network

I had a situation where I had to perform a backup of the database on a network. I performed the following

Mapped the  nwMachine\L share to Y drive.
Thus I had (\\nwMachine\L) Y:

If I do a backup at this point the SQL server will not see the mapped drive (Y:) yet .
I had to run few scripts to make SQL server see the mapped Y drive.

EXEC sp_configure 'advanced', 1

RECONFIGURE WITH override

GO

--to enable the xp_cmdshell
--once the job is done make sure this is turned off

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH override

GO


now make sql server see the mapped drive
EXEC xp_cmdshell 'net use Y: \\nwMachine\L
GO



--EXEC xp_cmdshell 'net use <local mapped drive> (space)  <shared path>
 

--Once we get result as "command ran successfully" we are successful.

--Now in the backup wizard the Y drive should appear along with the other local drives.

--now turn off the xp_cmdshell

EXEC sp_configure 'xp_cmdshell',0

RECONFIGURE WITH override

GO