y****9
发帖数: 144 | 1 New to SQL server ( just try to learn it as an Oracle DBA), did a test about
recovering a db, can someone tell me how to do a complete recovery in the
following case:
1. big_table reference point:
1> select max(id) from big_table;
2> go
-----------
2000000
2. Backup database testdata
3. insert several records into table big_table in the testdata database
1> select max(id) from big_table;
2> go
-----------
2000003
4. Shutdown the instance
5. delete the data file: c:\Microsoft SQL server\...\testdata.mdf
6. Startup the instance
7. Try to restore/recover database testdata
In SSMS: right-click 'testdata', 'task' the restore or backup options
are grayed out ( in one case, I can proceed)
right-click another database, choose task, then restore ...
with default options, receiving the following massage:
{{{
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'TUSNC012LKVT006'. (Microsoft.SqlServer.
SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The tail of the log for the database "
testdata" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup
the log if it contains work you do not want to lose. Use the WITH REPLACE or
WITH STOPAT clause of the RESTORE statement to just overwrite the contents
of the log. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
}}}
8. choose: Overwrite the existing database (WITH REPLACE)
9. Database restored, but the changes made in step 3 seems lost.
$ sqlcmd
1> use testdata
2> select max(id) from big_table;
3> go
Changed database context to 'testdata'.
-----------
2000000
(1 rows affected)
Question: how can I recover the database that includes all the changes in
step 3?
Thanks! | B*****g
发帖数: 34098 | 2 In 8 what did you choose for revcovery state?
about
the
【在 y****9 的大作中提到】
: New to SQL server ( just try to learn it as an Oracle DBA), did a test about
: recovering a db, can someone tell me how to do a complete recovery in the
: following case:
: 1. big_table reference point:
: 1> select max(id) from big_table;
: 2> go
: -----------
: 2000000
: 2. Backup database testdata
: 3. insert several records into table big_table in the testdata database
| y****9
发帖数: 144 | 3
==> I did not choose, so the default is:
"Leave the database ready to use by rolling back uncommitted transactions.
Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)."
I asked the question to a fellow SQL Server DBA in my work. He said in this
case we cannot do complete recovery without do transaction log backup after
step 3 and before step 4 , I can hardly believe from Oracle perspective. So
I ask this question just want to make sure.
【在 B*****g 的大作中提到】
: In 8 what did you choose for revcovery state?
:
: about
: the
| B*****g
发帖数: 34098 | 4 yes, no transaction log backup you can not recover this. Your company start
to use sql server?
this
after
So
【在 y****9 的大作中提到】
:
: ==> I did not choose, so the default is:
: "Leave the database ready to use by rolling back uncommitted transactions.
: Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)."
: I asked the question to a fellow SQL Server DBA in my work. He said in this
: case we cannot do complete recovery without do transaction log backup after
: step 3 and before step 4 , I can hardly believe from Oracle perspective. So
: I ask this question just want to make sure.
| g***l
发帖数: 18555 | 5 必须做TRANSACTION LOG BACKUP，你没有BACKUP怎么可能恢复呢？ | y****9
发帖数: 144 | 6 In my case, once the instance up, it can not find the data file , but the
log file is still there. I failed to find a way to backup active transaction
log (log tail), that's why I can not do complete recovery?.-- IMO, this is
really a limitation of SQL Server as compared to Oracle, in such case, it
is perfectly doable in Oracle.
In my test I have to shutdown the instance to delete the datafile to
simulate a disk failure, In reality, if a disk just failed, can we do
complete recovery ( i.e. the info in the trascation logs that have not been
backed up should preserve after recovery), I guess it should be able to,
otherwise too bad.
If the active portion of the transaction log will be lost after disk failure
, how can we choose SQL Server as production db ?
Again, new to SQL server, maybe I fail to understand some key points.
【在 g***l 的大作中提到】
: 必须做TRANSACTION LOG BACKUP，你没有BACKUP怎么可能恢复呢？
| y****9
发帖数: 144 | 7 I think I got the soluton, based on the book online:
"
If the database is offline and does not start.
Try to take a tail-log backup. Because no transactions can occur at this
time, using WITH NORECOVERY is optional. If the database is damaged, use
either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.
BACKUP LOG database_name TO [WITH { CONTINUE_AFTER_ERROR |
NO_TRUNCATE }
Important:
We recommend that you avoid using NO_TRUNCATE, except when the database is
damaged.
If the database is damaged, for example, if the database does not start, a
tail-log backup succeeds only if the log files are undamaged, the database
is in a state that supports tail-log backups, and the database does not
contain any bulk-logged changes.
"
I did the following test that proves it works:
1. after restore from a baseline full backup, we have id=200004
1> use testdata
2> select max(id) from big_table;
3> go
Changed database context to 'testdata'.
-----------
2000004
2. insert
1> insert into big_table values(2000006, 1,1, 'x','y');
2> go
(1 rows affected)
1> exit
3. take the database testdata offline
4. delete the data file: c:\Microsoft SQL server\...\testdata.mdf
5. bring the database testdata online ( In SSMS it appears offline though)
6. backup the tail-log with no truncate option
1> BACKUP LOG testdata TO LOCAL_DISK_TESTDATA WITH NO_TRUNCATE ;
2> go
Processed 4 pages for database 'testdata', file 'testdata_log' on file 16.
BACKUP LOG successfully processed 4 pages in 0.106 seconds (0.230 MB/sec).
7. Restore the db ( select the full backup same as in step 1 and the log
backup created in 6)
8. verify
1> use testdata
2> select max(id) from big_table;
3> go
Changed database context to 'testdata'.
-----------
2000006
(1 rows affected)
So the key is to be able to backup tail-log before restore. Now I have some
confidence on SQL Server :-) | g***l
发帖数: 18555 | 8 你自己不BACKUP TRANSACTION LOG，还嫌SQL SERVER没有CONFIDENCE，你的FULL
BACKUP是哪年做的还不知道，去APPLY TAIL LOG BACKUP。人家说的是有FULL+经常做
TRANSCATION LOG BACKUP，最后一点LOG没有BACKU的时候，才TAIL LOG BACKUP一下，
你从来都不做TRANSCATION LOG BACKUP，DATAFILE没了靠LOG FILE去恢复，这个比较悬。
如果真的很在意DATA COMPLETION，就应该做好LOG BACKUP，15分钟一次，甚至5分钟一次。
再不放心就要做MIRRORING或者LOG SHIPPING。 | y****9
发帖数: 144 | 9 you totally missed my point. My purpose is to find out how to do complete
recovery in SQL server. Without proper backup tail-log before restore/
recover, no matter how often you do transaction log backup, you can not do a
COMPLETE recovery. Isn't that true?
In Oracle, when we do complete recovery, we don't need the extra step of
backup the active log ( i.e. online redo log in Oracle).
悬。
一次。
【在 g***l 的大作中提到】
: 你自己不BACKUP TRANSACTION LOG，还嫌SQL SERVER没有CONFIDENCE，你的FULL
: BACKUP是哪年做的还不知道，去APPLY TAIL LOG BACKUP。人家说的是有FULL+经常做
: TRANSCATION LOG BACKUP，最后一点LOG没有BACKU的时候，才TAIL LOG BACKUP一下，
: 你从来都不做TRANSCATION LOG BACKUP，DATAFILE没了靠LOG FILE去恢复，这个比较悬。
: 如果真的很在意DATA COMPLETION，就应该做好LOG BACKUP，15分钟一次，甚至5分钟一次。
: 再不放心就要做MIRRORING或者LOG SHIPPING。
| g***l
发帖数: 18555 | 10 ORACLE当然牛啊，不知道有多少人用过ORACLE RESTORE了FULLBACKUP,online redo log自己就APPLY的，讲讲经验。
a
【在 y****9 的大作中提到】
: you totally missed my point. My purpose is to find out how to do complete
: recovery in SQL server. Without proper backup tail-log before restore/
: recover, no matter how often you do transaction log backup, you can not do a
: COMPLETE recovery. Isn't that true?
: In Oracle, when we do complete recovery, we don't need the extra step of
: backup the active log ( i.e. online redo log in Oracle).
:
: 悬。
: 一次。
| k********e
发帖数: 702 | 11 残余的 log file 也是可以 replay的，不一定要backup log
虽然SQL默认是rollback
不多说了，你懂的 | y****9
发帖数: 144 | 12 @Kissbigeye -
Thank you for your comments. I do believe to solve a problem there could be
more than one solutions.I searched the book online and found something that
may support your statement
http://technet.microsoft.com/en-us/library/ms187495.aspx
The first step of performing a complete restore is described as:
{{{
Typically, recovering a database to the point of failure involves the
following basic steps:
1.Back up the active transaction log (known as the tail of the log). This
creates a tail-log backup. If the active transaction log is unavailable, all
transactions in that part of the log are lost.
Important
Under the bulk-logged recovery model, backing up any log that contains bulk-
logged operations requires access to all data files in the database. If the
data files cannot be accessed, the transaction log cannot be backed up. In
that case, you have to manually redo all changes that were made since the
most recent log backup.
}}}
In the Important section - it mentioned that "you can manully redo all
changes" in case that the transaction log cannot be backup (but available).
I would appreciate if you can give some examples or test cases or cite some
well-known sources about the steps to manually apply the changes in the log.
Thanks,
Denis
【在 k********e 的大作中提到】
: 残余的 log file 也是可以 replay的，不一定要backup log
: 虽然SQL默认是rollback
: 不多说了，你懂的
| B*****g
发帖数: 34098 | 13 nod。Kissbigeye 现在应该是有经验的了，下个月开个讲座吧。
be
that
【在 y****9 的大作中提到】
: @Kissbigeye -
: Thank you for your comments. I do believe to solve a problem there could be
: more than one solutions.I searched the book online and found something that
: may support your statement
: http://technet.microsoft.com/en-us/library/ms187495.aspx
: The first step of performing a complete restore is described as:
: {{{
: Typically, recovering a database to the point of failure involves the
: following basic steps:
: 1.Back up the active transaction log (known as the tail of the log). This
|
|
