VB implements SQL Server database backuprecovery (original: ZJCXC (Zou Jian))

zhaozj2021-02-16  89

'********************************************************** ***********************

'** Module Name: FBackupDatabase_a

'** Description: Backup database, return to error information, normal recovery, return ""

'** call: FBackupDatabase_a "Backup File Name", "Database Name"

'**Parameter Description:

'** sbackupfilename Restore data inventory placement

'** SDATABASENAME backup data name

'** SisaddBackup is appended to the backup file

'**: Reference Microsoft ActiveX Data Objects 2.x Library

'** created: Zou Jian

'** Japan: December 09, 2003

'********************************************************** ***********************

Public Function FBackupDatabase_A (Byval SBackupFileName $ _

, Byval SDatabaseName $ _

Optional byval sisaddbackup as boolean = false _

) AS STRING

DIM IDB As Adodb.Connection

DIM ICONCSTR $, ISQL $, IRETURN $

ON Error Goto Lberr

'Creating an object

Set idb = new adodb.connection

'Connect the database server, modify the connection string according to your situation

Iconcstr = "provider = SQLOLEDB.1; Integrated Security = SSPI; PERSIST Security Info = False; Data Source = ZJ"

IDB.Open iconcstr

'Generate a database backup statement

ISQL = "Backup Database [" & SDatabaseName & "]" & VBCRLF & _

"To disk = '" & sbackupfilename "& vbcrlf & _

"with description = '" "zj-backup at:" & date & "" "& vbcrlf & _

IIF (SisaddBackup, ",", Init ")

IDB.EXECUTE ISQL

Goto lbexit

lberr:

IReturn = Error

Lbexit:

FBackUpDatabase_a = IReturn

END FUNCTION

'********************************************************** ************************ ** Module name: FRESTOREDATABASE_A '** Description: Restore the database, return to error information, normal recovery, return "" '** call: FRESTOREDATABASE_A "Backup File Name", "Database Name" ** Parameter Description:' ** SDATABASEPATH Restore Data Stock Directory '** SBACKUPNUMBER is from that backup number Restore' ** SREPLACEXIST Specify whether to overwrite Already existing data '**: References Microsoft ActiveX Data Objects 2.x library' ** Creation: Zou Jian '** Japan: December 09, 2003' ********** *********************************************************** ************* Public Function fRestoreDatabase_a (ByVal sBackUpfileName $ _, ByVal sDataBaseName $ _, Optional ByVal sDataBasePath $ = "" _, Optional ByVal sBackupNumber & = 1 _, Optional ByVal sReplaceExist As Boolean = False _) AS STRING DIM IDB AS AdoDb.Connection, IRE AS AdoDb.Recordset Dim Iconcstr $, ISQL $, IRETURN $, II & IN ERROR GOTO LBERR 'Create Object Set Idb = New ADO DB.Connection SET IRE = New AdoDb.Recordset 'Connecting the Database Server, modify the connection string iconcstr = "provike = sqloledb.1; integrated security = sspi; data source = zj" IDB. Open iconcstr 'gets the restored data inventory, if not specified, stored into SQL Server Data Directory if SDatabasePath = "", ISQL = "SELECT FileName from master..sysfiles" IRE.Open ISQL, IDB, AdopenkeySet, AdlockReadOnly isql = IRE (0) Ire.close SDatabasePath = Left (isql, ISTRREV (ISQL, "/")) endiff '

Check if the database exists If sReplaceExist = False Then iSql = "select 1 from master..sysdatabases where name = '" & sDataBaseName & "'" iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly If iRe.EOF = False Then iReturn = " The database already exists! "IRE.Close Goto Lbexit End if Ire.close End if 'Close the user process to prevent other users from being using the database, resulting in data recovery failed ISQL =" SELECT SPID from master..sysprocesses where dbid = db_id (' " & SDatabaseNameName & "')" Ire.Open ISQL, IDB, AdopenkeySet, AdlienkeySet, Adlieadonly While Ire.eof = false isql = "kill" & IRE (0) idb.execute isql IRE.MOVENEXT Were.Close' Get Database Recovery Information ISQL = "restore filelistonly from disk = '" & sBackUpfileName & "'" & vbCrLf & _ "with file =" & sBackupNumber iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly 'to generate a database restore statement iSql = "restore database [" & sDataBaseName & "]" & vbcrlf & _ "from disk = '" & sbackupfilename "" & vbcrlf & _ "with file =" & sbackupnumb ER & VBCRLF with IRE while not .eof Ireturn = IRE ("PhysicalName") II = IRETURN, ".") IReturn = IIF (II = 0, "", MID (IReturn, II)) & "'" isql = ISQL & ", Move '" & IRE ("LogicalName") & _ "' To '" & SDatabasePath & SDatabaseName & Ireturn & SDatabaseName & IReturn

转载请注明原文地址:https://www.9cbs.com/read-12591.html

New Post(0)