Note that the restore below is implemented in 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 procedure for backup database - Zou Jian 2003.10 - * /
/ ** /
/ * - Call example - Backup Current Database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = '/ dbname / _ / date / _db.bak' stored procedure Removes Backup and Restore Database: 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 / * - Backup database Universal Storage Procedure - Zou Jian 2003.10 - * /
/ ** /
/ * - Call example - Backup Current Database EXEC P_BACKUPDB @ BKPATH = 'C: /', @ bkfname = '/ dbname / _ / date / _db.bak' - Difference Backup Current Database EXEC P_BACKUPDB @ bkpath = 'C : / ', @ bkfname =' db_ / date / _df.bak ', @ bktype =' DF '- Backup Current Database Log EXEC P_BACKUPDB @ Bkpath =' C: / ', @ bkfname =' db_ / date / _log. Bak ', @ bktype =' log '- * /
Create
PROC
p_backupdb
@dbname
Sysname
=
'' '
,
-
To back up the database name, not specified, back up the current database
@BKPATH
nvarchar
(
260
)
=
'' '
,
-
The storage directory of the backup file, not specified, use SQL default backup directories
@BKFNAME
nvarchar
(
260
)
=
'' '
,
-
Backup file name, file name can be used in / dbname / represents the database name, / Date / Representative Date, / Time / Representative Time
@BkType
nvarchar
(
10
)
=
'
DB
'
,
-
Backup Type: 'DB' Backup Database, 'DF' Difference Backup, 'Log' Log Backup
@Appendfile
Bit
=
1
,
-
Adding / overlay backup file
@Password
nvarchar
(
20
)
=
'' '
-
This password must be provided when the password set for the backup file (SQL2000 only supports)
AS
Declare
@SQL
VARCHAR
(
8000
)
IF
ISNULL
(
@dbname
,
'' '
)
=
'' '
Set @ dbname
=
DB_NAME
()
IF
ISNULL
(
@BKPATH
,
'' '
)
=
'' '
Begin
SELECT
@BKPATH
=
RTRIM
(
Reverse
(filename))
From
Master..sfiles
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
(
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.SYSOBJECTSWHERE
id
=
Object_id
(N
'
[dbo]. [p_restoredb]
'
)
and
ObjectProperty
(ID, N
'
ISPROCEDURE
'
)
=
1
)
Drop
Procedure
[
DBO
]
.
[
p_restoredb
]
Go
/ ** /
/ * - Restore database universal stored procedures - Zou Jian 2003.10 - * /
/ ** /
/ * - Call example - Complete recovery database EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db' - Difference backup Restore 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 backup file names (with path) to recover
@dbname
Sysname
=
'' '
,
-
Define the reusable database name, default for backup file name
@dbpath
nvarchar
(
260
)
=
'' '
,
-
Restored data inventory places, not specified for SQL default data directory
@retype
nvarchar
(
10
)
=
'
DB
'
,
-
Recovery Type: 'DB' Full Recovery Database, 'DBNOR' is a different resumption, log recovery is complete, 'DF' Difference backup recovery, 'log' log recovery
@filenumber
int
=
1
,
-
Restore file number
@overexist
Bit
=
1
,
-
Whether to override the existing database, only @retype is "DB '/' dbnor 'is effective
@Killuser
Bit
=
1
,
-
Whether to close the user's use process, only @ overexist = 1 is effective
@Password
nvarchar
(
20
)
=
'' '
-
The password of the backup file (SQL2000 support only), if the password is set, this password must be provided
AS
Declare
@SQL
VARCHAR
(
8000
)
-
Get 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
)
-
Get recovered data inventory placement
IF
ISNULL
(
@dbpath
,
'' '
)
=
'' '
Begin
SELECT
@dbpath
=
RTRIM
(
Reverse
(filename))
From
Master..sfiles
WHERE
Name
=
'
Master
'
SELECT
@dbpath
=
Reverse
(
Substring
(
@dbpath
,
Charindex
(
'
/
'
,
@dbpath
),
4000
))
end
-
Generate 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
Case
ISNULL
(
@Password
,
'' '
)
WHEN
'' '
THEN
'' '
Else
'
PASSWORD =
'' '
@Password
'' ''
end
-
Add mobile logic file processing
IF
@retype
=
'DB
'
oral
@retype
=
'
DBNOR
'
Begin
-
Get logical file names from backup files
Declare
@LFN
nvarchar
(
128
),
@TP
charr
(
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
charr
(
1
), FGN
nvarchar
(
128
), SZ NUMERIC
20
,
0
), MSZ NUMERIC
20
,
0
))
-
Get information from backup files
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, MOVE
'' '
@LFN
'' '
TO
'' '
@dbpath
@dbname
CAST
(
@i
AS
VARCHAR
)
Case
@TP
WHEN
'
Di
'
THEN
'
.mdf
'' '
Else
'
.ldf
'' '
end
,
@i
=
@i
1
Fetch
NEXT
From
#f
INTO
@LFN
,
@TP
end
Close
#f
Deallocate
#f
end
-
Close User Process Process
IF
@overexist
=
1
and
@Killuser
=
1
Begin
Declare
HcForeach
Cursor
for
SELECT
s
=
'
Kill
'
CAST
(Spid)
AS
VARCHAR
)
From
Master..xysprocesses
WHERE
DBID
=
DB_ID
(
@dbname
)
EXEC
SP_MSFOREACH_WORKER
'
?
'
end
-
Restore database
EXEC
(
@SQL)
Go
Zou Jian said: To put it white, it is the application of the SQL statement of the database and restores the database:
-
Backup
BACKUP
Database
database
TO
DISK
=
'
C: / Your backup file name
'
-
reduction
RESTORE
Database
database
From
DISK
=
'
C: / Your backup file name
'
