T-SQL recovery, backup database)

This method is also good, and I use this method to restore the database backup.
Note that the following stored procedures are searched online. Thank you for the code provider. Reprinted here

Using T-SQL statement to realize the function of database backup and Restoration

It embodies four knowledge points in SQL Server:

1. Obtain the default directory on the SQL Server

2. Use of backup SQL statements

3. Restore the use of SQL statements, and disable the processing of other user processes during forced recovery.

4. Use of SQL statements for job creation

/* 1. -- get the database file directory

@ Dbname specifies the name of the database whose directory is to be obtained
If the specified data does not exist, return the default data directory set during SQL installation.
If null is specified, the default SQL Backup directory name is returned.
*/

/* -- Call example
Select database file directory = DBO. f_getdbpath ('tempdb ')
, [Default SQL Server Data Directory] = DBO. f_getdbpath ('')
, [Default SQL Server Backup Directory] = DBO. f_getdbpath (null)
--*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_getdbpath] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_getdbpath]
Go

Create Function f_getdbpath (@ dbname sysname)
Returns nvarchar (260)
As
Begin
Declare @ Re nvarchar (260)
If @ dbname is null or db_id (@ dbname) is null
Select @ Re = rtrim (reverse (filename) from Master .. sysdatabases where name = 'master'
Else
Select @ Re = rtrim (reverse (filename) from Master .. sysdatabases where name = @ dbname

If @ dbname is null
Set @ Re = reverse (substring (@ Re, charindex ('\', @ Re) + 5,260) + 'backup'
Else
Set @ Re = reverse (substring (@ Re, charindex ('\', @ Re), 260 ))
Return (@ Re)
End
Go

/* 2. -- back up the database

*/

/* -- Call example

-- Back up the current database
Exec p_backupdb @ bkpath = 'C: \ ', @ bkfname = 'db _ \ 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'

--*/

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

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
As
Declare @ SQL varchar (8000)
If isnull (@ dbname, '') ='' set @ dbname = db_name ()
If isnull (@ bkpath, '') ='' set @ bkpath = DBO. f_getdbpath (null)
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
Print @ SQL
Exec (@ SQL)
Go

/* 3. -- restore the database

*/

/* -- 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_backupdb @ 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_backupdb @ bkfile = 'C: \ db_20031015_log.bak ', @ dbname = 'db', @ retype = 'log'

--*/

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

Create proc p_restoredb
@ Bkfile nvarchar (1000), -- defines the backup file name to be restored
@ 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. Only @ retype is
@ Killuser bit = 1 -- whether to disable the user process. This parameter is only valid when @ overexist = 1.
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, '') ='' set @ dbpath = DBO. f_getdbpath ('')

-- 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
Print @ SQL
-- 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

