SQL Server2005 Automatic backup method for offsite _mssql2005

Recently, the company has just built an integrated platform for Taian, the Integrated platform server installed SQL Server 2005, because there is no backup server, so can only off-site backup database. Find a lot of information on the Internet, the integration of their own.

First, open the SQL Agent service, and set to "automatic"

Second, backup

1, SQL Agent-job-new job, this is under the SQL2005 operation, in fact, SQL2000 is similar (management →sql Server Agent → right key job → New job)

Figure 1

2, enter the name in the general

Figure 2

3, "Step" item-> new, enter the name, select the data, enter the following statement,

Figure 3

Statement as follows:

--In SQL Server2005, the Cmdshell command is turned off by default, and the Cmdshell command must first be opened:

--Show advanced options (performed only once)
EXEC sp_configure ' show advanced options ', 1
Go
Reconfigure
Go
--Allow execution of xp_cmdshell
EXEC sp_configure ' xp_cmdshell ', 1
Go
Reconfigure
Go

DECLARE @sql varchar (4000)
DECLARE @backupfile varchar (2000)
DECLARE @now datetime
DECLARE @retaindays int
DECLARE @deletefiles varchar (2000)
DECLARE @cmd varchar (2000)
DECLARE @i int


DECLARE @User varchar (2000)
DECLARE @Pwd varchar (2000)
DECLARE @Store varchar (2000)
DECLARE @IPPart varchar (2000)
DECLARE @IP varchar (2000)


-------------The following self modification-----------
Set @Store = ' employeeinfo '--------database name-----------
Set @User = ' liuhs '----------user name----------Administrator privileges
Set @Pwd = ' Winter '----------password----------password do not have a symbol
Set @IPPart = ' d$\back '-----path-----
Set @IP = ' 192.168.3.42 '-----server IP or host name--
Set @retaindays =1---The number of days to keep the backup
------------Above Self modification------------


--Add mapped drive
declare @string nvarchar (200)
Set @string = ' net use Z: \ \ + @IP + ' + ' + @IPPart + ' "Winter '/user:hp-liuhs\ ' + @User
EXEC master.. xp_cmdshell @string

Set @now =getdate ()
---Delete the previous backup, this has not been implemented well, have time to modify
Set @i=0
while (@i < 30)
Begin
Set @deletefiles = ' Z:\data ' + @Store + ' _db_ ' +convert (varchar (8), DATEADD (dd,-@retaindays-@i, @now), 112) + + '. B AK '
Set @cmd = ' del ' + @deletefiles
EXEC master.. xp_cmdshell @cmd
Set @i = @i +1
End

----Start Backup
Set @backupfile = ' Z:\data ' + @Store + ' _db_ ' + replace (replace (CONVERT (Varchar,getdate (), 20), '-', ', ', ', '), ': ','')+'. BAK '
Set @sql = ' backup database ' + @Store + ' to disk= ' + @backupfile + ' "with retaindays= ' +convert (varchar, @retaindays)
EXEC (@sql)

Set @backupfile = ' Z:\data ' + @Store + ' _tlog_ ' +replace (replace (CONVERT (Varchar,getdate (), 20), '-', ', ', ', '), ':','')+'. TRN '
Set @sql = ' backup LOG ' + @Store + ' to disk= ' + @backupfile + ' "with retaindays= ' +convert (varchar, @retaindays)
EXEC (@sql)

---delete mappings
EXEC master.. xp_cmdshell ' net use Z:/delete '

4. New job schedule, backup within specified time

Figure 4

5, the last query backup server is a successful backup

If "System error 5, Access Denied" appears:

The first thing to decide is to have sufficient permissions on the backup server, preferably the "Administrators" group account.

Second, on the backup server, open Group Policy-> Computer Configuration->windows Settings-> security Settings-> Local policy-> security options-> Network access: Local account sharing and security mode-> Classic-Local users authenticate as themselves.