Automate LogSHipping for multiple databases

Question

Automate LogSHipping for multiple databases

Viewing 15 posts - 16 through 29 (of 29 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

desiboy102493 - Monday, January 22, 2018 8:44 AM
Sue_H - Monday, January 22, 2018 8:29 AM
desiboy102493 - Monday, January 22, 2018 8:17 AM
Sorry for the late response.
when I query the system tables the group id is always 0 for log files
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid <>0 and SF.dbid = @dbid
open cDBFiles
So it would be just 0 for the group id?
Yes. You should look at modifying that script to use sys.master_files since it is the replacement for sys.sysaltfiles.
Sue
Getting this error saying invalid object LSDBlist, however, i created the table already on the MSDB
Thanks

When you get an error like that, it generally means you are not in the correct database or you need to fully qualify the object.

Sue

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 2

Sue_H - Monday, January 22, 2018 11:13 AM
desiboy102493 - Monday, January 22, 2018 8:44 AM
Sue_H - Monday, January 22, 2018 8:29 AM
desiboy102493 - Monday, January 22, 2018 8:17 AM
Sorry for the late response.
when I query the system tables the group id is always 0 for log files
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid <>0 and SF.dbid = @dbid
open cDBFiles
So it would be just 0 for the group id?
Yes. You should look at modifying that script to use sys.master_files since it is the replacement for sys.sysaltfiles.
Sue
Getting this error saying invalid object LSDBlist, however, i created the table already on the MSDB
Thanks
When you get an error like that, it generally means you are not in the correct database or you need to fully qualify the object.
Sue

I am on the MSDB db that's where I created the table. Not sure what you mean by qualify the object
Thanks.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

desiboy102493 - Monday, January 22, 2018 12:10 PM
Sue_H - Monday, January 22, 2018 11:13 AM
desiboy102493 - Monday, January 22, 2018 8:44 AM
Sue_H - Monday, January 22, 2018 8:29 AM
desiboy102493 - Monday, January 22, 2018 8:17 AM
Sorry for the late response.
when I query the system tables the group id is always 0 for log files
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid <>0 and SF.dbid = @dbid
open cDBFiles
So it would be just 0 for the group id?
Yes. You should look at modifying that script to use sys.master_files since it is the replacement for sys.sysaltfiles.
Sue
Getting this error saying invalid object LSDBlist, however, i created the table already on the MSDB
Thanks
When you get an error like that, it generally means you are not in the correct database or you need to fully qualify the object.
Sue
I am on the MSDB db that's where I created the table. Not sure what you mean by qualify the object
Thanks.

Then it does not exist if you are in the correct database and on the correct server where the table was created.
Please note that the table needs to be created on both the primary and secondary.
Make sure you are on the correct server.

Sue

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 4

Sue_H - Monday, January 22, 2018 12:26 PM
desiboy102493 - Monday, January 22, 2018 12:10 PM
Sue_H - Monday, January 22, 2018 11:13 AM
desiboy102493 - Monday, January 22, 2018 8:44 AM
Sue_H - Monday, January 22, 2018 8:29 AM
desiboy102493 - Monday, January 22, 2018 8:17 AM
Sorry for the late response.
when I query the system tables the group id is always 0 for log files
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid <>0 and SF.dbid = @dbid
open cDBFiles
So it would be just 0 for the group id?
Yes. You should look at modifying that script to use sys.master_files since it is the replacement for sys.sysaltfiles.
Sue
Getting this error saying invalid object LSDBlist, however, i created the table already on the MSDB
Thanks
When you get an error like that, it generally means you are not in the correct database or you need to fully qualify the object.
Sue
I am on the MSDB db that's where I created the table. Not sure what you mean by qualify the object
Thanks.
Then it does not exist if you are in the correct database and on the correct server where the table was created.
Please note that the table needs to be created on both the primary and secondary.
Make sure you are on the correct server.
Sue

The table is there
on both servers. Not sure what I am missing.
Thanks.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

desiboy102493 - Monday, January 22, 2018 1:11 PM
The table is there
on both servers. Not sure what I am missing.
Thanks.

A screenshot doesn't mean anything - you could have however many servers in SSMS so it doesn't mean you are on the correct server. And it doesn't mean you are in the correct database. If the table is there, it's your query. You need to be in the correct server and in the correct database.
If you run this exactly then and you get Invalid Object name, check what server you are connected to. It's not the same as your screenshot:
USE msdb go SELECT * FROM dbo.LSDBList

Sue

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 6

Sue_H - Monday, January 22, 2018 1:21 PM
desiboy102493 - Monday, January 22, 2018 1:11 PM
The table is there
on both servers. Not sure what I am missing.
Thanks.
A screenshot doesn't mean anything - you could have however many servers in SSMS so it doesn't mean you are on the correct server. And it doesn't mean you are in the correct database. If the table is there, it's your query. You need to be in the correct server and in the correct database.
If you run this exactly then and you get Invalid Object name, check what server you are connected to. It's not the same as your screenshot:
USE msdb go SELECT * FROM dbo.LSDBList
Sue

When I run this, i get the following

USE msdb 
go
SELECT * FROM dbo.LSDBList

Sue_H SSC Guru Points: 90860 More actions · Answer 7

desiboy102493 - Monday, January 22, 2018 1:28 PM
Sue_H - Monday, January 22, 2018 1:21 PM
desiboy102493 - Monday, January 22, 2018 1:11 PM
The table is there
on both servers. Not sure what I am missing.
Thanks.
A screenshot doesn't mean anything - you could have however many servers in SSMS so it doesn't mean you are on the correct server. And it doesn't mean you are in the correct database. If the table is there, it's your query. You need to be in the correct server and in the correct database.
If you run this exactly then and you get Invalid Object name, check what server you are connected to. It's not the same as your screenshot:
USE msdb go SELECT * FROM dbo.LSDBList
Sue
When I run this, i get the following
USE msdb 
go
SELECT * FROM dbo.LSDBList

Then you were NOT running that other query in msdb.

Sue

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 8

Sue_H - Monday, January 22, 2018 1:32 PM
desiboy102493 - Monday, January 22, 2018 1:28 PM
Sue_H - Monday, January 22, 2018 1:21 PM
desiboy102493 - Monday, January 22, 2018 1:11 PM
The table is there
on both servers. Not sure what I am missing.
Thanks.
A screenshot doesn't mean anything - you could have however many servers in SSMS so it doesn't mean you are on the correct server. And it doesn't mean you are in the correct database. If the table is there, it's your query. You need to be in the correct server and in the correct database.
If you run this exactly then and you get Invalid Object name, check what server you are connected to. It's not the same as your screenshot:
USE msdb go SELECT * FROM dbo.LSDBList
Sue
When I run this, i get the following
USE msdb 
go
SELECT * FROM dbo.LSDBList
Then you were NOT running that other query in msdb.
Sue

u mean this restore script?

Sue_H SSC Guru Points: 90860 More actions · Answer 9

desiboy102493 - Monday, January 22, 2018 1:59 PM
u mean this restore script?

You should go figure it out from the error you reported in your post here:
Post

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 10

Sue_H - Monday, January 22, 2018 2:21 PM
desiboy102493 - Monday, January 22, 2018 1:59 PM
u mean this restore script?
You should go figure it out from the error you reported in your post here:
Post

hmm...it was run under msdb . Have it ob both server (primary and secondary)
and running the restore command on the primary. The error still showing as invalid object 🙁

Thanks

barsuk SSChampion Points: 12280 More actions · Answer 11

At my previous job I created a process to logship over 300 prod databases to DR. I don't have scripts handy as it has been a while back. But here are few points.
We were running log dumps every 15 mins. Every 30 mins we kick a job(s) which run on each prod Server(roughly about 20-30 Databases of various sizes on each of the prod Server), which goes thru each tran log dump folder in Prod and copy latest log files to DR, logging each log copy to the table. Few times a day we were restoring logs on about 30 of DR servers by going thru the table( that table was located on DBA monitoring server and was accessible by both Prod and DR Servers), checking what was copied and restoring logs in the proper order....

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 12

barsuk - Sunday, February 4, 2018 2:22 AM
At my previous job I created a process to logship over 300 prod databases to DR. I don't have scripts handy as it has been a while back. But here are few points.
We were running log dumps every 15 mins. Every 30 mins we kick a job(s) which run on each prod Server(roughly about 20-30 Databases of various sizes on each of the prod Server), which goes thru each tran log dump folder in Prod and copy latest log files to DR, logging each log copy to the table. Few times a day we were restoring logs on about 30 of DR servers by going thru the table( that table was located on DBA monitoring server and was accessible by both Prod and DR Servers), checking what was copied and restoring logs in the proper order....

Hey Barsuk, This is exactly what I need to do. Right now I just have a script which generates restore command n then manually run it on the secondary server.
It will be so nice if you can find the script you used and share.
Thanks for your help.

barsuk SSChampion Points: 12280 More actions · Answer 13

Those restore commands you need generate and insert into the table at the time of the copy to the secondary. And then instead of running restore commands manually on the secondary server, all you need to do is to loop thru the table by the backup time and execute line by line restore command...

desiboy102493 Mr or Mrs. 500 Points: 506 More actions · Answer 14

barsuk - Wednesday, February 7, 2018 2:44 AM
Those restore commands you need generate and insert into the table at the time of the copy to the secondary. And then instead of running restore commands manually on the secondary server, all you need to do is to loop thru the table by the backup time and execute line by line restore command...

So I would have to create a custom table on both servers and insert the restore commands?

barsuk SSChampion Points: 12280 More actions · Answer 15

No, there are few ways to do that, but the most easiest way is to created only 1 table on a linked server ( which is accessible by both Prod and DR) so at the time of the copy you will need to create the following columns: Database name, Tran Log Name, Backup Time, Restore Time, Status, Tran Log File name... You can also add from Server Name , To Server Name if necessary. Once again that was the approach I used a while back. You need the Column status which should have the following values: like in progress, success, failed.... Tran Log File name is restore command you can create at the time of the copy dynamically ( we used LiteSpeed at that time) which starts with:
EXEC master.dbo.xp_restore_log......