Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.