IReturnend 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 'Connect the Database Server, modify the connection string iconcstr = "provcer = sqloledb.1; integrated security = SSPI; PERSIST security info = false; data source = sql server name" Idb.open iconcstr 'gets the restored data inventory, if not specified, stored to SQL Server Data Directory if sdatabasepath = "" "=" select filename from master..sysfiles "IRE.Open ISQL, IDB, AdopenKeyset, AdlockReadonly isql = IRE (0) Ire.close SDatabasePath = Left (isql, ISTRRREV (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
vbCrLf .MoveNext Wend .Close End With iSql = iSql & IIf (sReplaceExist, ", replace", "") iDb.Execute iSql iReturn = "" GoTo lbExit lbErr: iReturn = ErrorlbExit: fRestoreDatabase_a = iReturnEnd Function-- above procedure is Here is the stored procedure
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_backupdb]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_backupdb] Go
/ * - Universal stored procedure for backup database
Zou Jian 2003.10 - * /
/ * - Call example
- Back up the current database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = '/ dbname / _ / date / _db.bak'
- Difference backup Current Database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _df.bak', @ bktype = 'DF'
- Back up the current database log EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _log.bak', @ bktype = 'log' - * / create proc p_backupdb @ dbname sysname = ', - To back up the database name, not specified, back up the current database @BKPATH NVARCHAR (260) = '', - Backup file storage directory, not specified, use SQL default backup directory @BKFNAME NVARCHAR (260) = '' , - Backup file name, file name can be used in the file name, / DBNAME / representative database name, / date / representative date, / time / representative time @BKTYPE NVARCHAR (10) = 'DB', - Backup Type: 'DB' Backup Database, 'DF' Difference Backup, 'Log' Log Backup @Appendfile Bit = 1, - Adding / Overlay Backup File @password nvarchar (20) = '- Password set for the backup file (only SQL2000 support only), set After the recovery, this password must be provided as Declare @SQL VARCHAR (8000) if isnull (@dbname, ') =' 'set @ dbname = db_name () if isnull (@BKPATH,' ') =' 'begin select @ BKPATH = RTRIM (Reverse (filename)) from master..sysfiles where name = 'master' selection @ bkpath = Substring (@ bkpath, charindex ('/', @ bkpath) 1,4000), @ bkpath = reverse (Substring (@ Bkpath, Charindex ('/', @ bkpath), 4000) 'Backup /' end if isnull (@BKFNAME, ') =' 'set @BKFNAME =' / DBNAME / _ / date / _ / time /.Bak 'set @ bkfname = replace (Replace (@BKFNAME,' / DBNAME / ', @ dbname),' / Date / ', Convert (varchar, getdate (), 112)),' / TIME / ' , REPLACE (Convert) Ate (), 108), ':', ')) SET @ SQL =' backup ' case @Bktype when' log 'Then' log 'Else' Database 'end @ dbname ' to disk = '' ' @ BKPATH @ bkfname '' 'with' case @Bktype when 'DF' TEN 'DIFFERENTIAL,' ELSE '
END CASE @Appendfile When 1 Then 'Noinit' Else 'INIT' END CASE ISNULL (@password, '') when '' Ten '' Else ', Password =' '' End EXEC ( @sql) goif exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_RestoreDb]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [p_RestoreDb] Go
/ * - Restore the universal stored procedure for the database
Zou Jian 2003.10 - * /
/ * - Call example - Complete recovery database EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ DBNAME = 'DB'
- Difference backup recovery EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db', @ retype = 'dbnor'Exec p_restoreb @ bkfile =' c: /db_20031015_df.bak ', @ dbname =' DB ', @ rettype =' DF '
- Log Backup Restore EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ DBNAME = 'db', @ retype = 'dbnor'exec p_restoreb @ bkfile =' c: /db_20031015_log.bak ', @ dbname =' DB ', @ rettype =' log '
- * /
CREATE P_RESTOREDB @ bkfile nvarchar (1000), - Define backup file name (with path) @dbname sysname = '', - Define after restoring database name, default for backup file name @dbpath nvarchar (260 ) = ', - Restored data inventory depicted, not specified for SQL default data directory @retype nvarchar (10) =' db ', recovery type:' DB 'full-time recovery database,' dbnor ' Difference recovery, log recovery is complete recovery, 'DF' Difference backup recovery, 'log' log recovery @FileNumber int = 1, - Restore file number @overexist bit = 1, - Whether to override existing databases, only @Rtype is the 'DB' / 'dbnor' is effective @Killuser bit = 1, - Do you turn off the user's usage process, only @overExist = 1 When @password nvarchar (20) = '- The password of the backup file (only only SQL2000 support), if the password is set, this password must be provided as an asDeclare @SQL varchar (8000) - get the restore database name if isnull (@dbname, '') = 'select @ SQL = Reverse (@BKFile ), @ SQL = Case When Charindex ('.', @ SQL) = 0 Then @SQL Else Substring (@ SQL, Charindex ('.', @ SQL) 1, 1000) End, @ SQL = Case When Charindex '/', @ SQL) = 0 Then @SQL Else Left (@ SQL, Charindex ('/', @ SQL) -1) End, @ DBNAME = Reverse (@SQL)
- Get recovered data inventory placed by IF isnull (@dbpath, ') =' 'begin select @ dbpath = RTRIM (Reverse (filename)) from master..sysfiles where name =' master 'select @ dbpath = reverse Substring (@ dbpath, charindex ('/', @ dbpath), 4000) End
- Generate Database Recovery Statement Set @ SQL = 'Restore' Case @RTYPE WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END @ DBNAME 'From Disk =' ' @ bkfile ' '' ' ' with File = ' cast (@filenumber as var) case when @ Overexist = 1 and @retype in (' db ',' dbnor ') Then', Replace 'else' end case @rety gen 'dbnor' Then ' Norecovery 'Else', Recovery 'end case isnull (@password,' ') when' '' '' else ', password =' '' @ Password '' 'end - Handling IF to add mobile logic files @ retype = 'db' or @ rettype = 'dbnor'begin - Get logic filename DECLARE @LFN nVarchar (128) from backup files (128), @ t @ @ @ s varchar (1000)
- Create a temporary table, save the acquired information Create Table #TB (ln nvarchar (128), PN NVARCHAR (260), TP Char (1), FGN NVARCHAR (128), SZ Numeric (20, 0), MSZ NUMERIC 20, 0)) - Get information from the backup file set @ s = 'restore filelistonly from disk =' '' @ bkfile '' ' case isnull (@password,' ') when' '1 '' Else 'with password =' '' @ Password '' 'end insert @tb exec (@s) declare #f cursor for select ln, tp from # Open #f fetch next from #f Into @ lfn, @ TP set @ i = 0 while @@ fetch_status = 0 begin select @ SQL = @ SQL ', Move' '' ' @ @ dbpath @ dbname cast (@i as varchar) Case @TP when 'd' Ten '.mdf' '' Else '.ldf' 'end, @ i = @ i 1 fetch next from #f Into @ lfn, @ tp end close #f deallocate # fend - close a user process treatment if @ overexist = 1 and @ killuser = 1begin declare hCForEach cursor for select s = 'kill' cast (spid as varchar) from master..sysprocesses where dbid = db_id (@dbname) exec sp_msforeach_worker '?' end
- Restore Database EXEC (@SQL)
Go
/ * ---------------- Backup and recovery example --------------------- * /
/ * - EXEC P_BACKUPDB @ DBNAME = 'OA', @ bkpath = 'g: / db_backup /', @BKFNAME = '/ DBNAME / _ / date / _db.bak', @ bktype = 'db' - * /
/ * - EXEC P_RESTOREDB @ bkfile = 'g: /db_backup/oa_20040713_db.bak', @ DBNAME = 'SBDB', @ RetyPE = 'DB' - * /