-- 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
Insert into # TB exec ('Restore filelistonly from disk = ''' + @ bkfile + '''')
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 @ spid varchar (20)
Declare # spid cursor
Select spid = cast (spid as varchar (20) from Master .. sysprocesses where dbid = db_id (@ dbname)
Open # spid
Fetch next from # spid into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill '+ @ spid)
Fetch next from # spid into @ spid
End
Close # spid
Deallocate # spid
End

-- Restore database
Exec (@ SQL)

Go

/* 4. -- create a job

*/

/* -- Call example

-- Jobs executed every month
Exec p_createjob @ jobname = 'mm', @ SQL = 'select * From syscolumns ', @ freqtype = 'month'

-- Weekly jobs
Exec p_createjob @ jobname = 'ww ', @ SQL = 'select * From syscolumns', @ freqtype = 'Week'

-- Daily jobs
Exec p_createjob @ jobname = 'A', @ SQL = 'select * From syscolumns'

-- Jobs executed daily and repeated every four hours every day
Exec p_createjob @ jobname = 'B', @ SQL = 'select * From syscolumns ', @ fsinterval = 4

--*/
If
Exists (select * From DBO. sysobjects where id =
Object_id (n' [DBO]. [p_createjob] ') and objectproperty (ID,
N 'isprocedure ') = 1)
Drop procedure [DBO]. [p_createjob]
Go

Create proc p_createjob
@ Jobname varchar (100), -- job name
@ SQL varchar (8000), -- command to be executed
@ Dbname sysname = '', -- the default value is the current database name.
@ Freqtype varchar (6) = 'day', -- time period, month, week, day
@ Fsinterval Int = 1, -- the number of repetitions relative to the day
@ Time Int = 170000 -- execution start time. For repeated jobs, the time ranges from 0.
As
If isnull (@ dbname, '') ='' set @ dbname = db_name ()

-- Create a job
Exec MSDB .. sp_add_job @ job_name = @ jobname

-- Create a job
Exec MSDB .. sp_add_jobstep @ job_name = @ jobname,
@ Step_name = 'data ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval

-- Create Scheduling
Declare @ FTYPE int, @ fstype int, @ ffactor int
Select @ FTYPE = case @ freqtype when 'day' then 4
When 'Week' then 8
When 'month' then 16 end
, @ Fstype = case @ fsinterval when 1 then 0 else 8 end
If @ fsinterval <> 1 Set @ time = 0
Set @ ffactor = case @ freqtype when 'day' then 0 else 1 end

Exec MSDB .. sp_add_jobschedule @ job_name = @ jobname,
@ Name = 'schedule ',
@ Freq_type = @ FTYPE, -- every day, 8 weeks, 16 months
@ Freq_interval = 1, -- number of repeated executions
@ Freq_subday_type = @ fstype, -- whether to execute repeatedly
@ Freq_subday_interval = @ fsinterval, -- recurrence
@ Freq_recurrence_factor = @ ffactor,
@ Active_start_time = @ time -- execute at 17:00:00 pm

Go

/* -- Application case -- backup solution:
Full backup (once a week) + differential backup (once a day) + Log backup (once every 2 hours)

Call the above stored procedure for implementation
--*/

Declare @ SQL varchar (8000)
-- Full backup (once every week)
Set @ SQL = 'exec p_backupdb @ dbname = ''name of the database to be backed up '''
Exec p_createjob @ jobname = 'Weekly backup ', @ SQL, @ freqtype = 'Week'

-- Differential backup (backup once every day)
Set @ SQL = 'exec p_backupdb @ dbname = ''name of the database to be backed up'', @ bktype = 'df''
Exec p_createjob @ jobname = 'differential backups per Day', @ SQL, @ freqtype = 'day'

-- Log backup (every 2 hours)
Set @ SQL = 'exec p_backupdb @ dbname = ''name of the database to be backed up'', @ bktype = 'log''
Exec p_createjob @ jobname = 'every 2 hours log backup ', @ SQL, @ freqtype = 'day', @ fsinterval = 2

/* -- Application Case 2

Production data core database: produce

The backup solution is as follows:
1. Set up three jobs to perform daily backup, weekly backup, and monthly backup for the produce database respectively.
2. Create three new databases named daily backup, weekly backup, and monthly backup respectively.
3. Create three jobs and restore the three backup databases to the above three new databases.

Purpose: When any data loss occurs in the produce database, you can import the corresponding table data from the above three backup databases.
--*/

Declare @ SQL varchar (8000)

-- 1. Create a monthly Backup Task and generate a monthly backup database. The task is performed at every month:
Set @ SQL ='
Declare @ path nvarchar (260), @ fname nvarchar (100)
Set @ fname = ''produce _ ''+ convert (varchar (10), getdate (), 112) +'' _ M. Bak''
Set @ Path = DBO. f_getdbpath (null) + @ fname

-- Backup
Exec p_backupdb @ dbname = ''produce'', @ bkfname = @ fname

-- Generate a new monthly database based on the backup
Exec p_restoredb @ bkfile = @ path, @ dbname = ''produce _ month''

-- Prepare the basic database for weekly database recovery
Exec p_restoredb @ bkfile = @ path, @ dbname = ''produce _ '', @ retype = ''dbnor''

-- Prepare basic database for daily database recovery
Exec p_restoredb @ bkfile = @ path, @ dbname = ''produce _ day'', @ retype = ''dbnor''
'
Exec p_createjob @ jobname = 'monthly backup ', @ SQL, @ freqtype = 'month', @ time = 164000

-- 2. Create a weekly differential Backup Task and create a weekly Database Backup Task, which is performed at every Sunday afternoon:
Set @ SQL ='
Declare @ path nvarchar (260), @ fname nvarchar (100)
Set @ fname = ''produce _ ''+ convert (varchar (10), getdate (), 112) +'' _ w. Bak''
Set @ Path = DBO. f_getdbpath (null) + @ fname

-- Differential backup
Exec p_backupdb @ dbname = ''produce'', @ bkfname = @ fname, @ bktype = ''df''

-- Weekly differential recovery database
Exec p_backupdb @ bkfile = @ path, @ dbname = ''produce _ '', @ retype = ''df''
'
Exec p_createjob @ jobname = 'Weekly differential backup ', @ SQL, @ freqtype = 'Week', @ time = 170000

-- 3. Create a daily log backup job and create a daily Database Backup job, which is performed at every Sunday afternoon:
Set @ SQL ='
Declare @ path nvarchar (260), @ fname nvarchar (100)
Set @ fname = ''produce _ ''+ convert (varchar (10), getdate (), 112) +'' _ L. Bak''
Set @ Path = DBO. f_getdbpath (null) + @ fname

-- Log backup
Exec p_backupdb @ dbname = ''produce'', @ bkfname = @ fname, @ bktype = ''log''

-- Log Recovery daily Database
Exec p_backupdb @ bkfile = @ path, @ dbname = ''produce _ day'', @ retype = ''log''
'
Exec p_createjob @ jobname = 'Weekly differential backup ', @ SQL, @ freqtype = 'day', @ time = 171500