Post reply

How to Setup Log Shipping for Multiple Databases

  • September 28, 2011 at 6:38 pm

    #1388149

    how does this impact on the "Transaction Log Shipping" report?

    do you still use this report to check if all db's are in sync?

  • September 29, 2011 at 11:55 am

    #1388555

    John Sargent-410636,

    The report does not appear to be impacted by this. I'm a little weary of some of the "time since" columns that appear to repeat the same value but the rest are pretty sound.

    Thanks for the question!

  • September 29, 2011 at 2:09 pm

    #1388627

    Great article! This is defintely a more managable solution compared to 130 Agent jobs, but one caveat to be careful with is serial processing. Now that your process is doing one backup at a time it leaves your other database more susceptable to data loss. Should you lose a drive or the SQL instance the databases that havent been backed up, may not be as current as you like because they never got a turn to go.. which wouldnt happen in a situation where you have multiple jobs.

    If I were you I would consider creating a lookup table with a grouping and a priority number. With this type of setup you could create multiple backup jobs that grab different databases based on their grouping. You could then order the backups by priority.


    My blog: http://jahaines.blogspot.com

  • September 29, 2011 at 2:32 pm

    #1388634

    Adam,

    Very interesting idea for mitigating data loss.

    Thanks

  • November 3, 2011 at 10:07 am

    #1402745

    Thanks! I just implemented this solution.

    I think one query is missing: If the primary server breaks and all databases on the secondary server must become active, how do you recover them fast?

    I have one solution, involving two queries. First run this:

    SELECT N'RESTORE DATABASE [' + name + N'] WITH RECOVERY'

    FROM [sys].[databases]

    WHERE

    (DATABASEPROPERTY(name, N'IsInRecovery') = 1) OR

    (DATABASEPROPERTY(name, N'IsInStandBy') = 1)

    This query creates a new query of all standby and recovering databases, run the generated query to get them recovered.

  • November 3, 2011 at 10:28 am

    #1402773

    Thanks zdvsoftware. This is a very efficient way to recover the databases. Since you've taken us down this road what do you think about added the script to disable the copy and restore jobs at the same time? I have that below.

    DECLARE @cpy uniqueidentifier,@restore uniqueidentifier;

    SELECT @cpy = job_id FROM msdb.dbo.sysjobs WHERE name = 'LSCopy_ALL_DBs';

    SELECT @restore = job_id FROM msdb.dbo.sysjobs WHERE name = 'LSRestore_ALL_DBs';

    EXEC msdb.dbo.sp_update_job @job_id = @cpy, @enabled = 0;

    EXEC msdb.dbo.sp_update_job @job_id = @restore, @enabled = 0;

  • November 7, 2011 at 1:46 am

    #1403805

    Brian,

    When I started on this I searched the internet for a script that recovered all recovering databases (not like mine, which is a script that creates a script recovering all databases) and found... nothing. So I provoked my answer to see if I missed something.

    But now I realise something else. Maybe you want and you should investigate everything first. I'm talking about going into log files etc. So you need to be in SSMS checking the log files anyway, so you can disable the jobs too.

    But my script is probably OK, as it generates a script. The DBA can go over this and remove the lines for databases that should not be brought back. After that the remains can be executed. One improvement for my script could be sorting on database name.

    But hey, I'm new to this DBA game, originally I'm a developer.

  • November 7, 2011 at 12:28 pm

    #1404140

    zdvsoftware,

    Here is how you could go about having your script actually execute instead of just generating a script.

    -- RECOVER ALL DATABASES

    DECLARE @sql varchar(MAX)

    SELECT @sql = COALESCE(@SQL + '

    ','') + N'RESTORE DATABASE [' + name + N'] WITH RECOVERY'

    FROM [sys].[databases] d

    INNER JOIN [msdb].[dbo].[log_shipping_secondary_databases] l ON d.name = l.secondary_database

    WHERE

    d.is_in_standby = 1;

    EXEC(@SQL);

  • November 7, 2011 at 5:28 pm

    #1404226

    How or could this solution work on Log Shipping 1500dbs when the 2nd server is NOT on the domain? I have most of this solution working but how could I get the create new dbs to be implemented on the 2ndary server? Thanks.

    PS. This is really a grand solution and I really appreciate putting this out there.

    Reggie

  • November 8, 2011 at 8:24 am

    #1404591

    Thanks Brian for putting this together.

    One issue I'm running into is we are in a state of transition were we are migrating one of primary applications from another platform to SQL Server.

    As such, we have periodic database conversions that in effect replace the running databases on the primary server. So given that scenario, when it's time to bring in a new converted database, the simplest solution is to turn off log shipping, bring the newly converted database online, and then re-enable log shipping.

    The caveat is that when I turn off log shipping, the backup/copy/restore jobs are deleted. Do you have any solution/suggstions (other than scripts to recreate the jobs) whenever I need to do this.

    While I could leave log shipping off during this transition period, management is insistent that we have this in place during this time.

    Thanks in advance.

    Don

  • November 8, 2011 at 8:28 am

    #1404596

    reggie burns-317942,

    Wow 1500 DBs. That's impressive.

    I don't have any experience with working across domains so maybe someone else out there can clarify this but I don't see that you should have any issues if you are able to setup a linked server and you have a physical location that both the primary and secondary servers can access.

    In my 4th script I am restoring the backup made by the primary to the secondary using a linked server.

    If you are unable to do this you could also just run a DOS command on your backup folder to get the list of backup files (S:\SQLBackups\> DIR/b *.bak > backupList.txt).

    Let me know if I'm missing what you're going after.

    Thanks for posting.

  • November 8, 2011 at 8:43 am

    #1404614

    dhodgeh,

    If you could avoid blowing away the database and only change the objects within it then you wouldn't need to rebuild log shipping.

    I suppose you could also create a job that scans for DBs that are not participating in log shipping and add them. You would still be using scripts but it would be a hands off operation.

    Thanks

  • December 7, 2011 at 9:12 am

    #1418199

    How could you use scripts to get the missing db from the primary instance back it up copy that backup from the primary instance and then apply log shipping to that missing db?

    Sounds simple and straight forward but am is missing something here?

  • December 7, 2011 at 9:39 am

    #1418222

    Hi reggie burns-317942,

    I'm not sure what your looking for. I believe this article http://www.sqlservercentral.com/articles/Log+Shipping/75042/ describes the process of setting up log shipping including restoring the database to the secondary instance.

    In order to setup log shipping and use my scripts your going to need a common physical location that both the primary and secondary instances have access to. You'll also need a linked server to make restoring the databases to the secondary instance easier.

    Let me know what I'm missing.

    Thanks

  • December 7, 2011 at 10:59 am

    #1418293

    The scripts and process you have presented are great. I've used them and they are working all the way up to the process of "Create Database From Latest Backup on Production"

    1. I have the linked server established

    2. I have a mapped drive from the production backups to the secondary

    But when I execute that script I get this error

    "Msg 3044, Level 16, State 2, Line 23

    Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.

    Msg 3013, Level 16, State 1, Line 23

    RESTORE DATABASE is terminating abnormally."

    Would you know where my issue is?

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply