LAN backup DATABASE and files

I. SUMMARY of previously

The server is set up on the VirtualBox virtual machine, and the database document of the corresponding server is backed up on the virtual machine. Now because of some kind of accident, the virtual machine hangs, need us to repair the virtual machine or copy the files inside the virtual machine to the new virtual machine, if the success of course is good, but unfortunately the current mastery of the technology can not repair the data can not be copied, how to do??? can only cry ~ ~ ~

Ii. Lessons Learned

In order to avoid the occurrence of data copying does not cause unnecessary trouble, we set up the virtual machine in the important files scheduled backup to the physical machine, the physical machine is relatively stable, and even if the system hangs also know how to copy the data back.

Third, the implementation method of database backup

Method One: Stored procedure + Maintenance plan + Batch processing + Timing Plan

For SQLSERVER2000 can be scheduled in the schedule to execute a certain TSQL statement to implement the database offsite backup, the following:

(1) source server: Create a stored procedure (bakup_database) under Master data to back up the database and copy the resulting backup document to the target server, implementing the statement:

Use [master]

GO

/****** object:storedprocedure [dbo].    [Bakup_database] Script Date:2016/6/29 11:11:55 ******/

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo]. [Bakup_database] As

/*

--Implement function: Periodically back up the sqlserver2000 database in the server to another computer on the LAN

--full BACKUP

--use MASTER

*/

SET NOCOUNT on

DECLARE @STRPSW varchar, @STRUSR varchar (@STRCMDSHELL varchar), @STRDATABASENAME varchar (20), @ Fullfilename VARCHAR (200)

DECLARE @FILEFLAG varchar, @TOFILENAME varchar, @SQLSTR varchar, @SQLSTR2 varchar ($), @FLAGDEL varchar ( 20)

DECLARE @NOWDATETIME VARCHAR (50)

--Date and time

