To back up SQL with a command

The function of database backup and restore using T-SQL statement

Represents four points of knowledge in SQL Server:

1. Get the default directory on a SQL Server server

2. Use of Backup SQL statements

3. Restores the use of SQL statements, taking into account the process of shutting down other user processes when forcing recovery

4. Use of the job creation SQL statement

/*1.--get the file directory of the database

@dbname Specifies the name of the database to get the directory
Returns the default data directory set when SQL is installed if the specified data does not exist
If NULL is specified, the default SQL backup directory name is returned
*/

/*--Invocation 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 [email protected]

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.--backing up the database

*/

/*--Invocation Example

--Back up the current database
exec p_backupdb @bkpath = ' c:/', @bkfname = ' Db_/date/_db.bak '

--Differential 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 '

--*/

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= ',--the name of the database to be backed up, not specified to back up the current database
@bkpath nvarchar (260) = ",--the directory where the backup file is stored, and the default backup directory of SQL is used without specifying
@bkfname nvarchar (260) = ",---backup file name, file name can use/dbname/to represent the database name,/date/represents the date,/time/represents the time
@bktype nvarchar = ' db ',--Backup type: ' DB ' Backup database, ' DF ' differential backup, ' Log ' logs backup
@appendfile bit=1--Append/overwrite backup files
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[email protected]
+ ' to disk= '[email protected][email protected]
+ "with" +case @bktype when ' DF ' then ' differential, ' else ' end
+case @appendfile when 1 Then ' noinit ' Else ' INIT ' end
Print @sql
EXEC (@sql)
Go

/*3.--Recovering a database

*/

/*--Invocation Example
--Full Recovery database
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),---Define the backup file name to restore
@dbname sysname= ',--defines the restored database name, which defaults to the file name of the backup
@dbpath nvarchar (260) = ",---The restored database storage directory, the default data directory for SQL is not specified
@retype nvarchar = ' db ',--recovery type: ' db ' is done recovering the database, ' Dbnor ' for differential recovery, full recovery for log recovery, ' DF ' differential backup recovery, ' log ' log recovery
@filenumber int=1,--Recovered file number
@overexist Bit=1, whether to overwrite the existing database, only @retype
@killuser Bit=1--whether to turn off user use process, only valid when @overexist=1
As
DECLARE @sql varchar (8000)

--Get the restored 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 restored database storage directory
If IsNull (@dbpath, ') = ' Set @dbpath =dbo.f_getdbpath (')

--Generate Database recovery statement
Set @sql = ' Restore ' +case @retype when ' log ' then ' log ' Else ' database '  [email protected]
+ ' from disk= ' ' +case @retype when ' DBN Or ' Then ', NORECOVERY ' else ', RECOVERY ' end
Print @sql
--Add handling of the move logic file
If @retype = ' DB ' or @retype = ' Dbnor '
Begin
--Get the logical file name from the backup file
declare @lfn nvarchar, @tp char (1), @i int

--Create a temporary table, save the obtained information
CREATE table #tb (ln nvarchar, pn nvarchar (260), TP char (1), fgn nvarchar (+), sz numeric (20,0 ), Msz Numeric (20,0))
--Get information from backup file
insert into #tb exec (' Restore filelistonly from disk= ' ' [email  Protected] + " to " [email  protected] [email protected] +cast (@i as varchar)
+case @tp when ' D ' and '. '. ldf ' ' End
, @[email protected]+1
fetch NEXT from #f into @lfn, @tp
End
Close #f
deallocate #f
End

--Turn off User process processing
If @overexist =1 and @killuser =1
Begin
DECLARE @spid varchar (20)
DECLARE #spid cursor FOR
Select Spid=cast (spid as varchar) from master: sysprocesses where dbid=db_id (@dbname)
Open #spid
FETCH NEXT from #spid into @spid
While @ @fetch_status =0
Begin
EXEC (' kill '[email protected])
FETCH NEXT from #spid into @spid
End
Close #spid
Deallocate #spid
End

--Recover Database
EXEC (@sql)

Go

/*4.--Creating jobs

*/

/*--Invocation Example

--Monthly job execution
exec p_createjob @jobname = ' mm ', @sql = ' select * from syscolumns ', @freqtype = ' month '

--Jobs performed weekly
exec p_createjob @jobname = ' ww ', @sql = ' select * from syscolumns ', @freqtype = ' Week '

--Daily job execution
exec p_createjob @jobname = ' a ', @sql = ' select * from syscolumns '

--daily jobs that are repeated every 4 hours
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),--the command to execute
@dbname sysname= ',--defaults to the current database name
@freqtype varchar (6) = ' Day ',--period, month, Week week, day
@fsinterval int=1,--relative to the number of repetitions per day
@time int=170000-Start execution time, from 0 to 23:59 for jobs that are executed repeatedly
As
If IsNull (@dbname, ') = ' Set @dbname =db_name ()

--Create Job
EXEC msdb: Sp_add_job @[email protected]

--Create a job step
EXEC msdb: Sp_add_jobstep @[email protected],
@step_name = ' data processing ',
@subsystem = ' TSQL ',
@[email protected],
@command = @sql,
@retry_attempts = 5,--Retry count
@retry_interval = 5--Retry interval

--Create a schedule
declare @ftype int, @fstype int, @ffactor int
Select @ftype =case @freqtype when "Day" then 4
When the ' Week ' then 8
When the ' month ' then the 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 @[email protected],
@name = ' time schedule ',
@[email protected],--daily, 8 per week, 16 per month
@freq_interval = 1,--repeat number of executions
@[email protected],--Repeated execution
@[email protected],--repetition cycle
@[email protected],
@[email protected]--17:00:00 minute execution

Go

/*--Application Case-Backup solution:
Full backup (once every Sunday) + differential backup (Backup once per day) + log backup (backed up every 2 hours)

Call the stored procedure above to implement the
--*/

