How to remotely back up the database to the local in SQL Server
This article will explain in detail how to remotely back up the database to the local in SQL Server, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.
1. Enable xp_cmdshell first
USEmaster
EXECsp_configure'showadvancedoptions',1
RECONFIGUREWITHOVERRIDE
EXECsp_configure'xp_cmdshell',1
RECONFIGUREWITHOVERRIDE
EXECsp_configure'showadvancedoptions',0
RECONFIGUREWITHOVERRIDE
2. Secondly, use variables for backup.
Declare@dbsysname
Declare@dbpathvarchar (120)
Set@db='shenyin'
-- the file name is based on the database name + date YYYYMMDD+ extension
Set@dbpath='D:\'+ @ db+convert (varchar (10), getdate (), 112) + '.bak'
3. Then create a mapping in sql
Execmaster..xp_cmdshell'netuseF:\ 192.168.4.109\ DBBackup "nulili"
/ user:192.168.0.156\ Administrator'/*/*
Explanatory notes:
F: the drive letter that maps the network path to the local machine
192.168.4.109 is my native IP.
DBBackup is a folder shared by me on this computer
Administrator is the user name for the login of the system account
"nulili" is the password of the Administrator user
4. Backup command (@ db is the name of the database to be backed up)
BackupDatabase@dbTodisk=@dbpath
5 、 COPY
Declare@copypathvarchar (200)
Set@copypath='copy'+@dbpath+'F:'
Select@copypath
Execmaster..xp_cmdshell@copypath
6. Delete
Declare@deletepathvarchar (200)
Set@deletepath='del'+@dbpath
Select@deletepath
Execmaster..xp_cmdshell@deletepath
7. Delete the mapping after completion
Execmaster..xp_cmdshell'netuseF:/delete'
8. Close xp_cmdshell
USEmaster
EXECsp_configure'showadvancedoptions',1
RECONFIGUREWITHOVERRIDE
EXECsp_configure'xp_cmdshell',0
RECONFIGUREWITHOVERRIDE
EXECsp_configure'showadvancedoptions',0
RECONFIGUREWITHOVERRIDE
SQL Server on how to remote backup database to the local share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
