sql server 面试题（2） - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql server 面试题（2）

sql server 面试题（2）# Database - 数据库

z*y2008-10-27 07:10

1 楼

Now I have a VLDB about 2.5 Tera bytes. Now I need to do full backup right
away, but also find that the log backup job is running. So in that case can
I go ahead doing full backup while the log backup is running?
这个是为一家软件公司的Principle Field Engineer面试的。属于那种比较不常见的
问题。
还有一道关于备份的。
You as DBA need to do full backup for your production database, but you were
told the backup plan for this database is FULL==》DIFF==>DIFF==>FULL==>DIFF
==>DIFF==>DIFF from Sunday to Saturday. You were also told to keep t

j*n2008-10-27 07:10

2 楼

1.
didn't try concurrent backups before... however, based on BooksOnline,
backup is an online operation except DDL. I believe that you can do it
without problem.
2.
Yes, this chain is doable.
Use SSIS for maintenance plan.

z*y2008-10-27 07:10

3 楼

第一个解释的很好。
第二个：应该用backup database COPY_ONLY option.

j*n2008-10-27 07:10

4 楼

哦，再仔细看了一遍你的问题，才发现自己理解错误，呵呵
COPY_ONLY
Specifies that the backup is a copy-only backup, which does not affect the
normal sequence of backups. A copy-only backup is created independently of
your regularly scheduled, conventional backups. A copy-only backup does not
affect your overall backup and restore procedures for the database.
Copy-only backups were introduced in SQL Server 2005 for use in situations
in which a backup is taken for a special purpose, such as backing up the log
before an online

【在 z***y 的大作中提到】

: 第一个解释的很好。
: 第二个：应该用backup database COPY_ONLY option.

c*d2008-10-27 07:10

5 楼

1. 我认为没问题
不过我觉得jackrun的解释有问题
文档里有这么一句话
"The transaction log cannot be backed up during a full database backup or a
differential database backup."
如果按照jackrun的解释,在database backup和diff backup过程中也应该可以log back
up

【在 z***y 的大作中提到】

: 第一个解释的很好。
: 第二个：应该用backup database COPY_ONLY option.

c*d2008-10-27 07:10

6 楼

2. 没看懂这道题问什么呢
Sun ==> Mon ==>Tue ==>Wed ==>Thur ==>Fri ==>Sat
FULL0==>DIFF1==>DIFF2==>FULL1==>DIFF3==>DIFF4==>DIFF5
恢复策略是,
failure before Mon backup, FULL0 + log
failure before Tue backup, FULL0 + DIFF1 + log
failure before Wed backup, FULL0 + DIFF2 + log
failure before Thur backup, FULL1 + log
failure before Fri backup, FULL1 + DIFF3 + log
failure before Sat backup, FULL1 + DIFF4 + log
failure before Sun backup, FULL1 + DIFF5 + log
Sun ==> Mon ==>Tue ==>Wed ==>Thur ==>Fri

c*d2008-10-27 07:10

7 楼

w*e2008-10-27 07:10

8 楼

是没问题.
不过, zenny出的这题有问题.
在backup database时再进行log backup, 就破坏了该log chain...因为这时进行的log
backup是以它之前的full backup为基础的. 说笼统点是, 这时的log backup基本没用.
Worst scenario是: 这时的log backup有点乱, 可能某transaction是之前full backup
的, 某transaction 是之后这个full backup的. ---- (not sure about this. zenny
说说?)

a
back

【在 c*****d 的大作中提到】

: 1. 我认为没问题
: 不过我觉得jackrun的解释有问题
: 文档里有这么一句话
: "The transaction log cannot be backed up during a full database backup or a
: differential database backup."
: 如果按照jackrun的解释,在database backup和diff backup过程中也应该可以log back
: up

w*e2008-10-27 07:10

9 楼

酷比是oracle的, 估计不太懂SQL Server.
在SQL 2000里, 这个要求是不可能实现的, 所以LZ特意强调了SQL2005.

【在 c*****d 的大作中提到】

: 2. 没看懂这道题问什么呢
: Sun ==> Mon ==>Tue ==>Wed ==>Thur ==>Fri ==>Sat
: FULL0==>DIFF1==>DIFF2==>FULL1==>DIFF3==>DIFF4==>DIFF5
: 恢复策略是,
: failure before Mon backup, FULL0 + log
: failure before Tue backup, FULL0 + DIFF1 + log
: failure before Wed backup, FULL0 + DIFF2 + log
: failure before Thur backup, FULL1 + log
: failure before Fri backup, FULL1 + DIFF3 + log
: failure before Sat backup, FULL1 + DIFF4 + log

c*d2008-10-27 07:10

10 楼

黑马,能详细解释一下吗?
这道题里什么要求在2000里不能实现?
是不是因为wed进行full backup后以前的diff备份都没用了
这个叫做破坏了backup chain?

【在 w*******e 的大作中提到】

: 酷比是oracle的, 估计不太懂SQL Server.
: 在SQL 2000里, 这个要求是不可能实现的, 所以LZ特意强调了SQL2005.

c*d2008-10-27 07:10

11 楼

呵呵,一下就被黑马给看出来了
我是做Oracle的
对SQL Server的东西都是把Oracle的类似知识套过来用
不过,下一个可能的offer是做SQL Server的
希望以后能oracle/ms双修

【在 w*******e 的大作中提到】

: 酷比是oracle的, 估计不太懂SQL Server.
: 在SQL 2000里, 这个要求是不可能实现的, 所以LZ特意强调了SQL2005.

c*d2008-10-27 07:10

12 楼

探讨一下

log
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~这时不能log backup
用.
~~~~~~~~~~~~~~~~~~~~~~~~~~~这个理解不对吧,应该是上一次log
backup
backup
zenny

【在 w*******e 的大作中提到】

: 是没问题.
: 不过, zenny出的这题有问题.
: 在backup database时再进行log backup, 就破坏了该log chain...因为这时进行的log
: backup是以它之前的full backup为基础的. 说笼统点是, 这时的log backup基本没用.
: Worst scenario是: 这时的log backup有点乱, 可能某transaction是之前full backup
: 的, 某transaction 是之后这个full backup的. ---- (not sure about this. zenny
: 说说?)
:
: a
: back

z*y2008-10-27 07:10

13 楼

呵呵，我们以后可以多做一些oracle 和 sql server 的比较。但是不要讨论哪一种
好，这样经常会起争端。