I'm sure everyone is looking out for the loss of the database, which is also a headache for every developer, because the server and database are running. If the server crashes, then all the data does not exist, even if you do a backup on your machine, it is no use, why would say this is mainly because the computer is open to the Internet, so the probability of accident is relatively high. Eventually we can use the LAN to solve this problem. Take two machines as an example, a machine is a server, B machine is to put a server on the database back to B machine, the premise is B machine must have a database, SQL Server2005 as an example.
First a machine must have a database, procedures, iis,b machine must have a database, service procedures. The premise is that B machine must know the machine password of machine A.
Second, the code operates as follows this is just a stored procedure
IF not EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Backsqlorfile] and type in (n ' P ', n ' PC '))
BEGIN
EXEC dbo.sp_executesql @statement = N '
CREATE proc [dbo]. [Backsqlorfile]
@ServerIp varchar,--database server IP
@ServerName varchar,--Database computer users
@ServerPwd varchar,--database Computer User password
@ServerIISIp varchar,--IIS Server IP
@ServerIIsName varchar,--IIS server name
@ServerIIsPwd varchar,--IIS Server password
@SqlShareFile varchar,--remote SQL shared files
@IIsShareFile varchar,--remote IIS shared files
@MoveIIsPath varchar (,--d:/back)
@MoveSqlPath varchar (,--d:/sql)
@DataName varchar,--database name
@SqlloginName varchar,--Remote Database login name
@SqlLoginPwd varchar (50)--Remote database login password
As
Begin
/*=====================================================================*/
--create Data 2007-05-21
--modify Data 2007-05-21
--autor:sp
--method: Database remote backup, virtual host remote backup, IIS remote backup
/*====================================================================*/
DECLARE @iisname varchar (1000)--Back up the name of IIS
DECLARE @IISversion varchar (1000)--iis version number
DECLARE @MovePath varchar (1000)--copy file's road strength
DECLARE @IIsBack varchar (1000)--iis backup command
DECLARE @IIsBackmd varchar (1000)--iis a MD file backed up as a suffix
DECLARE @IIsBacksc varchar (1000)--iis a SC file backed up as a suffix
DECLARE @SqlName varchar (1000)--Backing up the database name
DECLARE @SqlPath varchar (1000)--Backing Up Database Files Jr.
DECLARE @IISyshe varchar (1000)--iis snare mapping
DECLARE @Sqlyshe varchar (1000)--sql network mapping
DECLARE @sqlback varchar (1000)---database backup command
DECLARE @url varchar (1000)--the path to the remote backup of the database
DECLARE @osql varchar (1000)--database backup DOS operation command
Begin
--naming rules ' filename + month + day + year '
Set @SqlName = @DataName +replace (CONVERT (varchar), GETDATE (), (), ""-"," ") +". Bak "
Set @url = ' E:/program files/microsoft SQL server/mssql.1/mssql/backup/' + @SqlName
Set @sqlback = ' backup database ' + @DataName + ' to disk= ' ' + @url + ' "' with Init '
Set @iisname = ' IIs ' +replace (CONVERT (varchar), GETDATE (), 110), '-', ', '
Set @osql = ' osql-s ' + @ServerIp + '-u ' + @SqlloginName + '-P ' + @SqlLoginPwd + '-dmaster-q ' + @sqlback + ' "'
Set @IISversion = ' 123 '--//replace (CONVERT (varchar), GETDATE (), 120), ': ', '-'
Set @IISBack = ' iisback/backup/s ' + @ServerIISIp + '/u ' + @ServerIIsName + '/p ' + @ServerIIsPwd + '/b ' + @iisname + '/V ' ' + @IISversion + '--remote backup of IIS files Road strength
-Where/S on behalf of the remote computer name or ip/u on behalf of the user/P/b for the backup name/V on behalf of the version number you backed up/e to encrypt the backup file
Set @IIsBackmd = ' Copy y:/' + @iisname + '. MD ' + @IISversion + ' ' + @MoveIIsPath + ' '
Set @IIsBacksc = ' Copy y:/' + @iisname + '. SC ' + @IISversion + ' ' + @MoveIIsPath + ' '
Set @Sqlpath = ' Copy q:/' + @SqlName + ' + @MoveSqlPath + ' '
Set @IISyshe = ' net use y://' + @ServerIISIp + '/' + @IIsShareFile + ' ' ' + @ServerIIsPwd + ' ' '/user: ' + @ServerIISIp + '/' + @ServerIIsName + "'"
Set @Sqlyshe = ' net use Q://' + @ServerIp + '/' + @SqlShareFile + ' ' ' + @ServerPwd + ' ' '/user: ' + @ServerIp + '/' +@ Servername+ ' ""
EXEC master.. xp_cmdshell @IISBack--Remote backup
EXEC master.. xp_cmdshell @IISyshe--//to create a network map of IIS '
EXEC master.. xp_cmdshell @IIsBackmd
EXEC master.. xp_cmdshell @IIsBacksc
EXEC master.. xp_cmdshell ' net use y:/delete '--Delete mappings
EXEC master.. xp_cmdshell @osql--Remote backup Database
EXEC master.. xp_cmdshell @Sqlyshe--//set up a network mapping database '
EXEC master.. xp_cmdshell @Sqlpath
EXEC master.. xp_cmdshell ' net use Q:/delete '--Delete mappings
End
End
'
End
IF not EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Creat_job] and type in (n ' P ', n ' PC '))
BEGIN
EXEC dbo.sp_executesql @statement = N '
CREATE proc [dbo]. [Creat_job]
@prorcname varchar (1000), the name of the stored procedure to invoke the timed call, or, if not in the current library, the name of the library: Owner name ... Stored Procedure Name
@job_date datetime-The execution time of the stored procedure (including time information)
As
declare @dbname sysname, @jobname sysname, @date int, @time int
Select @jobname = ' Job _ ' +cast (newid () as varchar (36))
, @date =convert (varchar, @job_date, 112)
, @time =replace (CONVERT (varchar, @job_date, 108), ': ', '
if exists (select * from msdb.. sysjobs where Name= @jobname)
EXEC msdb.. Sp_delete_job @job_name = @jobname
--Create Job
EXEC msdb.. Sp_add_job @job_name = @jobname, @delete_level =0
--Create job step
DECLARE @sql varchar (1000)
Select @sql = ' EXEC ' + @prorcname
, @dbname =db_name ()
EXEC msdb.. Sp_add_jobstep @job_name = @jobname,
@step_name = ' Execute steps ',
@subsystem = ' TSQL ',
@database_name = @dbname,
@command = @sql,
@retry_attempts = 5--Number of retries
@retry_interval = 5--Retry interval
--Create a schedule
EXEC msdb.. sp_add_jobschedule @job_name = @jobname,
@name = ' time arrangement ',
@enabled = 1,
@freq_type = 4, representing daily execution
@active_start_date = @date,
@active_start_time = @time,
@freq_interval =1--at least once.
--Add target server
EXEC Msdb.dbo.sp_add_jobserver
@job_name = @jobname,
@server_name = N ' (local) '
'
End
It also backs up IIS and maps through the network, which results in a backup file on machine A and the same backup file on B. The operation of the job is also used here.
Note: SQL Server2005 By default is not open xp_cmdshell This is the system stored procedure,
So we have to open it exec sp_configure ' show advanced options ', 1, and then run reconfigure once again run EXEC sp_configure ' xp_cmdshell ', 1, the last reconfigure.
