August 16, 2008 at 7:57 pm
Hi Folks,
if I want to set the recovery model of a db from simple to full is it necessary to get everyone out of the db?
August 16, 2008 at 8:01 pm
No.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2008 at 8:02 pm
No, it is not necessary to get everyone out of the db. But, you need to make sure you have transaction log backups scheduled. How often you schedule them depends upon the business requirements for recovery.
You need to determine how much data loss is acceptable, then schedule the transaction log backups accordingly. For critical systems, I will schedule my transaction log backups every fifteen minutes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 16, 2008 at 8:03 pm
The recovery mode of a db can be changed at any time but when changing from simple, the new mode does not take real effect until after a full database backup is made.
SQL = Scarcely Qualifies as a Language
August 17, 2008 at 10:59 am
Carl is correct. You need to take a full backup immediately otherwise you are not really protecting your data.
August 17, 2008 at 11:42 pm
thanks guys - is there any issue setting the model back to simple again when it is in full recovery model?
August 18, 2008 at 1:30 am
Not apart from losing the ability to recover from transaction logs. If you are going to be flip-floppingfrom full to simple and back again you may be making your recovery more copmplex than it needs to be. What are you trying to achieve?
Mike John
August 18, 2008 at 4:55 pm
Hope you don't find my explanation too dry...
I am transfering a 2000 db to 2005. I haven't been given a test environment to work with. I am migrating by restore and backup and the migration need to be done late in the night to minimize user disruption. So on the day of migration I was going to do a full backup and restore during the day to do the bulk of the transfer and later in the night I was going to do a log backup and restore to move the transactions during the day (for my own conveniency). The current database at the moment use simple recovery model. I was wanting to do a test run so I need to set the current db to full recovery model but I don't want to have to keep an eye on the transaction logs for the old db so when I am finished testing I want to set the recovery model back to simple.
August 18, 2008 at 6:58 pm
bodhilove (8/18/2008)
Hope you don't find my explanation too dry...I am transfering a 2000 db to 2005. I haven't been given a test environment to work with. I am migrating by restore and backup and the migration need to be done late in the night to minimize user disruption. So on the day of migration I was going to do a full backup and restore during the day to do the bulk of the transfer and later in the night I was going to do a log backup and restore to move the transactions during the day (for my own conveniency). The current database at the moment use simple recovery model. I was wanting to do a test run so I need to set the current db to full recovery model but I don't want to have to keep an eye on the transaction logs for the old db so when I am finished testing I want to set the recovery model back to simple.
Sounds logical to me...:)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 11:36 pm
cool.. Just a couple of questions about the migration
should I only have to transfer the logins once using this method because once the sids are in sync If I restore the same databases down later on.. that won't cause me problems?
Has anyone got any ideas on how to best verify the migration was sucessful. I am guessing I am going have to query system table on both ends (systables, sysdatabases, syslogins, sysusers) to see they have the same amount of rows for the user data?
August 19, 2008 at 5:40 am
bodhilove (8/18/2008)
Hope you don't find my explanation too dry...I am transfering a 2000 db to 2005. I haven't been given a test environment to work with. I am migrating by restore and backup and the migration need to be done late in the night to minimize user disruption. So on the day of migration I was going to do a full backup and restore during the day to do the bulk of the transfer and later in the night I was going to do a log backup and restore to move the transactions during the day (for my own conveniency). The current database at the moment use simple recovery model. I was wanting to do a test run so I need to set the current db to full recovery model but I don't want to have to keep an eye on the transaction logs for the old db so when I am finished testing I want to set the recovery model back to simple.
i prefer detach, copy and re attach of the databases, you'll probably find it quicker to. Apply the migrated logins after the databases are restored\attached otherwise the script may error as the users default database wont exist
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2008 at 10:50 am
Whenever you restore a database to a different instance than it was originally backed up from, you will need to reassociate the logins to the database users. I use the query below to generate a bunch of SP_CHANGE_USERS_LOGIN commands that will clean up the discrepancy between logins and users:
[font="Courier New"]/* create fix statements */
select 'exec sp_change_users_login ''Auto_Fix'', ''' + name + ''''
from sys.database_principals
where type in ('S', 'U')
and name not in ('guest','INFORMATION_SCHEMA','sys','dbo')
order by name[/font]
August 19, 2008 at 5:23 pm
ok charshman, but what if I have created the logins and run your sync script and then I want to restore the database again from the same source db. I won't have to resync the logins will I?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply