October 26, 2015 at 4:46 am
Had a requirement to perform an activity on SQL Server where I bring down an application for an activity however SQL Server is up for backups.The activity starts once sql backups are completed andi restart SQL Server once activity is complete. I need to ensure that there were no changes made to the user databases during the time the backups were being taken. Is there any query there to check the same.
October 26, 2015 at 4:56 am
Are there any jobs that could make update/insert of data?
Do you need data compare, schema compare or both?
Igor Micev,My blog: www.igormicev.com
October 26, 2015 at 5:08 am
Thanks, the jobs would not be making changes as anticipated, however need to take into account any sort of data change.
October 26, 2015 at 6:03 am
vishalsendmail (10/26/2015)
Thanks, the jobs would not be making changes as anticipated, however need to take into account any sort of data change.
You'd better not make a data/schema comparison after and spend more time on ensuring that nothing could possibly do some changes into databases rather than running some queries or tools to find possible changes.
If it's that all that makes update/insert into the databases, than you can just run your schedule.
Igor Micev,My blog: www.igormicev.com
October 26, 2015 at 6:30 am
Why do you need to prevent changes during the backup? The backup will be transactionally complete, regardless of whether there are changes made during the backup, or not.
If you "prevent" changes to the database during the backup how do you ensure that a task, started BEFORE the backup, is not STILL running?
You could force single user, and terminate all connections, during the backup - but personally I think this is unnecessary (unless you have a special need 🙂 )
October 26, 2015 at 7:04 am
The requirement was that post the application being brought down, there should not be any changes to the databases by any other means like running a query through query anaylser. In case that is done , through should be a way to check that an insert/update/delete has been done.
October 26, 2015 at 7:14 am
Ah, OK. So for example a Month End routine is done in the APP and the backup needs to be at that exact moment in time.
This is a bit of a long shot but:
If you are using full recovery model? you could save the last full backup AND the log backups since then, including one log backup AFTER the cut-off time, and then if you ever need to restore you use STOP AT to restore to exactly the time that the APP was stopped.
Only other way I can think of is to alter the database and set it to, say, OFFLINE (actually it needs to be something that can be backed up ... SINGLE USER might do, but there is always a risk that some other process grabs the "one user connection".
But you still need to do this EXACTLY at the point that the APP is stopped, and I can't see how that is possible - there will always? be a window of opportunity for something else to squeeze in one-last-transaction just before you bolt the door!! surely?
October 26, 2015 at 7:45 am
The following operation will set database to RESTRICTED_USER mode, meaning that only SYSADMIN accounts can login. It also does a rollback and disconnect on any existing connections.
alter database <db_name> set restricted_user with rollback immediate;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply