SQL*Server 备份策略

作者：chao_ping    时间：01-10-14 18:13

Off-topic: SQL*Server 备份策略

朋友请我帮忙，无奈我也不懂SQLServer。
如何制定一个比较完备的SQL Server7的备份策略。
将要做磁盘镜像。
但是SQL Server本身肯定还是需要备份的。
能否那位兄弟有成熟的再用的备份策略：脚本/文档share 小弟一份！
谢谢。
chao_ping@163.com

---

作者：yanwang     时间：01-10-15 02:10

If you just want to backup once, follow the precedure below.
1. Open SQL Server Enterprise Manager.
2. Click Microsoft SQL servers
3. Click + on the left side of SQL Server group
4. Click + on the left side of the SQL server that you are interesting
5. Click + on the left side of Databases folder
6. Find the database that you want backup, right mouse click on the database.
7. Highlight All Tasks-->Backup databases...
8. Mark radio button Database-complete for full backup
9. Under destination, click add, then select the directory that the backup file will go, or select the tape drive by click Backup Device radio button. Then click OK. Then OK again. The backup will start.

If you want to schedule backup, you need to create a Database Maintenance Plan. The steps 1-4 are the same.
5. Click + on the left side of Management folder.
6. Right mouse click on Database Maintenance Plans, then click New Maintenance plan...
7. Click next.
8. Select databases that you want to backup, I always select all databases.
9. Click next.
10. select Update statistics used by query optimizer, or nothing. I don't select the other two selection, click next.
11. select Check database integrity. click next.
12. Mark Backup the database as part of the maintenance plan. Select tape if you want to backup to tape, or disk. Under the schedule, click change, then you could select the time you want, then click OK. click next
13. Mark Create a subdirectory for each database. Select the time you want to Remove files older than ?? weeks. Click next.
14. Mark Backup the transanction log as part of the maintenance plan. Then select tape or disk. same as before. you can change the time. For production, backup the transanction log every two hours is a normal setting. For heavily used system, the time could be 30 min. Click next.
15. Same as before, mark Create a subdirectory for each database. Select the time you want to Remove files older than ?? weeks. Click next.
16. Select write report to file or send an e-mail to operators. I did not use mail. Click next.
17. To write the record, you may use the default setting, click next.
18. Then review the setting, if everything is OK, click finish, you are done. Then check the plan is executed or not next day.

For cold backup, one need to shutdown SQL Server, then use OS copy command to copy datafiles. Another way is to use detach and attach.

Please check books online. You can find almost all the answers there. Good luck!

---

作者：snowhite2000     时间：01-10-15 04:05

象yanwang说的一样，做一次性backup还是create job or maintenance plan including backup plan for scheduling backup.

我是这样做的：

1.create two maintenance plans, one for system database (master, model and msdb) full disk backup at 11pm from Mon to Sat. keep one week's backup copies on the hard disk. SQL SERVER job engin will delete old more than 7 days backup automatically.
another maintenance for user database. Full disk backup at 11:30pm Mon. to Sat. too. Log files, once an hour from 8am to 8pm from Mon. to Friday.

2. OS admin will use some other tool backup whole hard disk to tape. so I will have two backup copies. I have all database datafiles and logfiles, I can use store procedure sp_attach_db or sp_attach_single_file_db, I also can use resotre database commande to restore database from backup database file.

只有一点，我所知道的是SQL SERVER不需要做COLD backup. 这是一个先做过Oracle database的人的误区。只要没有用户在线，没有transaction，DBA不需要shutdown SQL SERVER. 而所谓的shutdown 只是stop mssqlserver 的services 而已。

---

作者：yanwang     时间：01-10-15 05:11

Thanks for sharing your strategy! Your backup method is perfect.

---

作者：kezizi     时间：01-10-15 07:17

on NT, to shutdown oracle is also to stop oracle service.

---

作者：guo     时间：01-10-15 09:11

还好,我做了3年的sqldba,手头上有一些自己做的脚本
结构是这样的:
dbbklist表用于存放数据备份信息如各个数据库的备份目录,完全备份和增量备份时间间隔
结构如下:
dbname sysname,--数据库名
pastd tinyint,--自上次备份后的天数
fulld tinyint,--完全备份的时间间隔
diffd tinyint,--增量备份时间间隔
fdate varchar(10),--最近一次完全备份的时间
bpath varchar(255)--备份文件存放目录

下面是备份过程:
create proc backupdb
as
declare
@errorno int,--用于检查备份是否成功
@dbname sysname,
@pastd tinyint,
@fulld tinyint,
@diffd tinyint,
@fdate varchar(10),
@bpath varchar(255),
@today varchar(10)

declare dblist cursor
for select * from dbbklist--逐个数据库进行处理

set @today=convert(varchar(10),getdate(),20)

open dblist
fetch next from dblist into @dbname,@pastd,@fulld,@diffd,@fdate,@bpath

while @@fetch_status =0
begin
if (@fdate is null) or (@fdate ='') begin--需要初始化备份.任务:进行备份,写入当前日期至fdate中
exec ('BACKUP DATABASE '+@dbname+' TO DISK = '''+@bpath+@dbname+@today+'F.Bak'+
''' WITH INIT , NOUNLOAD , NAME ='''+@dbname+'在'+@today+'的完全备份'+
''', SKIP , restart, STATS = 10, NOFORMAT ')
update dbbklist set fdate=@today where current of dblist
print '数据库'+@dbname+'完成第一次数据库备份,本次完全备份日期为:'+@today
end
else begin--如果不空,说明已经进行过备份.将@pastd与@fulld,@diffd进行比较
if @pastd>=@fulld begin --当@pastdate不小于@fulldate时,进行完全备份
exec ('BACKUP DATABASE '+@dbname+' TO DISK = '''+@bpath+@dbname+@today+'F.Bak'+
''' WITH INIT , NOUNLOAD , NAME ='''+@dbname+'在'+@today+'的完全备份'+
''', SKIP , restart, STATS = 10, NOFORMAT ')
--备份完成后将当前日期写入到fdate中,将@pastdate置1,开始新一轮循环
update dbbklist set fdate=@today,pastd=1 where current of dblist
print '数据库'+@dbname+'开始新一轮完全与差异备份.当前日期为:'+@today
end
else begin--当@pastdate小于@fulldate时
if (@pastd>=@diffd) and (@pastd-@diffd*(@pastd/@diffd)=0) begin--当@pastdate不小于@diffd时,进行差异备份
exec ('BACKUP DATABASE '+@dbname+' TO DISK = '''+@bpath+@dbname+@fdate+'D.Bak'+
''' WITH INIT , NOUNLOAD ,DIFFERENTIAL , NAME ='''+@dbname+'在'+@today+
'的增量备份.上次完全备份时间:'+@fdate+''', SKIP , restart, STATS = 10, NOFORMAT ')
end
--备份完成后,将@pastdate加1,继续循环
update dbbklist set pastd=@pastd+1 where current of dblist
print '一天又一天'
end
end
--处理下一条记录
fetch next from dblist into @dbname,@pastd,@fulld,@diffd,@fdate,@bpath
end

close dblist
deallocate dblist

GO


具体使用上过程是:
使用job每天调用存储过程backupdb,由该过程检查是否需要进行备份以及何种备份,dblist记录对个个数据库的备份配置信息,可以使用标准insert ,update,delete进行编辑

这个过程在以前做sqldba时用过,后来转到oracle就不用了,有些地方需要完善比如老备份文件的清除功能没有做进去.

---

作者：chao_ping     时间：01-10-15 10:02

谢谢各位。
我用你们的方法试试看，有问题继续提问。
谢谢啦