To implement a backup and restore database programmatically

Backup | programming | data | Database Note that the following backup restores are implemented using stored procedures!

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 databases

--Jiangjian 2003.10--*/

/*--Call Example

--Back up the current database
exec p_backupdb @bkpath = ' C:\ ', @bkfname = ' \dbname\_\date\_db.bak '
Stored procedures implement backup and restore databases:
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 databases

--Jiangjian 2003.10--*/

/*--Call 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, and the current database is not specified
@bkpath nvarchar (260) = ',--the directory where the backup files are stored, and the SQL default backup directory is not specified
@bkfname nvarchar (260) = ',--backup file name, file name can use \dbname\ to represent database name, \date\ representative date, \time\ representative time
@bktype nvarchar = ' db ',--Backup type: ' DB ' backup database, ' DF ' differential backup, ' Log ' log backup
@appendfile bit=1,--Append/overwrite backup files
@password nvarchar (20) = '--the password set for the backup file (sql2000 support only), after setting, 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 + @dbname
+ ' to disk= ' + @bkpath + @bkfname
+ ' ' with ' +case @bktype when ' DF ' then ' differential, ' else ' end
+case @appendfile when 1 Then ' noinit ' Else ' INIT '
+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

/*--Common stored procedures for recovering databases

--Jiangjian 2003.10--*/

/*--Call 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_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 the backup file name (with path) to be recovered
@dbname sysname= ',--Define the restored database name, default to the file name of the backup
@dbpath nvarchar (260) = ',--restored database storage directory, unspecified SQL default data directory
@retype nvarchar = ' db ',--recovery type: ' DB ' finished recovery database, ' Dbnor ' for differential recovery, log recovery for full recovery, ' DF ' differential backup recovery, ' log ' log recovery
@filenumber int=1,--Recovered file number
@overexist Bit=1,-whether overwriting an existing database, @retype only ' DB '/' Dbnor ' is valid
@killuser Bit=1,--whether to turn off user-use processes, only valid @overexist=1
@password nvarchar (20) = '--Password for backup file (sql2000 only), if password is set at backup, this password must be supplied
As
DECLARE @sql varchar (8000)

--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, ') = '
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

--Add the processing of the mobile logical file
If @retype = ' DB ' or @retype = ' Dbnor '
Begin
--Get the logical file name from the backup file
declare @lfn nvarchar (128), @tp char (1), @i int,@s varchar (1000)

--Create a temporary table, save the obtained information
CREATE table #tb (ln nvarchar (128), PN nvarchar (in), TP char (1), FGN nvarchar (128), SZ Numeric (20,0), Msz Numeric (20,0))
--Get the 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

--Close User process processing
If @overexist =1 and @killuser =1
Begin
DECLARE Hcforeach cursor FOR
Select S= ' Kill ' +cast (spid as varchar) from master. sysprocesses
where dbid=db_id (@dbname)
exec sp_msforeach_worker '? '
End

--Restoring the database
EXEC (@sql)

Go
Jiangjian said:
To be blunt, is the application of the SQL statement that backs up the database and restores the database:

--Backup
Backup database to disk= ' C:\ your back file name '

--Restore
Restore database databases from disk= ' C:\ your backup file name '


PS: Jian Jianlo is really my idol Ah!