在一个文件中备份所有内容？ [英] back everything up in a single file?
问题描述
问候，
我们以前的SQL Server 2000 DBA在一个磁盘上备份了所有内容
文件。我所说的全部备份，差异备份和
交易日志。有关当前备份的详细信息，请参阅下文
设置。当我查看DBBackup的''查看内容'时，我看到它包含了日期，完整和差异备份的
混合物。她已经退出了。
其他同事说（用不确定的语气）她能够从这样的单个文件恢复
数据库，尽管没有人见过或知道过怎么
她做了恢复。我对SQL Server的了解，特别是关于它的备份/恢复的b / b $ b bb是有限的。但我曾与其他
数据库合作，例如Oracle，MySQL和Postgres。我认为日志备份，完整的备份和差异备份应该是完全分开的。另外，每种类型的备份都需要
，每个数据库应该有自己的备份文件。
请指教。
== ===
日志备份：
CREATE PROCEDURE sp_lbackup AS
备份日志AGEP到DBBackup
备份日志careerfairs到DBBackup
备份日志CoEdocuments到DBBackup
备份日志委员会到DBBackup
备份日志会议到DBBackup
GO
完全备份：
CREATE PROCEDURE dbo.sp_fullbackup AS
备份数据库AGEP到DBBackup WITH INIT
备份数据库CoEdocuments到DBBackup
备份数据库careerfairs到DBBackup
备份数据库委员会到DBBackup
备份数据库会议到DBBackup
GO
差异备份：
CREATE PROCEDURE sp_diffbackup AS
备份数据库AGEP到DBBackup与差异
备份数据库careerfairs到DBBackup与差异
备份数据库CoEdocuments到DBBackup与差异
备份数据库委员会到DBBackup与差异
备份数据库会议到DBBackup与差异
GO
=====
预先感谢您的帮助，
Bing
Greetings,
Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did ''view contents'' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I''ve ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.
=====
Log backup:
CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO
Full backup:
CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO
Diff backup:
CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====
Thanks in advance for any help,
Bing
推荐答案
" Bing Du" < bd*@iastate.edu>在留言中写道
news：c1 ********** @ news.iastate.edu ...
"Bing Du" <bd*@iastate.edu> wrote in message
news:c1**********@news.iastate.edu...
问候，
我们以前的SQL Server 2000 DBA备份了单个磁盘文件中的所有内容。我的意思是完整备份，差异备份和事务日志。有关如何设置备份的详细信息，请参阅下文。当我查看DBBackup的查看内容时，我看到它包含了日期，完整和差异备份的混合日期。她放弃了。
其他同事说（以一种不确定的语气）她能够从这样一个文件中恢复数据库，尽管没有人看到或知道她是如何恢复的。我对SQL Server的了解，特别是关于其备份/恢复的知识是有限的。但我曾与其他
数据库合作，例如Oracle，MySQL和Postgres。我认为日志备份，完整的备份和差异备份应该完全分开。另外，在每种类型的备份中，每个数据库都应该有自己的备份文件。
请指教。
=====
日志备份：
创建过程sp_lbackup AS
备份日志AGEP到DBBackup
备份日志careerfairs到DBBackup
备份日志CoEdocuments到DBBackup
备份日志委员会到DBBackup
备份日志会议到DBBackup
GO
完全备份：
创建过程dbo.sp_fullbackup AS
备份数据库AGEP到DBBackup WITH INIT
备份数据库CoEdocuments到DBBackup备份数据库careerfairs到DBBackup
备份数据库委员会到DBBackup备份数据库会议到DBBackup
GO
差异备份：
创建过程sp_diffbackup AS
备份数据库AGEP到DBBackup与差异备份数据库careerfairs到DBBackup与差异备份数据库CoEdocuments到DBBackup与差异备份数据库委员会致D BBackup与差异备份数据库会议到DBBackup与差异
GO
=====
提前感谢您的帮助，
> Bing
Greetings,
Our former SQL Server 2000 DBA backed up everything in a single disk
file. By everything I mean, full backup, differential backup and
transaction logs. See below for details of how the backup is currently
set up. When I did ''view contents'' of DBBackup, I saw it contained the
mixture of log, full and diff backups of verious dates. She has quit.
Other coworkers said (in a not-sure tone) she was able to restore the
databases from such a single file, although nobody ever saw or knew how
she did recovery. My knowledge about SQL Server, especially regarding
its backup/restore is limited. But I''ve ever worked with other
databases, e.g. Oracle, MySQL and Postgres. I think log backups, full
backups and differential backups should be completely separated. Also,
in each type of backups, each database should have its own backup file.
Please advise.
=====
Log backup:
CREATE PROCEDURE sp_lbackup AS
Backup log AGEP To DBBackup
Backup log careerfairs To DBBackup
Backup log CoEdocuments To DBBackup
Backup log committee To DBBackup
Backup log conference To DBBackup
GO
Full backup:
CREATE PROCEDURE dbo.sp_fullbackup AS
Backup database AGEP To DBBackup WITH INIT
Backup database CoEdocuments To DBBackup
Backup database careerfairs To DBBackup
Backup database committee To DBBackup
Backup database conference To DBBackup
GO
Diff backup:
CREATE PROCEDURE sp_diffbackup AS
Backup database AGEP To DBBackup with differential
Backup database careerfairs To DBBackup with differential
Backup database CoEdocuments To DBBackup with differential
Backup database committee To DBBackup with differential
Backup database conference To DBBackup with differential
GO
=====
Thanks in advance for any help,
Bing
在上面的代码中，DBBackup不是一个文件，它是一个备份设备，所以在
理论中它''你的DBA可能会将设备指向不同的物理文件以提供某种轮换，尽管从你的
描述听起来不太可能。事实上，就个人而言，我无法看到如何使用她的方法有效地管理备份。
无论如何，如果你需要快速解决方案，我建议创建一个或多个
更多的数据库维护计划，并将所有数据库备份到一个方便的磁盘位置。之后，使用标准备份软件或
方法将备份文件复制到磁带和/或其他物理服务器。
维护计划向导（企业管理器，工具菜单）应该很容易
足以使用，你可以相当快地设置它。它会直接向文件备份
，也可以在指定了一段时间后删除旧备份。
我'不是说这是最好的或唯一的方法，并且向导不能提供一些
功能（例如差异备份），但
作为一种方式快速放置一些可管理的东西，它应该是好的。
罚款。
之后，找一个新的DBA和/或开始阅读联机丛书信息
onRecovery Models和备份和恢复操作。
Simon
In the code above, DBBackup is not a file, it''s a backup device, so in
theory it''s possible that your DBA used to point the device at different
physical files to provide some sort of rotation, although from your
description that sounds unlikely. In fact, personally, I can''t see how it
would be possible to manage backups effectively using her approach.
In any case, if you need a quick solution, I would suggest creating one or
more database maintenance plans, and back up all your databases to a
convenient disk location. After that, use your standard backup software or
methods to copy the backup files to tape and/or another physical server. The
maintenance plan wizard (Enterprise Manager, Tools menu) should be easy
enough to use that you can set this up fairly quickly. It will back up
directly to files, and can also remove old backups after a period you
specify.
I''m not saying that that is the best or only approach, and some
functionality (eg differential backups) isn''t available from the wizard, but
as a way of quickly putting something manageable in place, it should be
fine.
After that, find a new DBA and/or start reading the Books Online information
on "Recovery Models" and "Backup and Restore Operations".
Simon
非常感谢您的回复，Simon。我会查看您提到的在线
信息。我知道DBBackup不是一个文件。这是一个可以改为指向不同物理设备的逻辑设备。
但同时，每个备份设备只能指向一个物理设备/>
设备，无论是磁盘文件还是磁带，对吧？我们的SQL Server企业版
经理 - >管理 - >备份显示：
==========
名称物理位置设备类型
DBBackup e：\data \ MSSQL \ BACKUP \DBBackup.BAK磁盘备份
=== ========
所以，我不认为这个DBBackup.BAK包含完整的差异
和日志备份会起作用在恢复中。如果我错了，请纠正我。
Bing
***通过Developersdex发送 http://www.developersdex.com ***
不要只参加USENET。 ..得到奖励！
Thanks much for your response, Simon. I''ll check out the online
information you mentioned. I understand DBBackup is not a file. It''s a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:
==========
Name Physical Location Device Type
DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========
So, I don''t think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I''m wrong.
Bing
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
" comp.databases.ms-sqlserver" <一个******* @ devdex.com>在消息中写道
news：40 ********************* @ news.frii.net ...
"comp.databases.ms-sqlserver" <an*******@devdex.com> wrote in message
news:40*********************@news.frii.net...
信息。我知道DBBackup不是一个文件。它是一个逻辑设备，可以更改为指向不同的物理设备。
但同时，每个备份设备只能指向一个物理设备，无论是磁盘文件还是录音带，对吗？我们的SQL Server企业版
Manager-> Management->备份显示：
==========
名称物理位置设备类型
DBBackup e：\data \ MSSQL \ BACKUP \DBBackup.BAK磁盘备份
===========
所以，我不喜欢我认为这个包含完整，差异和/或日志备份的DBBackup.BAK可以在恢复中工作。如果我错了，请纠正我。
Bing
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
Thanks much for your response, Simon. I''ll check out the online
information you mentioned. I understand DBBackup is not a file. It''s a
logic device that can be changed to point to different physical devices.
But at the same time, each backup device can only point to one physical
device, either disk file or tape, right? Our SQL Server Enterprise
Manager->Management->Backup shows:
==========
Name Physical Location Device Type
DBBackup e:\data\MSSQL\BACKUP\DBBackup.BAK Disk Backup
===========
So, I don''t think this DBBackup.BAK which contains full, differential
and log backups would work in restore. Please correct me if I''m wrong.
Bing
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
<单个文件或设备可以包含多个备份，只要在<上下文中有意义，就可以从中恢复
任何单个备份。 br />
你正在恢复的东西（你不能将数据库A日志恢复到数据库B，对于
例子，或者不按顺序恢复日志）。如果你将备份
类型和数据库混合在一个文件中是没有关系的，除了从管理角度来看。
您可以选择单个备份到使用首先备份还原恢复企业管理
还原对话框落下。这可能是一个更清晰的方式来演示你可以做什么（仅在测试服务器上，
课程），假设你有sysadmin权限：
/ *创建测试数据库* /
创建数据库foo
go
/ *备份数据库两次，并且日志一次，到同一个文件，
**中间有一个msdb备份，表明我们可以将b $ b **混合备份。
* /
备份数据库foo到disk =''c：\ foo.bak''
go
备份数据库foo到disk =''c：\ foo.bak''
go
备份数据库msdb到disk =''c：\ foo.bak ''
go
backup log foo to disk =''c：\ foo.bak''
go
/"查看文件中可用的备份* /
从disk =''c：\ foo.bak恢复headeron''
go
/ *恢复第一个备份并使数据库可用* /
从disk =''c：\ foo.bak'恢复数据库foo，文件= 1，恢复
go
/ *恢复第二次备份，但不恢复（所以我们可以应用日志）* /
从磁盘恢复数据库foo = ''c：\ foo.bak''与file = 2，norecovery
go
/ *恢复日志并使数据库可用* /
从disk =''c：\ foo.bak'恢复log foo，文件= 4，恢复
go
/ *清理* /
drop database foo
go
exec master..xp_cmdshell''del c：\ foo.bak''， no_output
go
Simon
A single file or device can contain multiple backups, and you can restore
any individual backup from it, as long as it makes sense in the context of
what you''re restoring (you can''t restore database A logs to database B, for
example, or restore logs out of order). It doesn''t matter if you mix backup
types and databases in one file, except from a management perspective.
You can select the individual backup to restore in the Enterprise Manage
restore dialogue using the "First backup to restore" drop down. This may be
a clearer way to demonstrate what you can do (on a test server only, of
course), assuming you have sysadmin permissions:
/* Create a test DB */
create database foo
go
/* Back up the DB twice, and the log once, to the same file,
** with an msdb backup in the middle to show that we can
** mix backups.
*/
backup database foo to disk = ''c:\foo.bak''
go
backup database foo to disk = ''c:\foo.bak''
go
backup database msdb to disk = ''c:\foo.bak''
go
backup log foo to disk = ''c:\foo.bak''
go
/" View the backups available in the file */
restore headeronly from disk = ''c:\foo.bak''
go
/* Restore the 1st backup and make the DB available */
restore database foo from disk = ''c:\foo.bak'' with file = 1, recovery
go
/* Restore the 2nd backup, but do not recover (so we can apply the log) */
restore database foo from disk = ''c:\foo.bak'' with file = 2, norecovery
go
/* Restore the log and make the DB available */
restore log foo from disk = ''c:\foo.bak'' with file = 4, recovery
go
/* Clean up */
drop database foo
go
exec master..xp_cmdshell ''del c:\foo.bak'', no_output
go
Simon
这篇关于在一个文件中备份所有内容？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