DECLARE @sql varchar (8000)
--Full backup (once every Sunday)
Set @sql = ' exec p_backupdb @dbname = ' database name to be backed up '
exec p_createjob @jobname = ' Weekly backup ', @sql, @freqtype = ' Week '

--Differential backup (backup once per day)
Set @sql = ' exec p_backupdb @dbname = ' database name to be backed up ', @bktype = ' DF '
exec p_createjob @jobname = ' Daily differential backup ', @sql, @freqtype = ' Day '

--Log backups (backed up every 2 hours)
Set @sql = ' exec p_backupdb @dbname = ' database name to be backed up ', @bktype = ' LOG '
exec p_createjob @jobname = ' log backup every 2 hours ', @sql, @freqtype = ' Day ', @fsinterval =2

/*--Application Case 2

Production Data Core library: Produce

The backup scenario is as follows:
1. Set up three jobs, daily backup to produce library, weekly backup, monthly backup
2. Create three new libraries, named: Daily backup, weekly backup, monthly backup
3. Set up three jobs to restore three backup libraries to the three new libraries above.

Purpose: When the user has any data loss in the produce library, it is possible to import the corresponding table data from the three backup libraries above.
--*/

DECLARE @sql varchar (8000)

--1. Create a monthly backup and generate monthly backup of the database, every 1 days per month 16:40 minutes:
Set @sql = '
declare @path nvarchar (260), @fname nvarchar (100)
Set @fname = ' Produce_ ' +convert (varchar), GETDATE (), + "_m.bak"
Set @path =dbo.f_getdbpath (null) [email protected]

--Backup
exec p_backupdb @dbname = "produce", @[email protected]

--Generate monthly new library based on backup
EXEC p_restoredb @[email protected], @dbname = ' Produce_ month '

--Prepare the base database for weekly database recovery
EXEC p_restoredb @[email protected], @dbname = ' Produce_ week ', @retype = ' Dbnor '

--Prepare the base database for daily database recovery
EXEC p_restoredb @[email protected], @dbname = ' Produce_ Day ', @retype = ' Dbnor '
‘
exec p_createjob @jobname = ' monthly backup ', @sql, @freqtype = ' month ', @time =164000

--2. Create weekly differential backups and generate weekly backup database jobs every Sunday 17:00:
Set @sql = '
declare @path nvarchar (260), @fname nvarchar (100)
Set @fname = ' Produce_ ' +convert (varchar), GETDATE (), + "_w.bak"
Set @path =dbo.f_getdbpath (null) [email protected]

--Differential backup
exec p_backupdb @dbname = ' produce ', @[email protected], @bktype = ' DF '

--Differential Recovery Week database
EXEC p_backupdb @[email protected], @dbname = ' Produce_ week ', @retype = ' DF '
‘
exec p_createjob @jobname = ' Weekly differential backup ', @sql, @freqtype = ' Week ', @time =170000

--3. Create daily log backups and generate daily backup database jobs every Sunday 17:15:
Set @sql = '
declare @path nvarchar (260), @fname nvarchar (100)
Set @fname = ' Produce_ ' +convert (varchar), GETDATE (), + "_l.bak"
Set @path =dbo.f_getdbpath (null) [email protected]

--Log backup
exec p_backupdb @dbname = ' produce ', @[email protected], @bktype = ' LOG '

--Log Recovery Day database
EXEC p_backupdb @[email protected], @dbname = ' Produce_ Day ', @retype = ' LOG '
‘
exec p_createjob @jobname = ' Weekly differential backup ', @sql, @freqtype = ' Day ', @time =171500

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

/*--Common stored procedures for backing up a database

--Jiangjian 2003.10 (please keep this information when quoting)--*/

/*--Invocation Example

--Back up the current database
exec p_backupdb @bkpath = ' c:/', @bkfname = '/dbname/_/date/_db.bak '

--Differential 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= ',--the name of the database to be backed up, not specified to back up the current database
@bkpath nvarchar (260) = ",--the directory where the backup file is stored, and the default backup directory of SQL is used without specifying
@bkfname nvarchar (260) = ",---backup file name, file name can use/dbname/to represent the database name,/date/represents the date,/time/represents the time
@bktype nvarchar = ' db ',--Backup type: ' DB ' Backup database, ' DF ' differential backup, ' Log ' logs backup
@appendfile bit=1,--Append/overwrite backup files
@password nvarchar (20) = "--the password set for the backup file (sql2000 support only), after Setup, this password must be provided upon recovery
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[email protected]
+ ' to disk= '[email protected][email protected]
+ "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= ' [email protected]+ ' ' End
EXEC (@sql)
Go

To back up SQL with a command