April 10, 2018 at 4:21 pm
Now, the DB is in SIMPLE recovery mode without any backup. The traditional way is,
1. Change it to FULL recovery mode
2. FULL Backup DB
3. Backup Log with norecovery
When DB is huge, it does take much time. Is there any other way by which we can quickly change its status to Restoring?
GASQL.com - Focus on Database and Cloud
April 10, 2018 at 5:38 pm
Alexander Zhang - Tuesday, April 10, 2018 4:21 PMNow, the DB is in SIMPLE recovery mode without any backup. The traditional way is,
1. Change it to FULL recovery mode
2. FULL Backup DB
3. Backup Log with norecoveryWhen DB is huge, it does take much time. Is there any other way by which we can quickly change its status to Restoring?
I'm sorry, but what exactly are you trying to accomplish?
April 11, 2018 at 5:02 am
Why on earth would you change a database in simple recovery to full in order to backup the log, which is only going to contain transactions that haven't yet truncated or completed? If they've completed, they'll truncate at the next checkpoint. If you're trying to somehow force the checkpoint, just issue the checkpoint command. I'm very confused by what you're trying to do here, along with Lynn.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 11, 2018 at 9:18 am
Alexander Zhang - Tuesday, April 10, 2018 4:21 PMNow, the DB is in SIMPLE recovery mode without any backup. The traditional way is,
1. Change it to FULL recovery mode
2. FULL Backup DB
3. Backup Log with norecoveryWhen DB is huge, it does take much time. Is there any other way by which we can quickly change its status to Restoring?
what is it you are trying to accomplish, the post title suggests something different
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2018 at 9:30 am
Lynn and Grant, Thanks a lot for your comments. I don't have anything big behind this question. Actually, I'm trying to dig deep on the different behaviors among diff DB status. So that, I would like to set my testdb(big in simple mode without any backup) to diff states quickly. You know, it's not hard to switch it to ONLINE, OFFLINE, SUSPECT, RECOVERING, RECOVERING PENDING or EMERGENCY. When I try to change it to Restoring, my steps mentioned above do work, but take long. I'm wondering if there is any other way to make it happen easily.
GASQL.com - Focus on Database and Cloud
April 11, 2018 at 10:08 am
Alexander Zhang - Wednesday, April 11, 2018 9:30 AMLynn and Grant, Thanks a lot for your comments. I don't have anything big behind this question. Actually, I'm trying to dig deep on the different behaviors among diff DB status. So that, I would like to set my testdb(big in simple mode without any backup) to diff states quickly. You know, it's not hard to switch it to ONLINE, OFFLINE, SUSPECT, RECOVERING, RECOVERING PENDING or EMERGENCY. When I try to change it to Restoring, my steps mentioned above do work, but take long. I'm wondering if there is any other way to make it happen easily.
Recovering assumes that the database is in a state capable of restoring additional log backups. You could leave a database in recovery for additional restores of differential backups, but you lose that once you take a full backup on the source database. So again, what are you trying to accomplish? I still don't have a good understanding here.
April 11, 2018 at 12:34 pm
Alexander Zhang - Wednesday, April 11, 2018 9:30 AMLynn and Grant, Thanks a lot for your comments. I don't have anything big behind this question. Actually, I'm trying to dig deep on the different behaviors among diff DB status. So that, I would like to set my testdb(big in simple mode without any backup) to diff states quickly. You know, it's not hard to switch it to ONLINE, OFFLINE, SUSPECT, RECOVERING, RECOVERING PENDING or EMERGENCY. When I try to change it to Restoring, my steps mentioned above do work, but take long. I'm wondering if there is any other way to make it happen easily.
The only reasons to do this are to either insure you have a tail-log backup prior to restoring over the database - or to insure no further operations are performed on that database after the final log backup has been taken (e.g. migration of database to new instance).
Since this can only be done as a tail-log backup - then the database must be in a recovery model that allows transaction log backups to be performed. The only option you have available is to switch to full or bulk recovery models, perform a full backup - then initiate the tail-log backup.
Note: if the database is in simple recovery model - then you are not concerned about losing a days worth of transactions, so changing to full - performing a new backup - then performing a tail-log backup is quite useless other than taking up resources and time. If you are concerned about updates - then put the database into single-user (or restricted), backup the database - then take the database offline.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 11, 2018 at 1:33 pm
Looks no shortcut. Thanks guys.
GASQL.com - Focus on Database and Cloud
April 11, 2018 at 1:36 pm
BTW, there is no specific purpose, but for some unreasonable and stupid curiosity:-)
GASQL.com - Focus on Database and Cloud
April 11, 2018 at 1:41 pm
Alexander Zhang - Wednesday, April 11, 2018 1:36 PMBTW, there is no specific purpose, but for some unreasonable and stupid curiosity:-)
Have fun.
Maybe go with smaller data sets to start with.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply