September 12, 2008 at 3:30 pm
I have a SSIS database backup package that I am planning to install on our "common" SQL 2005 instance. This instance contains all the security, maintenance, and common code elements for our enterprise. All of our other SQL instances have a linked server to this common instance, so that they all have access to it.
What this package does is backup all our other application instances from this common instance- reading the instance properties from a table within the common instance (which instances to backup, which ones will get a log backup, which ones will we backup the system dbs on, etc.).
For my log backups, I have a sql script template file that I am executing on each instance to backup (The backup database task in SSIS does not work for doing log backups on multiple databases- so that was not an option). This template is simply the actual procedure that will execute the BACKUP LOG for each database on that instance that has been set to backup. I create this procedure on each instance to be backed up, then execute it, then drop it. This works very well in most cases except when I am backing up the common instance itself... Here's the offending code:
DECLARE Instance_DBs CURSOR LOCAL FAST_FORWARD
FOR SELECT
name
FROM
sys.databases s
WHERE
recovery_model_desc = 'FULL'
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND NOT EXISTS ( SELECT
1
FROM
Common.Security.dbo.Backup_Instance i
JOIN
Common.Security.dbo._Backup_Log_Exclusions e ON i.instance_id = e.instance_id
WHERE
i.name = SERVERPROPERTY('InstanceName')
AND e.Database_Name = s.Name )
When I attempt to copy this procedure to the common instance (to back it up as well), it fails because we do not have a linked server to the common instance on the common instance. I know what the problem is, I just don't know if SQL has an elegant way to handle this. I tried to do an IF block based on the SERVERPROPERTIES('InstanceName') , and have two versions of the procedure in the script file, but SQL did not like this:
IF SERVERPROPERTIES('InstanceName') = 'COMMON'
BEGIN
BUILD PROCEDURE usp_tmp_Backup_Logs
{version without qualifying the instance name}
ELSE
BUILD PROCEDURE usp_tmp_Backup_Logs
{version that qualifies the instance name}
END
Does anyone have any ideas for me? Maybe 2005 introduces an elegant way to handle this. I would prefer not to maintain two versions of the script file, and definitely know that creating a linked server to common from the common instance itself will not fly.
Any constructive ideas are appreciated, thanks.
September 16, 2008 at 7:47 pm
Well... I guess I'm on my own with this one... Luckily, I did come up with a solution.. I simply added a script step to take the template and load it into a string variable. Then, depending on the instance name, either remove the linked server qualifier, or leave it as-is. I then write it back out to a 2nd file which I then execute to create the worker procedure.
Once I complete the backup for each instance, I drop the worker procedure.
Just thought someone might find this useful.
September 16, 2008 at 9:04 pm
Another option (possibly) would be to use synonyms in each database. Part of your script would be a check for the synonym, and if it does not exist - create it. If you are running on your common system, omit the linked server reference when creating the synonym.
Something like this:
IF OBJECT_ID('yourschema.synonym') IS NOT NULL
BEGIN;
CREATE SYNONYM yourschema.synonym FOR linkedserver.database.dbo.object;
END;
You can add a check into the above for which server you are on - and, if that server is your common server create the synonyms without the reference to the linked server.
In your procedure, reference the synonym instead.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply