SQL Server simple commands

Simple command:

Backup:

Backup database test to disk = 'd: \ 04-08-07bak.bak'

Restore: Restore database test from disk = 'd: \ 04-08-07bak.bak 'with norecovery

Database Backup/recovery using SQL

 

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 database name for obtaining the directory. 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 the 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 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 = '', -- Name of the database to be backed up, if this parameter is not specified, the current database is backed up @ bkpath nvarchar (260) = '', which is the storage directory of the backup file. If this parameter is not specified, the default SQL Backup Directory @ bkfname nvarchar (260) is used) = '', -- backup file name. \ dbname \ can be used in the file name to represent the database name, \ date \ represents the date, \ time \ represents the time @ bktype nvarchar (10) = 'db', -- Backup Type: 'db' backup database, 'df 'differential backup, '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 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 be restored @ dbname sysname = '', -- Define the database name after recovery, the default value is the backup file name @ dbpath nvarchar (260) = '', which is the storage directory of the recovered database. If this parameter is not specified, it is the default data directory of SQL. @ retype nvarchar (10) = 'db', -- Recovery Type: 'db' restores the database after completion, 'dbnor 'indicates differential recovery, log recovery is complete, and 'df' indicates differential Backup recovery, 'log' Log Recovery @ filenumber Int = 1, -- the recovered file number @ overexist bit = 1, -- whether to overwrite the existing database, only @ retype is @ killuser bit = 1 -- whether to disable the user process. It is valid only when @ overexist = 1 as declare @ SQL varchar (8000)

-- Obtain 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)

-- Obtain the recovered database storage directory if isnull (@ dbpath, '') = ''set @ dbpath = DBO. f_getdbpath ('')

-- generate the 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 @ retype when 'dbnor 'then ', norecovery 'else ', recovery 'end print @ SQL -- add processing of Mobile logical files 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, save the obtained information create table # Tb (LN nvarchar (128), PN nvarchar (260), TP char (1), FGN nvarchar (128), SZ numeric ), 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 for 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

-- Execute monthly job exec p_createjob @ jobname = 'mm', @ SQL = 'select * From syscolumns ', @ freqtype = 'month'

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

-- Daily job exec p_createjob @ jobname = 'A', @ SQL = 'select * From syscolumns'

-- For jobs executed every day, the job that repeats 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, -- compared with the number of repeated times per day @ time Int = 170000 -- the execution start time, for the repeated jobs, it will be from 0 to as if isnull (@ dbname ,'') = ''set @ dbname = db_name ()

-- Create job exec MSDB .. sp_add_job @ job_name = @ jobname

-- Create job step 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 a 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, -- Repeated execution times @ freq_subday_type = @ fstype, -- whether to execute repeatedly @ freq_subday_interval = @ fsinterval, -- recurrence @ freq_recurrence_factor = @ ffactor, @ active_start_time = @ time -- 17:00:00 execution

Go

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

Call the above stored procedure for implementation --*/

Declare @ SQL varchar (8000) -- full backup (once every day 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 scheme is as follows: 1. set three jobs to perform daily backup, weekly backup, and monthly backup for the produce database respectively. create three new databases named daily backup, weekly backup, and monthly backup. 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 job to back up and generate a monthly backup database every month at every day of the month: Set @ SQL = 'Clare @ 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 the monthly new database exec p_restoredb @ bkfile = @ path, @ dbname = 'produce _ month' Based on the backup''

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

-- Prepare the 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 backup database task at every Sunday: Set @ SQL = 'Clare @ 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 _ Week '', @ 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 backup database job at every Sunday: Set @ SQL = 'Clare @ 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 day database exec p_backupdb @ bkfile = @ path, @ dbname = 'produce _ Day '', @ retype = ''log'' 'exec p_createjob @ jobname = 'Weekly differential backup ', @ SQL, @ freqtype = 'day', @ time = 171500