April 7, 2006 at 5:35 am
Hi
I have a database that can not be replicated using Transaction or Merge Replication due to having multiple primary keys on some tables. I can't use logshipping as the servers are SQL 2000 Standard edition, and I need Enterprise edition to do logshipping.
I want to backup the database > Compress the DB > Transfer it to standbay server > Uncompress the DB > Restore DB.
Similar for the Transaction log but no compression needed.
Now here's the catch I need to Run this and invoke the jobs on the remote server from the primary as I have no way of knowing when the DB copy has completed?
Can Anyone help?
Cheers
TryingToBeDBA
April 7, 2006 at 6:22 am
We currently have a process that does something similar. We use cmdshell(in a stored proc) to zip and copy as one step in a job when that step completes it kicks of a job(sp_start_job) on the remote server that performs the restore.
April 7, 2006 at 8:10 am
Thanks but can you give me examples of these sp's and jobs so I could see how its done?
much appreaciated in advance
April 12, 2006 at 3:30 am
Simplified version
--====================================================
--First SQLTask using Source Connection
--Backup DB process-----
Backup Database {DBName}
To File = {BckupFile}
Declare @Cmd Varchar(2000)
Select @Cmd = 'Zip DBZipFile BckupFile'
Exec master..xp_cmdShell @Cmd
Select @Cmd = 'Copy DBzipFile DestZipFile'
Exec master..xp_cmdShell @Cmd
--===========================================================
The other task uses the Destination Connection and does the reverse process.
April 12, 2006 at 8:42 am
How did you create multilple primary keys on a table? I did not think this was possible.
Tom
April 12, 2006 at 8:51 am
You cannot have multiple primary keys on a table. You can have multiple indexes, even unique indexes, but only one primary key.
They could be compound keys.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply