May 15, 2009 at 2:06 pm
Hi,
i need to implement log shipping sql 2005 to 30 databases.
i don't want to do the log shipping wizard for all 30 databases.
when i try to script out and run the log shipping wizard i get an error about converting varchar to unique identifier.
I've a script that backup all 30 database and restore them on the secondary server with standby,what is the working tsql to set it up and only change the database name?
THX
May 15, 2009 at 2:27 pm
I've always built my own log shipping mechanisms and they are easier to manage and more robust since they have more intelligence and robustness into the design than the stock log shipping supplied by MSFT. (Besides, AFAIK they are not going to support it in future releases of SQL Server.)
You can use a variable for the database name in the restore statement. like:
RESTORE DATABASE @dbName FROM DISK = @diskPath WITH NO RECOVERY
One caveat is, that if you need to use the MOVE option you'll have to use dynamic SQL and exec()
The probability of survival is inversely proportional to the angle of arrival.
May 15, 2009 at 2:46 pm
I do not have my old SQL Server 7.0 scripts at hand but check what Pop Rivett has to say http://www.simple-talk.com/sql/backup-and-recovery/pop-rivetts-sql-server-faq-no.4-pop-does-log-shipping/ 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 15, 2009 at 11:55 pm
thx for your replays.
but anyone knows how to make the ms log shipping work from the tsql and not from the wizard?
THX
May 16, 2009 at 11:03 am
set up log shipping through the wizard for just one of the databases. then use the 'script configuration' button for primary and secondary to create TSQL script for the setup and just alter it as necessary for each database.
This script will not include the initial restore of the database with norecovery so you will need to use dynamic SQL to create that. I strongly suggest you use the same directory paths on both servers so the complexity of the 'with move' clause does not come into it, and backup each database with the same suffix e.g. dbname_logship.bak
---------------------------------------------------------------------
May 16, 2009 at 12:11 pm
THX.
there was a problem with the export script on sql server 2005 sp2 log shipping.
on sp3 log shipping the error is disappear when you run the script on the secondary server.
October 15, 2015 at 10:17 am
sturner (5/15/2009)
I've always built my own log shipping mechanisms and they are easier to manage and more robust since they have more intelligence and robustness into the design than the stock log shipping supplied by MSFT. (Besides, AFAIK they are not going to support it in future releases of SQL Server.)You can use a variable for the database name in the restore statement. like:
RESTORE DATABASE @dbName FROM DISK = @diskPath WITH NO RECOVERY
One caveat is, that if you need to use the MOVE option you'll have to use dynamic SQL and exec()
Hi Sturner, can you share your scripts for logshipping ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply