Tips for simple backup and migration of large data Databases

Today, I am so ashamed .... Because of my carelessness, I deleted millions of data records, deleted. The customer's important data for a few years is gone. I was worried about it. Fortunately, I got it back.

You must exercise caution when operating big data.

In SQLServer2005, how do we migrate data from one database server to another database server?

Let me first talk about my practice today: Because my notebook has no software installed, I cannot show it to you. You can do it yourself)

At the beginning, I used a method that was very stupid and insecure, and everyone took the precaution.

The boss handed me the task. When I thought about so much data, I couldn't insert it one by one. After thinking for a while, I decided to use SQLServer's built-in tools, right-click, and import/export data. When t-sqlsyntax is used to export data, operate on a data warehouse server and copy the queried data to a file in the format of. rptand. txt. Then, import the data to another server. When I operate on the data of the first few tables, I was very proud. When I found that the imported data was of the datetime type in the last table, I found it dumb, tens of thousands of data entries can't be imported after the previous dozens of normal data entries are imported to the next one? Success! Why? I checked the data and found that there was a null value in the piece of shell data. Well ??? Why can't I import data if it is empty? The differences between the varchar and datetime types are all known to me. Yes, this is the problem. When we export the data, the most serious drawback of the data is that the field description is missing and all data is changed to the varchar type. If you don't believe it, you can use the import tool to import it to a new table. You can try it on your own.) You will find that the default data type is varchar. Tragedy, this method unfortunately failed!

The correct method is:

I, Use a T-SQL statement to create a temporary table, which is the same as the field definition you need to export the data table. Assume that the database table to be exported is A. The temporary table is A_Temp.

 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1912295212-0.jpg "border =" 0 "alt =" "/>

 

Look at the T-SQL statement:

 
 

  
  
INSERT  INTO  A_Temp  SELECT  *  FROM  A  WHERE 1 = 1  AND  BeginTime  > ’2012-11-05 00:00:00’ AND BeginTime < ‘2012-11-06 00:00:00’ 
 
 

Copy the filtered data to the_Temp table.

 

Then operate on another database server:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/19122a516-1.jpg "border =" 0 "alt =" "/>

 

Use a T-SQL statement to create a temporary table, which is the same as the field definition you need to export the data table.

 

II, Use SQLServer import and export tool to import the database table A_Temp of 22 to the database table B _Temp of 23. Now add the data in the B _Temp table to table B. This is done.

 

 
 

  
  
INSERT  INTO  B  SELECT  *  FROM  B_Temp 
 
 

 

 

Summary:

1) You can clearly understand the data you want to export to facilitate later maintenance and query;

2) No Data Type MismatchAt that time, it was because of my carelessness that the Delete statement was executed as a Select statement during data query. As a result, the results resulted in serious consequences and almost lost money. Khan...

3) The idea is clear and rigorous.

 

Well, today's summary is here. There is no end to learning. You must be careful in your work. When you operate on a large amount of data, you must consider every time you use Update, Delete, and Insert. Check again and think about what you are doing?Don't be so excited.

This article is from the "On My Way" blog, please be sure to keep this source http://shuyangyang.blog.51cto.com/1685768/1050935