SET @NOWDATETIME = replace (replace (CONVERT (CHAR (), GETDATE (), 20), ': ', '), '-', ')

--Need to fill in the Domain name \ username (the target machine's Windows login name)

SET @STRUSR = ' target server ip\administrator/y '

--Need to fill in the Windows login password

SET @STRPSW = ' target server login password '

--Need to fill in IP (IP address of target machine)

SET @STRCMDSHELL = ' NET use \ \ target server ip\e$\ destination folder ' + @STRPSW + '/user: ' [email protected]

--Need to fill in the IP (IP address of the target machine) and the saved directory (e.g., target server E- disk )

SET @TOFILENAME = ' \ \ destination server ip\e$\ destination folder '

--Fill true to delete the local backup temporary file, fill in false or other characters to indicate retention of the file

SET @FLAGDEL = ' FALSE '

------------------------------------back up the database-------------------------------------

--The database name of the backup

SET @STRDATABASENAME = ' database name to be backed up '

--backup file naming rules: Database name _ date time. bak

SET @FILEFLAG = @STRDATABASENAME + ' _ ' + @NOWDATETIME

--Need to fill in the local backup temporary files directory, because the need to back up to local and then copy to the target machine (such as: Save Directory as F:\DBbakup)

SET @FULLFILENAME = ' F:\DBbakup\ ' [email protected]+ '. Bak '

SET @SQLSTR = ' COPY ' [email protected]+ ' [email protected]

SET @SQLSTR2 = ' DEL ' [email protected]

BACKUP DATABASE @STRDATABASENAME to disk= @FULLFILENAME with INIT

--Try to connect to the target machine

EXEC MASTER. xp_cmdshell @STRCMDSHELL

--Copy to target machine

EXEC MASTER. xp_cmdshell @SQLSTR

--Delete The local backup temp file

IF (@FLAGDEL = ' TRUE ') EXEC MASTER: xp_cmdshell @SQLSTR2

GO

(2) Source server: Open SQL Server Enterprise Manager and create a maintenance plan above the database that you want to back up. You can set the time/period of the future run of this maintenance plan (performed 00:01 daily) as needed.

(3) Source server: In SQL Server Enterprise Manager, manage->sql Server Agent-and jobs, you can find the maintenance plan that you just established this job.

(4) Source server: Double-click the job, Pop-up Properties dialog box, on the steps page, click the "Edit" button, in the pop-up Edit Job Step dialog box, modify the "General" page, "command" text content, instead: EXECUTE master.dbo.bakup_ DataBase.

(5) Source server: Back here to complete. We found that at the end of the stored procedure we did not delete the local files, over time the hard disk will be full, then it is necessary to manually check the deletion of redundant historical documents, the workload is relatively large and cumbersome, So when we set up the full backup maintenance plan in subclause (2) We set it to delete the previous history document (we are setting up to delete the files backed up 2 days ago, so that the local only keep the latest 2 days of backup documents), to ensure that the hard disk is not easy to burst. However, the ideal is very plump reality is very skinny, automatically delete the history of the statement was commented out, failed to implement the automatic deletion function, at this time we wrote a delete 2 days ago The history document of the bat file (forfiles_del.bat) in the timing plan to execute the document, the bat file content is as follows:

@echo off

If not exist "F:\dbbakup\*%date:~0,4%%date:~5,2%%date:~8,2%*.bak" Goto:notfound

(:: F:\DBbakup A backup file that is generated on the same day deletes historical data generated 2 days before the folder

forfiles/p "F:\DBbakup"/M/* */d-2/* "Cmd/c del @path"

Goto:exit

)

: NotFound

(

Goto:exit

)

: Exit

Exit

Note: the (win2003) BAT file name cannot be too long or too complex, or the task plan cannot be executed.

(6) Target server: You will find that we have been to the target server to put new files, so that the target server's hard disk is easily filled, how to do??? Delete some history files, of course, by using Batch + timing schedule to delete historical data 2 days ago. Batch processing (Forfiles_del.bat, executed at 4 points per day) reads as follows:

@echo off

:d el83

If not exist "E:\sqldbbak\*%date:~0,4%%date:~5,2%%date:~8,2%*.bak" goto:notfound83

(:: Files created on the day of this directory are deleted 2 days ago Historical information

forfiles/p "E:\SQLDBBak"/M/* */d-2/* "Cmd/c del @path"

Goto:exit

)

: notfound83

(

Goto:exit

)

: Exit

Exit

(7) Complete

Method Two: Maintenance plan + Batch processing + Timing Plan

Take win2012 as an example to back up the bak document of db to \ \ target server ip\e$\ Sqldbbak, the implementation steps are as follows:

(1) Create a new database maintenance plan, set a daily 00:00 to start backing up the database and delete The historical data 2 days ago

(2) Daily 02:00 Copy the backup file to \ \ target server ip\e$\sqldbbak, using batch processing + Timing Plan, batch (copy (don ' Tdelete_important_useforsqlbak). bat) reads as follows:

net use \ \ target server ip\e$\sqldbbak User login password /user: target server ip\administrator/ Y

Copy d:\ "program Files" \ "Microsoft SQL Server" \mssql11. Sql2012\mssql\backup \ \ target server Ip\e$\sqldbbak

(3) Batch processing settings:

Start a task schedule and create a basic task, such as

Fill in on demand

The next step is to start the program by default, without changing the direct next

Check the error to complete. When finished, return to the main interface to see our newly created basic tasks.

Double tap to open

Double-click the Open property to complete the following settings:

(4) for the target server file has been increased processing methods, see method One ( 6 )

(5) Complete

Iv. Site File backup

copy files under E:\1.WEB to \ \ target server ip\f$\webbak, using batch processing (copy.bat) + timing plan, batch content as follows:

:: connecting to a remote server

net use \ \ target server ip\f$\webbak User login password ,/user: target server ip\ administrator/y

:: Copy Files

xcopy E:\1.WEB \ target server ip\f$\webbak/e/h/y

Note: the (win2003) BAT file name cannot be too long or too complex, or the task plan cannot be executed.

Description: There is no new, existing update, so there is no historical data to occupy the hard disk space problem

LAN backup DATABASE and files