Make Network Path Visible For SQL Server Backup and Restore in SSMS

There are two main approaches to make a network path visible for SQL Server backup and restore operations in SSMS:

1. Using UNC Path:

This is the recommended approach and avoids complications with drive mappings. Simply specify the full Universal Naming Convention (UNC) path to the network location in your backup or restore statements.

Here’s an example:

SQL
BACKUP DATABASE MyDatabase
TO
 DISK
=
 N
'\\Servername\ShareName\MyDatabaseBackup.bak'

2. Mapping Network Drive (with caution):

If you prefer using a drive letter, you can map the network drive. However, keep in mind that the SQL Server service account needs access to the mapped drive. Here’s what to consider:

• Map the drive permanently: Use the net use command with the /PERSISTENT:YES flag to ensure the drive remains mapped even after a reboot.

OR

• Use xp_cmdshell (with caution):

This method involves enabling the xp_cmdshell extended stored procedure, which can be a security risk if not handled carefully. Only use this approach if necessary and follow these steps:

1. Enable xp_cmdshell (with caution):

SQL
EXEC
 sp_configure
'show advanced options'
,
1
;
GO
RECONFIGURE;
GO

EXEC
 sp_configure
'xp_cmdshell'
,
1

GO
RECONFIGURE
GO

2. Map the drive using xp_cmdshell:

SQL
EXEC
 xp_cmdshell
'net use H: \\Servername\ShareName /USER:Domain\Username'

Replace:

• H: with your desired drive letter.
• \\Servername\ShareName with the actual network path.
• Domain\Username with the credentials that have access to the share (if necessary).

3. Verify the mapping:

SQL
EXEC
 xp_cmdshell
'Dir H:'

4. Use the mapped drive letter in your backup or restore statements (e.g., H:\MyDatabaseBackup.bak).
5. Remember to disable xp_cmdshell after use for security reasons:

SQL
EXEC
 sp_configure
'xp_cmdshell'
,
0

GO
RECONFIGURE
GO

Important points:

• Mapping a drive might not work if the SQL Server service account doesn’t have access to the share.
• Using xp_cmdshell carries security risks. Enable it only when necessary and disable it afterward.

Recommendation:

For better security and consistency, it’s generally recommended to use the UNC path directly in your backup and restore statements.