Example Description:
Environment: Win2K SQLServer 2K Query Analyzer
SQLServer Service instance name: mainserver
Requires backed up database name: MSDB
Local Machine Name (Client End): DAVID
Local users: ZF password: 123
Domain name: Domain
Locally provide folders for backup requirements: E: / TEST
Step 1: Establish a shared folder in program code (or CMD window) NET Share test = E: / TEST or brief description with NetShareAdd this API: NET Share: It is the network command inside Windows. Role: Establish a local shared resource to display the shared resource information of the current computer. Syntax: See NET Share /? Step 2: Establish a Shared Credit Relationship Master "NET Use // David / Test 123 / User: Domain / ZF 'Brief Description: 1: XP_cmdshell: is the extended stored procedure for SQL Server. Role, execute a given command string in the manner of the operating system command line interpreter, and returns any output in a text line mode. Syntax: See SQL Server Online Help 2: Net Use: It is the network command inside Windows. Role, connect your computer with a shared resource or disconnect, or display information about your computer connection. This command also controls a persistent network connection. Syntax: See NET USE /?
Step 3: Backup Database Backup Database MSDB to Disk = '// David / Test / MSDB.BAK' does not need to explain, syntax see SQLServer online help
Step 4: Delete the Shared Folder Call (or CMD Window) NET Share Test / Delete or Use NetSharedl This API Result: Procedure 1376, these pages belong to the database 'msdb' file 'msdbdata' (in the file 1 on 1). Process 1 page, these pages belong to the file 'msdblog' of the database 'msdb' (on the file 1). The Backup Database operation has successfully handled 1377 pages and spent 3.653 seconds (3.086 MB / sec).
In this way, the MSDB on the mainserver server is backed up to the E: /TEST / TSDB.BAK file of the David machine, is it easy to use? The restoration database operation is also the same, as long as the statement of the third step is changed to 'restore database msdb from disk =' // david / test / msdb.bak 'is OK. . Can you try it? ! (The simplest test tool query analyzer CMD window) Note: XP_cmdshell This extension stored procedure can only call the SA level, and it is one of SQL Server's security hazards. Many DBAs like to delete or disable them, so developers Be careful when using it. The example in the article is just a brief explanation of how to use the extended stored procedure to achieve remote backup and recovery, without security and other considerations, I hope the reader is perfected in the code.
