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 / * - Backup Database General Store / * - Call Example - Backup Current Database EXEC P_BACKUPDB @ Bkpath = 'C: /', @ bkfname = '/ dbname /_/Date/_db.bak 'stored procedure Realization Backup and Restore Database: 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 / * - Backup Database Universal Store
/ * - Call example - Backup 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 '- Backup 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 The file storage directory, not specified, use SQL default backup directory @BKFNAME NVARCHAR (260) = ', backup file name, file name can be used in / 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, - Addition / Overlay Backup File @ Password nvarchar (20) = '- The password set for the backup file (support only), after setting, 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..xysfiles where name =' master 'select @ bkpath = Substring (@ @@ @@ BKPATH, Charindex ('/', @ bkpath) 1,4000), @ bkpath = reverse (Substring (@ Bkpath, Charindex ('/', @ BKPATH), 4000) 'Backup /' end if isnull (@ BKFNAME, '') = '' set @BKFNAME = '/ dbname / _ / da TE / _ / TIME / .BAK 'set @ bkfname = Replace (Replace (@BKFNAME,' / DBNAME / ', @ dbname),' / Date / ', Convert (varchar, getdate (), 112), '/ Time /', Replace (varchar, getdate (), 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 TEN 'END CASE ISNULL (@password,' ') when' '' '' else ', password =' '' @ Password '' 'end exec (@sql) Go if 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
/ * - Call example - Complete recovery database EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db' - Difference backup Restore EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ DBNAME = 'DB', @ retype = 'dbnor'Exec p_restoredb @ bkfile =' c: /db_20031015_df.bak ', @ dbname =' db ', @ retype =' DF '- log backup recovery EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db', @ retype = 'dbnor'exec p_restoredb @ bkfile =' c: /db_20031015_log.bak ', @ DBNAME =' DB ', @ RetyPE =' log '- - * / create proc p_restoredb @ bkfile nvarchar (1000), - Define backup file names (with path) @dbname sysname = '', - Define the reputable database name, default for backup file name @dbpath NVARCHAR (260) = '', - Restored data inventory places, not specified, the default data directory for SQL @retype nvarchar (10) = 'DB', - Recovery Type: 'DB' Financial Recovery Database, ' DBNOR 'is resumed, log recovery is complete,' DF 'Difference Backup Recovery,' Log 'Day
Restore @FileNumber INT = 1, - Restore file number @overexist bit = 1, - Whether to override the existing database, only @retype is 'db' / 'dbnor' is effective @Killuser bit = 1, - Whether to close the user usage process, only @parexist = 1 When @password nvarchar (20) = '- The password of the backup file (SQL2000 support only), if the password is set, this password must be provided as a password asclare @SQL varchar 8000) - Received Recovered 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 the restoreable data inventory placement IF isnull (@dbpath, '') = '' begin select @ dbpath = RTRIM (REVERSE FileName)) from master..sfiles where name = 'master' select @ dbpath = reverse (@ dbpath, charindex ('/', @ dbpath), 4000) end - Generate Database Recovery Statement Set @ SQL = ' RESTORE ' CASE @Retype when' log 'Then' log 'Else' Database 'end @ dbname ' from disk = '' ' @ bkfile ' '' ' ' with file = ' cast (@filenumber as varchar) Case when @ Overexist = 1 and @retype in ('db', 'dbnor') Then ', Replace' else '' end case @retyme ' DBNOR 'THEN', NORECOVERY 'ELSE', Recovery 'end case isnull (@password,' ') when' 'TEN' 'ELSE', Password = '' 'End - Add Mobile Logic Processing IF @ rettype = 'db'
OR @ Retype = 'DBNOR'BEGIN - Gets logical file name from the backup file Declare @LFN NVARCHAR (128), @ TP Char (1), @ i int, @ s varchar (1000) - Create a temporary table, save Get information crete 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 '' '' Else 'with password =' ' ' @ Password ' '' end INSERT INTO #TB EXEC (@s) Declare #f Cursor for select ln, tp from # @f fetch next from #f Into @ lfn, @ TP set @ i = 0 while @@ fetch_status = 0 Begin select @ SQL = @ SQL ', Move' '' ' @ lfn ' '' to '' ' @ dbpath @ DBNAME CAST (@i as varchar) Case @tp when' d 'Then '.mdf' 'Else' .ldf '' end, @ i = @ i 1 fetch next from @fn, @ tp end close #f deallocate #fnd - Close User Process 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 - to restore the database exec (@sql ) GO - Backup Backup Database Database To Di SK = 'C: / Your Backup File Name - Restore RESTORE DATABASE Database from Disk =' C: / Your Backup File Name '
