Programming SQL Server backup and Restoration

Note that the following Backup and restoration operations are implemented using 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
 
/* -- General stored procedures for backing up databases
 
-- Producer build 2003.10 --*/
 
/* -- Call example
 
-- Back up the current database
Exec p_backupdb @ bkpath = 'C: \ ', @ bkfname =' \ dbname \ _ \ date \ _ dB. Bak'
To back up and restore a database in a 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
 
/* -- General stored procedures for backing up databases
 
-- Producer build 2003.10 --*/
 
/* -- Call example
 
-- Back up the current database
Exec p_backupdb @ bkpath = 'C: \ ', @ bkfname =' \ dbname \ _ \ date \ _ dB. Bak'
 
-- Differential backup of the current database
Exec p_backupdb @ bkpath = 'C: \ ', @ bkfname = 'db _ \ date \ _ DF. Bak', @ bktype = 'df'
 
-- Backup the current database log
Exec p_backupdb @ bkpath = 'C: \ ', @ bkfname = 'db _ \ date \ _ log. Bak', @ bktype = 'log'
--*/
Create proc p_backupdb
@ Dbname sysname = '', -- Name of the database to be backed up. If this parameter is not specified, the current database is backed up.
@ Bkpath nvarchar (260) = '', -- specifies the directory where backup files are stored. If this parameter is not specified, the default SQL Backup Directory is used.
@ Bkfname nvarchar (260) = '', -- backup file name. \ dbname \ can be used in the file name to represent the database name, \ date \ represents the date, and \ time \ represents the time
@ Bktype nvarchar (10) = 'db', -- Backup Type: 'db' backup database, 'df 'differential backup, and 'log' log backup
@ Appendfile bit = 1, -- append/overwrite the backup file
@ Password nvarchar (20) = ''-- the password set for the backup file (only supported by SQL2000). This password must be provided when the backup file is restored.
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'
Select @ 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 (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 'then' differential, 'else' end
+ Case @ appendfile when 1 then 'noinit 'else' init 'end
+ Case isnull (@ password, '') when''' then''' 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 General stored procedure of the database
 
-- Producer build 2003.10 --*/
 
/* -- Call example
-- Complete database recovery
Exec p_restoredb @ bkfile = 'C: \ db_20031015_db.bak ', @ dbname = 'db'
 
-- Differential Backup Recovery
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), -- defines the backup file name to be restored (with Path)
@ Dbname sysname = '', -- defines the name of the recovered database. The default value is the backup file name.
@ Dbpath nvarchar (260) = '', -- the restored database storage directory. If this parameter is not specified, it is the default SQL data directory.
@ Retype nvarchar (10) = 'db', -- Recovery Type: 'db' restores the database after completion. 'dbnor 'indicates differential recovery, and logs are completely restored, 'df' differential Backup Recovery and 'log' Log Recovery
@ Filenumber Int = 1, -- restored file number
@ Overexist bit = 1, -- whether to overwrite existing databases. It is valid only when @ retype is 'db'/'dbnor '.
@ Killuser bit = 1, -- whether to disable the user process, only valid when @ overexist = 1
@ Password nvarchar (20) = ''-- password of the backup file (only supported by SQL2000). This password is required if the password is set during Backup.
As
Declare @ SQL varchar (8000)
 
-- Get the 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)
 
-- The recovered database storage directory is obtained.
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 statements
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 @ retype when 'dbnor 'then', norecovery 'else', recovery 'End
+ Case isnull (@ password, '') when''' then''' else', password = ''' + @ password + ''' end
 
-- Processing of adding a Mobile logical File
If @ retype = 'db' or @ retype = 'dbnor'
Begin
-- Obtain the logical file name from the backup file
Declare @ LFN nvarchar (128), @ TP char (1), @ I int, @ s varchar (1000)
 
-- Create a temporary table and save the obtained information
Create Table # Tb (LN nvarchar (128), PN nvarchar (260), TP char (1), FGN nvarchar (128), SZ numeric (20, 0 ), msz numeric (20, 0 ))
-- Obtain information from the backup file
Set @ s = 'Restore filelistonly from disk = ''' + @ bkfile + ''''
+ Case isnull (@ password, '') When ''then'' 'else' with Password = ''' + @ password + ''' end
Insert into # TB exec (@ s)
Declare # F cursor for select ln, TP from # TB
Open # 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 # F into @ LFN, @ TP
End
Close # F
Deallocate # F
End
 
-- Disable User process Processing
If @ overexist = 1 and @ killuser = 1
Begin
Declare hcforeach cursor
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
Gong Jian said:
To put it bluntly, it is the application of backing up the database and restoring the SQL statement of the database:
 
-- Backup
Backup database to disk = 'C: \ your Backup Filename'
 
-- Restore
Restore database from disk = 'C: \ your Backup Filename'