Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 SQLCMD Mode and Log Shipping

Author  Topic 

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-05-19 : 14:48:10
I'm trying to create a log shipping procedure to simplify (hopefully automate) the role change operation for SQL Server 2005 Standard. I'm at a point where I need to pass configuration information between the primary and secondary servers. Here's a snippet:
:setvar serverPri "serverA"
:setvar serverSec "serverB"
:setvar DBPri "myDB"
:setvar folderBak "\\serverC\backup\"

:connect $(serverPri)

declare @backupfolder varchar(128)

select @backupfolder=pd.backup_share
from msdb.dbo.log_shipping_primary_secondaries ps
inner join msdb.dbo.log_shipping_primary_databases pd on ps.primary_id=pd.primary_id
where ps.secondary_server='$(serverSec)' and pd.primary_database='$(DBPri)'

print @backupfolder -- this works like I want

:setvar folderBak "@backupfolder" -- this doesn't work like I want
GO

:connect $(serverSec)
print @backupfolder --this errors out because it's a different server
My question is: Is there a way to pass data from SQL Server into a SQLCMD variable that can persist across connections? Some options I've tried or considered are:

- Query the log shipping tables via linked servers: REJECTED due to security issues (credit card processing, SAS70, PCI, etc.)
- Write data to file share: REJECTED also for security reasons (xp_cmdshell disabled, UNC access restricted, SAS/PCI)
- Existing script on the internet that does this already: LOOKED and couldn't find one for SQL Server 2005
- Other option I haven't thought of: HOPEFUL that someone can provide an alternative

We are going to use database mirroring but also need log shipping as an additional failover option for multiple locations. We would need to have a script to set up log shipping on the mirror in case we fail over to it. Service Broker is an option but will require full automation of log shipping config at failover and failback.

Any help or advice is appreciated. Thanks.
   

- Advertisement -