SQL Server backup database SQL script (LAN)

-- ===================================================== ======
-- Author: Shu mu
-- Create Date: 2011-02-15
-- Description: SQL script used by SQL Server to back up a database (LAN)
-- ===================================================== ======
Create proc [DBO]. [usp_sys_backupdb]
@ Dbname varchar (50) = NULL, -- database to be backed up
@ Backuptype varchar (50) = 'full' -- Backup Type Full full backup diff differential backup
As
Begin
Exec sp_configure 'show advanced options', 1
Reconfigure

Declare @ dbname varchar (50)
Declare @ backuptype varchar (50)

Set @ dbname = db_name ()
Set @ backuptype = 'full'

declare @ filepath varchar (100) -- backup storage path
declare @ filename varchar (100) -- backup file full name
declare @ PWD varchar (50) -- username
declare @ uid varchar (50) -- password

Set @ dbname = isnull (@ dbname, db_name ())
Set @ filepath = '\ 192.168.1.125 \ F $ \ dbbak \ full'
If @ backuptype = 'diff'
Begin
Set @ filepath = Replace (@ filepath, 'full', 'diff ')
End

Set @ filename = @ filepath + '\'
+ @ Dbname
+ Convert (varchar (100), getdate (), 112)
+ '_'
+ @ Backuptype
Set @ Pwd = '02'
Set @ uid = 'wl-02 \ Administrator'

Exec master .. sp_configure 'xp _ Your shell', 1
Reconfigure

Declare @ authority varchar (100)
Set @ authority = 'net use' + @ filepath + '"' + @ PWD + '"/User: "' + @ uid + '"'
Exec master .. xp_mongoshell @ authority
-- Full backup
If @ backuptype = 'full'
Begin
Set @ filename = @ filename
+ '_'
+ Ltrim (STR (datepart (wk, getdate () % 2 ))
+ '. Bak'
Backup Database @ dbname to disk = @ filename with init, retaindays = 6
End

-- Differential backup
If @ backuptype = 'diff'
Begin
Set @ filename = @ filename
+ '_'
+ Ltrim (STR (datepart (DW, getdate ()-1 ))
+ '. Bak'
Backup Database @ dbname to disk = @ filename with init, differential, retaindays = 6
End

Declare @ del varchar (100)
Set @ del = 'net use' + @ filepath + '/delete'
Exec master .. xp_mongoshell @ del
Exec master .. sp_configure 'xp _ Your shell', 0
Reconfigure

Exec sp_configure 'show advanced options', 0
Reconfigure
End