April 30, 2013 at 1:29 pm
Currently we have a rather elaborate backup job we run nightly on a very large reporting server.
We do our backups and for the databases we aren't backing up (due to them being replicated from a live server) we run a T-SQL CHECKPOINT on them. All in all, with every every step, this is over 157 steps. And when we add a new database, it's quite elaborate adding it in since we try to keep things alphabetized.
I've been building a SSIS package so all I have to do is fix 1 line of code to add a new database. This part works great! I won't go into detail with this other then it works very well.
I want to add the next step to this which is a loop that will read t-sql code and get a list of replicated databases that we run CHECKPOINT on. Now to be clear...this is NOT the checkpoint package within SSIS. This is the t-sql code "CHECKPOINT".
I've added a "Execute SQL Task" that is within a Sequence container, which is housed within a ForEach Loop Container, that reads from a "Execute SQL Task". The looping works fine. I have the t-sql CHECKPOINT within my "Execute SQL Task" but I'm not 100% sure if this working right.
So this leads me to several questions:
1. Will this work?
2. Is there a way to check to see if my CHECKPOINT ran on my list of databases?
3. If this isn't the right way, how would I do it?
I hope this makes sense for everyone.
Thank you for your time.
April 30, 2013 at 1:50 pm
John,
What's the purpose of the T-SQL CHECKPOINT, what are you trying to accomplish.
Also, not because a database is being replicated, can't be backed up. Actually, I have few databases that are involved in replication and they do have backups as well. I guess that I am not following you ... what could be the reason for not running backup on those, space maybe?
April 30, 2013 at 2:30 pm
We backup the databases on the production server. No need to backup the database on the reporting server (the replicated server). Would be rather redundant.
Also, the T-SQL Checkpoint is done to write all the dirty pages to disk. We do that just to "clear things out" of the databases we aren't backing up.
I'd like to do that in my SSIS package. I know that's somewhat vague but this is what we've been doing for quite sometime but I'd like to simplify the process by using SSIS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply