Backup MSSQL Sever Database to a mapped drive
Backup MSSQL Sever Database to a mapped drive
Issue here is that a network share that is mapped using a local drive letter will not be visible to a SQL Server instance as it is running as a service.The SQL Server service runs in the environment of the local console with the security of the startup account of SQL Server applied.
Mapped drives are specific to a session and not visible to a service started on the local console.In order to use mapped drive for MSSQL Server database backup we need to follow below steps:
1)Run the following command from a query window EXEC xp_cmdshell 'net use <drivename> <share name>'
Where: <drive name>: Letter used to map the drive<share name>: UNC path to the share
xp_cmdshell : T-SQL used for initiating a Windows command Shell and Passes in strings for execution.
2) If in your case xp_cmdshell is not enabled ,follow below query to enable it:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
sp_configure : T-SQL used to display and configure setting of current MSSQL Server instance.
3) There could be some cases where you might need user login in order to map drive. In that case, Open up command prompt window and use the following command:
net use <drivename> <share name> /user:username password
Example: net use y: \\servername\folder /user:username password
Example: net use y: \\servername\folder /user:username password
Reference :
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql?view=sql-server-ver15
Learn MSSQL in depth Knowledge from Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance
=================================================================================
You can also , Visit our Blogs for related topic on Backup & Restore:
Why go to all that trouble? Why not just...
ReplyDeleteBACKUP DATABASE dbname TO DISK =
N'\\servername\sharedfolder\db.BAK
Agree , We can use the above command but backup to UNC path is not supported. In order to make sure backup can be restored , We should use checksum with backup command.
DeletePost which we made was specific to a situation where we want to backup MSSQL Database on a Network share with mapped Drive.
Regards
All Database Solutions
Website: https://all-database-soultions.blogspot.com/
Twitter : https://twitter.com/database_all
LinkedIn : https://www.linkedin.com/in/all-database-solutions-633091225/