现在问题是库越越大,全备份已不太可能了。差异备份也很难,即将每天发生的数据备份,但这种备份仍有问题,数据也大的惊人。我想问有没有对库中指定的几个表今天数据进行备份的机制,有人会说用select查询处啊,但我需要指出的是这是不可取的有很多表并没有日期或日期并不一个确定的参数比如update发生日期并不会变化,所以这种方法是不可取的。达到目标 对库中指定的几个表今天数据进行备份的机制,语句导出数据开成文件,同时提供可导入的语句
解决方案 »
- 没有扩展智能提示的
- 关于一个SQL语句的字符转换问题,请高手指点?
- 两几天有人自动删除我的数据,而且是2004-5-1号到2005-5-1号的数据,请问各位大侠我该怎么办???
- 求一个表里不同科目学生的人数
- 存储过程中判断插入是否成功
- date类型null值默认成1900-1-1
- 用SQL怎样把getdate得到的结果转换成yyyy-mm-dd格式
- SQL怎么分虚拟字段啊?在线等....急!!!急!!!急!!!急!!!急!!!
- 不解的性能问题,存储过程执行需要两分钟,拷到查询分析器中执行不到1秒
- 请帮我看看这个pb报错什么意思,谢谢!
- .sql 文件怎么导入到数据库
- 对course表的check约束设置不起,怎么办啊!哪里出错了,请各位帮帮忙!!
create database Test
on primary
(
name='Test_data.mdf',
filename='d:\Test_data.mdf'
)
log on
(
name='Test_data.ldf',
filename='d:\Test_data.ldf'
)
--创建测试表
create table Test.dbo.ta(id int)
insert into Test.dbo.ta select id from sysobjects
--创建备份文件
backup database Test to disk='d:\Test.bak' with format
--创建一个NDF文件
alter database Test
add file
(
name='file',
filename='d:\test.ndf',
SIZE = 1MB
)
--关闭MSSQL服务,然后删除NDF文件然后再打开MSSQL服务,此时Test数据库被疑置啦!
--恢复数据疑置
--首先备份日志文件
backup log Test to disk='d:\Test_log.bak' with format,no_truncate
--利用文件组备份文件恢复被破坏的文件
restore database Test from disk='d:\Test.bak' with Norecovery
--恢复日志文件
restore Log Test from disk='d:\Test_log.bak' with RECOVERY --TipS
--如果已经建立ndf文件的话那么恢复日志文件的话用下面的这句话
--只不过将with RECOVERY-->WITH REPLACE
restore Log Test from disk='d:\Test_log.bak' WITH REPLACE
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2010/04/07/5457193.aspx
如果是对不多的表进行备份,如下的方法仅供参考:1 可以建立一个辅助数据库,对这几个表进行备份.定点拷贝这几张表到辅助数据库.对该数据库备份.
可写CMD,批处理进行维护.2 也可以将表的数据导出成txt,fmt 对文本文件进行备份.维护txt或则平面文本的数据.
use master;
if db_id('MultipleFilesDB') is not null drop database [MultipleFilesDB];
CREATE DATABASE [MultipleFilesDB] ON PRIMARY
( NAME = N'MultipleFilesDB_Primary', FILENAME = N'd:\tt\MultipleFilesDB_Primary.mdf' , SIZE = 4096KB , FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'MultipleFilesDB_Secondary', FILENAME = N'd:\tt\MultipleFilesDB_Secondary.ndf' , SIZE = 4096KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MultipleFilesDB_log', FILENAME = N'd:\tt\MultipleFilesDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MultipleFilesDB', @new_cmptlevel=90
GO
ALTER DATABASE [MultipleFilesDB] SET RECOVERY FULL
GO
USE [MultipleFilesDB]
GO
IF NOT EXISTS (SELECT groupname FROM dbo.sysfilegroups WHERE (status & 0x10) != 0 AND groupname = N'PRIMARY') ALTER DATABASE [MultipleFilesDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
/* Create a test table on each filegroup */
USE [MultipleFilesDB]
GOCREATE TABLE [dbo].[OrdersPrimary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersPrimary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]GOCREATE TABLE [dbo].[OrdersSecondary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersSecondary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [SECONDARY]
) ON [SECONDARY]GO/* Populate the first order in each table */INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())/* Do a full backup */
BACKUP DATABASE [MultipleFilesDB] TO DISK = N'd:\tt\MultipleFilesDB.bak' WITH INIT, NAME = N'MultipleFilesDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO/* Add another set of rows so we can tell if the t-logs have been applied */
INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())/* Do a transaction log backup */
BACKUP LOG [MultipleFilesDB] TO DISK = N'd:\tt\MultipleFilesDB.trn' WITH NOFORMAT, INIT, NAME = N'MultipleFilesDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
/* doing a piecemeal restore: ,must restore the primary first*/
use master;
RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM DISK = N'd:\tt\MultipleFilesDB.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10, PARTIAL
GO--It shows one row returned because we didn't get our transaction logs.
SELECT * FROM MultipleFilesDB.dbo.OrdersPrimary;--select from the secondary filegroup,you'll get an error :
--SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary;/* bring the secondary filegroup online. */
RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM DISK = N'd:\tt\MultipleFilesDB.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
GOwaitfor delay '00:00:03';
/* And do another select */
SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary
use master;
if db_id('MultipleFilesDB') is not null drop database [MultipleFilesDB];
CREATE DATABASE [MultipleFilesDB] ON PRIMARY
( NAME = N'MultipleFilesDB_Primary', FILENAME = N'd:\tt\MultipleFilesDB_Primary.mdf' , SIZE = 4096KB , FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'MultipleFilesDB_Secondary', FILENAME = N'd:\tt\MultipleFilesDB_Secondary.ndf' , SIZE = 4096KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MultipleFilesDB_log', FILENAME = N'd:\tt\MultipleFilesDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MultipleFilesDB', @new_cmptlevel=90
GO
ALTER DATABASE [MultipleFilesDB] SET RECOVERY FULL
GO
USE [MultipleFilesDB]
GO
IF NOT EXISTS (SELECT groupname FROM dbo.sysfilegroups WHERE (status & 0x10) != 0 AND groupname = N'PRIMARY') ALTER DATABASE [MultipleFilesDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
/* Create a test table on each filegroup */
USE [MultipleFilesDB]
GOCREATE TABLE [dbo].[OrdersPrimary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersPrimary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]GOCREATE TABLE [dbo].[OrdersSecondary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersSecondary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [SECONDARY]
) ON [SECONDARY]GO/* Populate the first order in each table */INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())/* Do a full backup */
BACKUP DATABASE [MultipleFilesDB] TO DISK = N'd:\tt\MultipleFilesDB.bak' WITH INIT, NAME = N'MultipleFilesDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO/* Add another set of rows so we can tell if the t-logs have been applied */
INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())/* Do a transaction log backup */
BACKUP LOG [MultipleFilesDB] TO DISK = N'd:\tt\MultipleFilesDB.trn' WITH NOFORMAT, INIT, NAME = N'MultipleFilesDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
/* doing a piecemeal restore: ,must restore the primary first*/
use master;
RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM DISK = N'd:\tt\MultipleFilesDB.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10, PARTIAL
GO--It shows one row returned because we didn't get our transaction logs.
SELECT * FROM MultipleFilesDB.dbo.OrdersPrimary;--select from the secondary filegroup,you'll get an error :
--SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary;/* bring the secondary filegroup online. */
RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM DISK = N'd:\tt\MultipleFilesDB.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
GOwaitfor delay '00:00:03';
/* And do another select */
SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary
Microsoft® SQL Server™ 2000 支持备份或还原数据库中的个别文件或文件组。这是一种相对较完善的备份和还原过程,通常用在具有较高可用性要求的超大型数据库 (VLDB) 中。如果可用的备份时间不足以支持完整数据库备份,则可以在不同的时间备份数据库的子集。
例如,某站点需要花三小时备份数据库,并且每天只能用两个小时执行备份。该站点可在一个晚上备份一半文件或文件组,并在第二个晚上备份另一半。如果包含数据库文件或文件组的磁盘出现故障,那么该站点可以只还原丢失的文件或文件组。该站点还必须进行事务日志备份,并且在备份文件或文件组之后必须还原所有事务日志备份。
还可以从完整数据库备份集中还原文件和文件组。这将加快恢复速度,因为在第一步只还原已损坏的文件或文件组,而不是整个数据库。