A simple and safe way to back up your SQL data

Test · 发表于 2011-8-5 15:45:18

In many middle or small companies, peogrammers usually have some time to test or change coding in a real system, sometimes its dangerious to do that way, even did you do some menual backup of the real data, and sometimes you can not control that even the data backup itsself still can cause some data losing or strcture destory. so try to use stored procedures or just some script snippets will give you much easy, convenient and safe way to make that for you. actually you can still do some detail coding to make it use more friendly.

an easy script may simmilar like this:

PRINT 'Clear order data in the mirror database ...'
DELETE FROM [WilkyDB_Mir].[dbo].[His_Order_1]
--- WHERE (Cat_ID = 2011)
PRINT 'Copying order data to the mirror database ...'
INSERT INTO [WilkyDB_Mir].[dbo].[His_Order_1]
(RDate, ID, Name, CustomerID, ord_weight, ord_lns, issales_id, ord_num, inv_date, shp_date, ctype, stock_locat)
SELECT RDate, ID, Name, CustomerID, ord_weight, ord_lns, issales_id, ord_num, inv_date, shp_date, ctype, stock_locat
FROM WilkyDB.dbo.His_Order_1
---WHERE (Cat_ID = 2011)
PRINT 'Data mirror of order completed.'

复制代码

Do you think it's not clear enough for what's the codes doiing? you can always make it better, like:

DECLARE @mCount int, @mDate datetime;
SELECT @mCount=0, @mDate=GetDate();
SELECT @mCount=COUNT(*) FROM [WilkyDB_Mir].[dbo].[Dict];
SELECT TOP 1 @mDate=DT_Create FROM [WilkyDB_Mir].[dbo].[Dict]
ORDER BY DT_Create Desc;
PRINT 'Clear data dictionary data in the mirror database ...'
PRINT 'Total number of '+CONVERT(varchar, @mCount)+' last updated on: '+CONVERT(varchar,@mDate)+'.'
DELETE FROM [WilkyDB_Mir].[dbo].[Dict]
SELECT @mCount=COUNT(*) FROM [WilkyDB].[dbo].[Dict];
SELECT TOP 1 @mDate=DT_Create FROM [WilkyDB].[dbo].[Dict]
ORDER BY DT_Create Desc;
PRINT 'Copying data of '+CONVERT(varchar, @mCount)+' data dictionary to the mirror database ...'
INSERT INTO Dict
SELECT Label, Cat_ID, KEY_Code, KEY_Name, KEY_LNK, DT_Enable, DT_Disable, DT_Create, DT_Modify, Operator, Status, Notes
FROM WilkyDB.dbo.Dict AS Dict_1
SELECT @mCount=COUNT(*) FROM [WilkyDB_Mir].[dbo].[Dict];
SELECT TOP 1 @mDate=DT_Create FROM [WilkyDB_Mir].[dbo].[Dict]
ORDER BY DT_Create Desc;
PRINT 'Total of '+CONVERT(varchar, @mCount)+' data dictionary numbers are backed up dated to: '+CONVERT(varchar,@mDate)+'.'
PRINT 'Data dictionary mirror completed.'

复制代码

Seems more clear? but how if the database is huge and maybe you just want to backup the part usually changed? There you go:

PRINT 'Clear 2011 BSC Plan data in the mirror database ...'
DELETE FROM [WilkyDB_Mir].[dbo].[BSCSalesPlan]
WHERE (Cat_ID = 2011)
PRINT 'Copying 2011 BSC Plan data to the mirror database ...'
INSERT INTO WilkyDB_Mir.dbo.BSCSalesPlan
(Cat_ID, Bus_Unit, Prov, Branch, SalesPerson, CustID, CustName, OLS, CSR, PA, GV, ProcSCalls, TolSCalls, Tons, Sales, CGS, CostPerTon, GP, GPP,
NumOfOrders, GMI)
SELECT Cat_ID, Bus_Unit, Prov, Branch, SalesPerson, CustID, CustName, OLS, CSR, PA, GV, ProcSCalls, TolSCalls, Tons, Sales, CGS, CostPerTon, GP, GPP,
NumOfOrders, GMI
FROM WilkyDB.dbo.BSCSalesPlan
WHERE (Cat_ID = 2011)
PRINT 'Data mirror of 2011 BSC Plan completed.'

复制代码

You see, just a little bit further consideration, you can have it easily.

Then, mayb eit's a good idea to put them into the stored procedures. so you no need to worry about where to find it out. just expand the MSSMS, find the SP name, then click it. Done!

USE [WilkyDB_Mir]
GO
/****** Object: StoredProcedure [dbo].[Back_Dict] Script Date: 08/05/2011 16:34:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: John Zhang (cq_box @ 126.com)
-- Create date: August 5, 2011
-- Description: Backup data dictionary before any updating.
-- =============================================
CREATE PROCEDURE [dbo].[Back_Dict]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @mCount int, @mDate datetime;
SELECT @mCount=0, @mDate=GetDate();
SELECT @mCount=COUNT(*) FROM [WilkyDB_Mir].[dbo].[Dict];
SELECT TOP 1 @mDate=DT_Create FROM [WilkyDB_Mir].[dbo].[Dict]
ORDER BY DT_Create Desc;
PRINT 'Clear data dictionary data in the mirror database ...'
PRINT 'Total number of '+CONVERT(varchar, @mCount)+' last updated on: '+CONVERT(varchar,@mDate)+'.'
DELETE FROM [WilkyDB_Mir].[dbo].[Dict]
--- WHERE (Cat_ID = 2011)
SELECT @mCount=COUNT(*) FROM [WilkyDB].[dbo].[Dict];
SELECT TOP 1 @mDate=DT_Create FROM [WilkyDB].[dbo].[Dict]
ORDER BY DT_Create Desc;
PRINT 'Copying data of '+CONVERT(varchar, @mCount)+' data dictionary to the mirror database ...'
INSERT INTO Dict
SELECT Label, Cat_ID, KEY_Code, KEY_Name, KEY_LNK, DT_Enable, DT_Disable, DT_Create, DT_Modify, Operator, Status, Notes
FROM WilkyDB.dbo.Dict AS Dict_1
---WHERE (Cat_ID = 2011)
SELECT @mCount=COUNT(*) FROM [WilkyDB_Mir].[dbo].[Dict];
SELECT TOP 1 @mDate=DT_Create FROM [WilkyDB_Mir].[dbo].[Dict]
ORDER BY DT_Create Desc;
PRINT 'Total of '+CONVERT(varchar, @mCount)+' data dictionary numbers are backed up dated to: '+CONVERT(varchar,@mDate)+'.'
PRINT 'Data dictionary mirror completed.'
END
GO

复制代码

This is the original article, if you think its useful please let me know by just drop some words here. Thanks!

账号		自动登录	找回密码
密码			注册

A simple and safe way to back up your SQL data

浏览过的版块