关于数据更新、备份的问题

Question

http://expert.csdn.net/Expert/topic/2359/2359124.xml?temp=7.911319E-02

Answer 1

/*--备份数据库--邹建 2003.10--*//*--调用示例--备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='\DBNAME\_\DATE\_db.bak'--差异备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_df.bak',@bktype='DF'--备份当前数据库日志
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_log.bak',@bktype='LOG'--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_backupdb]
GOcreate proc p_backupdb
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
as
declare @sql varchar(8000)
if isnull(@dbname,'')='' set @dbname=db_name()
if isnull(@bkpath,'')=''
begin
select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
if isnull(@bkfname,'')='' set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK'
set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname)
,'\DATE\',convert(varchar,getdate(),112))
,'\TIME\',replace(convert(varchar,getdate(),108),':',''))
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname
+' to disk='''+@bkpath+@bkfname
+''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
+case @appendfile when 1 then 'NOINIT' else 'INIT' end
exec(@sql)
go

Answer 2

/*--恢复数据库--邹建 2003.10--*//*--调用示例
--完整恢复数据库
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db'--差异备份恢复
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_backupdb @bkfile='c:\db_20031015_df.bak',@dbname='db',@retype='DF'--日志备份恢复
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_backupdb @bkfile='c:\db_20031015_log.bak',@dbname='db',@retype='LOG'--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_RestoreDb]
GOcreate proc p_RestoreDb
@bkfile nvarchar(1000), --定义要恢复的备份文件名
@dbname sysname='',     --定义恢复后的数据库名,默认为备份的文件名
@dbpath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@retype nvarchar(10)='DB', --恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复
@filenumber int=1, --恢复的文件号
@overexist bit=1,        --是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效
@killuser bit=1     --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000)--得到恢复后的数据库名
if isnull(@dbname,'')=''
select @sql=reverse(@bkfile)
,@sql=case when charindex('.',@sql)=0 then @sql
else substring(@sql,charindex('.',@sql)+1,1000) end
,@sql=case when charindex('\',@sql)=0 then @sql
else left(@sql,charindex('\',@sql)-1) end
,@dbname=reverse(@sql)--得到恢复后的数据库存放目录
if isnull(@dbpath,'')=''
begin
select @dbpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @dbpath=reverse(substring(@dbpath,charindex('\',@dbpath),4000))
end--生成数据库恢复语句
set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname
+' from disk='''+@bkfile+''''
+' with file='+cast(@filenumber as varchar)
+case when @overexist=1 and @retype in('DB','DBNOR') then ',replace' else '' end
+case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end--添加移动逻辑文件的处理
if @retype='DB' or @retype='DBNOR'
begin
--从备份文件中获取逻辑文件名
declare @lfn nvarchar(128),@tp char(1),@i int --创建临时表,保存获取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
--从备份文件中获取信息
insert into #tb exec('restore filelistonly from disk='''+@bkfile+'''')
declare #f cursor for select ln,tp from #tb
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@i as varchar)
+case @tp when 'D' then '.mdf''' else '.ldf''' end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
end--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin  
exec('kill '+@spid)
fetch next from #spid into @spid
end  
close #spid
deallocate #spid
end--恢复数据库
exec(@sql)go

Answer 3

更多的参考我的贴子:数据库备份/恢复方案
http://expert.csdn.net/Expert/topic/2359/2359124.xml?temp=9.761989E-03为别人提供的备份方案
http://expert.csdn.net/Expert/topic/2559/2559560.xml?temp=.7928278

Answer 4

如何建立、维护以及联机备用服务器 (Transact-SQL)
备用服务器的建立通常包括在主服务器上创建数据库备份和定期事务日志备份，然后按照顺序将这些备份应用到备用服务器。在还原操作之间，备用服务器保持只读状态。当需要使用备用服务器时，任何未完成的事务日志备份，包括来自主服务器的活动事务日志备份都将应用到备用服务器中，同时还要恢复数据库。在主服务器上创建备份 执行 BACKUP DATABASE 语句以创建数据库备份。
执行 BACKUP LOG 语句以创建事务日志备份。
为每个希望随着时间的变化而创建的事务日志重复步骤 2。 
建立和维护备用服务器 执行使用 STANDBY 子句的 RESTORE DATABASE 语句以便还原步骤 1 中在主服务器上创建的数据库备份。在回滚未提交事务影响的数据页之前，指定包含这些数据页内容的撤消文件名称。
执行使用 STANDBY 子句的 RESTORE LOG 语句以便应用步骤 2 中在主服务器上创建的每个事务日志。
为每个在主服务器上创建的事务日志重复步骤 2。 
使备用服务器联机（主服务器发生故障） 执行使用 NO_TRUNCATE 子句的 BACKUP LOG 语句以备份当前活动的事务日志。当备用服务器联机时，这是将应用到备用服务器的最新事务日志备份。有关更多信息，请参阅如何创建当前活动事务日志的备份。
执行使用 STANDBY 子句的 RESTORE LOG 语句以便应用所有事务日志备份，包括步骤 1 中创建的仍未应用到备用服务器的活动事务日志备份。
执行 RESTORE DATABASE WITH RECOVERY 语句以恢复数据库并使备用服务器联机。 
示例
下例在备用服务器上安装 MyNwind 数据库。在还原操作之间，可以只读模式使用数据库。-- Restore the initial database backup on the standby server.
USE master
GO
RESTORE DATABASE MyNwind
   FROM MyNwind_1 
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Repeat for each transaction log backup created on the 
-- primary server.
--
-- Time elapses.. .. ..
--
-- The primary server has failed. Back up the 
-- active transaction log on the primary server.
BACKUP LOG MyNwind
   TO MyNwind_log3
   WITH NO_TRUNCATE
GO
-- Apply the final (active) transaction log backup
-- to the standby server. All preceding transaction
-- log backups must have been already applied.
RESTORE LOG MyNwind
   FROM MyNwind_log3
   WITH STANDBY = 'c:\undo.ldf'
GO
-- Recover the database on the standby server, 
-- making it available for normal operations.
RESTORE DATABASE MyNwind
   WITH RECOVERY
GO

Answer 5

使用备用服务器
备用服务器是当主生产服务器发生故障时可联机使用的第二个服务器。备用服务器包含主服务器数据库的复本。当主服务器因已调度的维护而不可用时，也可以使用备用服务器。例如，如果主服务器需要硬件或软件升级，即可使用备用服务器。备用服务器使用户得以在主服务器不可用时还能继续使用数据库。当主服务器再次可用时，必须将数据库复本在备用服务器上所发生的任何更改都还原到主服务器上。否则，这些更改都将丢失。当用户重新开始使用主服务器时，应将它的数据库进行备份并再次在备用服务器上还原。备用服务器的实现包括三个阶段： 在主服务器上创建数据库和正在进行的事务日志的备份。
通过对主服务器上的数据库进行备份并在备用服务器上还原来设置并维护备用服务器。
如果主服务器出现故障，使备用服务器联机。 
重要  当主服务器不可用时，所有用户进程都必须登录到备用服务器，并重新启动刚才正在执行的任何任务。用户进程不会自动切换到备用服务器上执行，并且不在主服务器和备用服务器之间维护事务。如果使主服务器脱离网络或对其手工重命名，同时重命名备用服务器，则备用服务器的网络名称和地址将不同于用户以前使用的服务器。 主服务器数据库中的事务日志备份定期应用到备用服务器，以保证备用服务器与主服务器保持同步。一旦主服务器或者只是一个数据库出现故障，备用服务器上的数据库即可用于用户进程。任何不能访问主服务器的用户进程都可转而使用备用服务器。备用服务器配置与 Microsoft® SQL Server™ 2000 故障转移群集中使用的虚拟服务器配置不同。备用服务器中包含 SQL Server 数据库的另一个复本。在虚拟服务器配置中，单个的数据库复本装载在共享群集磁盘上，由构成虚拟服务器基础的主要和次要物理服务器共享。在主服务器上创建备份
在主服务器上： 为每个要复制的数据库创建完整数据库备份。有关更多信息，请参见数据库备份。
定期为每个要复制的数据库创建事务日志备份。有关更多信息，请参见事务日志备份。 
在主服务器上创建事务日志备份的频率取决于生产服务器数据库的事务更改量。如果主数据库中的事务发生频率高，经常备份事务日志对降低故障中丢失数据的可能性很有帮助。重要  将 master 的复本从生产服务器还原到备用服务器时，不能备份 master 的事务日志。只能备份和还原 master 数据库。设置和维护备用服务器
备用服务器按如下方式进行设置和维护： 在备用模式下将主服务器上的数据库备份还原到备用服务器上，并指定撤消文件（每个数据库一个撤消文件）。 
在备用模式下恢复数据库或事务日志时，需要回滚任何未提交的事务，以使数据库在逻辑上保持一致的状态并在需要时用于只读目的。受未提交的回滚事务影响的数据库页将被修改。这将撤消未提交事务最初作出的更改。在恢复过程修改这些页的内容之前，将用撤消文件保存这些页的内容，以防未提交事务所执行的更改丢失。在下次将后续的事务日志备份应用于数据库之前，必须首先重新应用先前由恢复过程回滚的未提交事务。将撤消文件中保存的更改重新应用于数据库，然后再应用下一个事务日志。说明  必须有足够的磁盘空间供撤消文件增长，以使撤消文件能够包含数据库中由回滚的未提交事务修改的所有不重复的页。定期将主服务器上创建的每个后续事务日志应用于备用服务器上的数据库。在备用模式下应用每个事务日志，并指定先前还原数据库时所使用的同一撤消文件。 
对备用服务器应用事务日志备份的频率取决于主生产服务器数据库的事务日志备份频率。经常应用事务日志可减少在生产系统出现故障时使备用服务器联机所需要做的工作。在备用模式下，只能对数据库执行只读操作，例如不尝试修改数据库的数据库查询。这使数据库得以用于决策支持查询或 DBCC 检查。使备用服务器联机
当主服务器开始不可用时，备用服务器上的所有数据库都完全同步不太可能。一些在主服务器上创建的事务日志备份可能还没有应用于备用服务器。另外，自事务日志上次备份后，主服务器数据库很可能有一些改动，尤其是在使用频繁的系统中。在用户使用备用复本之前，主数据库有可能与备用复本同步且备用服务器可通过以下方法联机： 按顺序将在主服务器上创建的、尚未应用的任何事务日志备份应用于备用服务器。
在主服务器上创建活动事务日志的备份，并将其应用于备用服务器上的数据库。活动事务日志的备份在应用于备用服务器时，将使用户得以使用故障发生前一刻的主数据库复本，但任何未提交的事务都将永远丢失。有关更多信息，请参见事务日志备份。 
如果主服务器没有损坏，则当计划的维护或升级发生时，可以使用 NORECOVERY 备份活动事务日志。这将使数据库处于还原状态，并使您得以用来自辅助服务器的事务日志备份更新主服务器。然后无需创建辅助服务器的完整数据库备份即可切换回主服务器。有关更多信息，请参见 BACKUP。恢复备用服务器上的数据库。这将在不创建撤消文件的情况下恢复数据库，使用户得以修改数据库。 
一台备用服务器可以包含来自多个 SQL Server 实例的数据库备份。例如，某个部门可能有五台服务器，每台服务器都运行使命关键的数据库系统。可以使用一台备用服务器，而不必使用五台单独的备用服务器。五个主系统上的数据库备份都可以装载到这个备份系统中，从而减少必需的资源量并节省开支。不太可能出现多个主系统同时出现故障的情况。另外，备用服务器的规格可以比各主服务器高，以应付多个主系统在某一特定时刻同时不可用的极少见情况。

Answer 6

控制好SQLSERVER能够使用的最大内存数量,不能将全部内存都交给SQLSERVER

Answer 7

小马哥的想法不错，我也曾经想过，但它在我的知识范围之外。服务器是双cpu四通道、1G内存，可是这怎么分配系统资源，可以详细回帖吗？
做联机备用服务器也是一个很好的办法。但在数据传输过程中占用的系统资源应该怎么管理呢？千万条记录的联机传输也是很占用系统资源的。

Answer 8

对于处理这样一个实时在线系统，想通过备份恢复到指定的时间点，是没有必要的，所以我的意见是根本不需要日志备份，保证系统硬件的稳定是最重要的。
下面我谈谈我们的一个超市系统的安全策略：
1.SQLSERVER的功能选择，要是一台单独的服务器，只用作数据处理。
2.硬件的配置，CPU和MEMORY一定是越快越多，主要是HD，一定要作RAID5 （最好是RAID 10）。
3.SQLSERVER的配置，确定分配给MSSQLSERVER的MEMERY最好不要超过总计内存的80％，数据库的恢复模式最好是简单（不需要日志恢复。
4.每日作全备份，每周作INDEX根新和碎片处理。
5.有条件的话可以采用集群技术。

Answer 9

谢谢“黄山光明顶”。关于系统资源的配置不是什么问题，主要是要解决问题。主和备份服务器可以不是实时同步，要求的只是降低系统资源的占有率。
我觉得小马哥的想法不错“控制好SQLSERVER能够使用的最大内存数量,不能将全部内存都交给SQLSERVER”，但它在我的知识范围之外。服务器是双cpu四通道、1G内存，可是这怎么分配系统资源，可以有人详细回帖吗？

Answer 10

1G memory你可以分配800M给sqlserver,CPU让系统自动控制。
主要你要考虑HD的分配，比如可以考虑用多文件集份配到不同的分区，提高I/O的效率，采用多网卡....主要是要解决问题。主和备份服务器可以不是实时同步，要求的只是降低系统资源的占有率。
我认为会增加系统资源的占有率,主和备份服务器不会均衡负载，反而会增加，这只是一种安全的策略，要均衡负载只有通过集群技术，你可以安装WINDOWS SERVER 2003（企业版）,提供了很好的集群服务的工具。