Unsolved
This post is more than 5 years old
1 Rookie
•
92 Posts
0
1595
April 14th, 2010 09:00
Detail report of SQL databases when using MSSQL:
I can generate a report showing the backups of our SQL databases, but it is a summary report, not a detail of each and every database within a SQL instance.
Does anyone know how to get this either via NMC report, or MMINFO? I've tried, and can't find a way. This is necessary for Joint Commission and / or other regualtory reporting. They can't assume that database XYZ was backed up, even though that database was in SQL instance "ABCthroughXYZ" .
What I'm looking for would be a report showing each database being backed up:
msdb
Model
database1
database2
databseXYZ,
etc....
TIA.
7.5.2, NM for SQL.
No Events found!
Phil_1
20 Posts
0
April 15th, 2010 01:00
I'm in the same boat. I would like to know if this is possible.
I am using NW 7.5.1.
The simple answer could be: the SQL DBA can generate a list of backed up databases with times/ sizes and other info to confirm database backups.
I also use PowerSnap, therefore to backup databases in PowerSnap, we must use the saveset MSSQL:db1 which shows up via mminfo.
When backing up other client, where databases are smaller, or automatically created by some application, then we use MSSQL: and that all it shows is a list of savesets by the name MSSQL:
client1 MSSQL$instance1: 15/04/2010 00:01:01 15/04/2010 00:01:02 6 1549436 vF
client1 MSSQL$instance1: 15/04/2010 00:01:04 15/04/2010 00:01:04 6 2122708 vF
client1 MSSQL$instance1: 15/04/2010 00:01:07 15/04/2010 00:03:49 2 3128781484 vF
client1 MSSQL$instance1: 15/04/2010 00:04:05 15/04/2010 00:04:06 6 33167404 vF
client1 MSSQL$instance1: 15/04/2010 00:04:09 15/04/2010 00:04:09 2 3167140 vF
client1 MSSQL$instance1: 15/04/2010 00:04:12 15/04/2010 00:04:12 6 1585792 vF
client1 MSSQL$instance1: 15/04/2010 00:04:14 15/04/2010 00:04:15 2 14181648 vF
client1 MSSQL$instance1: 15/04/2010 00:04:19 15/04/2010 00:04:19 2 704 vF
This is good for the SQL DBA reports.
Due to our 'recent' upgrade of PowerSnap/ SQL module, when the SQL report is run, the report provides the 300/ 400 bytes of the Cover saveset.
client2 MSSQL:PS_DB 12/04/2010 12:56:53 12/04/2010 12:56:55 1 10487460 vF
client2 MSSQL:PS_DB 12/04/2010 12:56:58 12/04/2010 12:56:59 1 3212080 vF
client2 MSSQL:PS_DB 12/04/2010 12:57:25 12/04/2010 12:57:25 1 448 vKF
client2 MSSQL:PS_DB 12/04/2010 12:57:56 12/04/2010 12:57:56 1 416 vKF
client2 MSSQL:PS_DB 12/04/2010 12:58:36 12/04/2010 12:58:36 1 372 vKF
client2 MSSQL:PS_DB 13/04/2010 02:04:09 13/04/2010 02:00:33 1 10486820 vF
client2 MSSQL:PS_DB 13/04/2010 02:04:16 13/04/2010 02:00:38 1 3211888 vF
client2 MSSQL:PS_DB 13/04/2010 02:04:39 13/04/2010 02:01:01 1 448 vKF
client2 MSSQL:PS_DB 13/04/2010 02:04:55 13/04/2010 02:01:16 1 416 vKF
client2 MSSQL:PS_DB 13/04/2010 02:05:25 13/04/2010 02:01:47 1 372 vKF
client2 MSSQL:PS_DB 14/04/2010 02:03:08 14/04/2010 01:59:31 1 10486820 vF
client2 MSSQL:PS_DB 14/04/2010 02:03:13 14/04/2010 01:59:35 1 3211888 vF
client2 MSSQL:PS_DB 14/04/2010 02:03:35 14/04/2010 01:59:56 1 448 vKF
client2 MSSQL:PS_DB 14/04/2010 02:03:50 14/04/2010 02:00:12 1 416 vKF
client2 MSSQL:PS_DB 14/04/2010 02:04:21 14/04/2010 02:00:42 1 372 vKF
I have a case open with support who have validated (replicated in the labs) that the Cover saveset is reporting back to SQL, as opposed to the data backed up to tape. On the previous versions of PS/ SQL the data backed up to tape was reported back.
Once this is fixed, then contact your SQL DBA to generate the reports.
I also have the issue of 'instances', where it is possible to have multiple instances on the same server, and the second instance, nothing will be backed up. I can not be certain everything is backed up.
The other query I'd like to know is this saveset:
client3 C:\Documents and Settings\Default User\Application Data\database3Ded47248a5-ad45-8b44-b24b-23eac3854926 14/04/2010 0
2:02:52 14/04/2010 01:59:13 1 6152 vrF
Anyone know how this helps the reporting?
Phil
Holger_Inf
1 Rookie
•
122 Posts
0
April 15th, 2010 02:00
I believe this depends on the namespace used while doing the backup.
Namespace ist the Option -N, which gives the name for the saveset within mmdb.
Whole doing serverinitated backups this is set via the saveset definition, typically the entire DB
If you set the saveset definition to single instances you can get reports with mminfo, but you have to maintain this list
Doing clientinitated backups this is set via the command option -N. If you do not use this option this defaults to the saveset.
Hope this help
Holger_Inf
1 Rookie
•
122 Posts
0
April 15th, 2010 02:00
Oh, before I forgot: Be aware, that this may effect the schedules. The namespace is used to check, if there is already a level full backup of a saveset. If you change the name no matching full may be found and the next backup may be promoted to full
axavier1
76 Posts
0
April 15th, 2010 05:00
Hi David,
NMC can do what you want on Networker Backup Status, Save Set Details by Client Report, you also can create a script using gstclreport command to send report automatically.
Regards,
Anderson Xavier
rovinabi
55 Posts
0
April 15th, 2010 08:00
Hi Andreson,
Well, it is possible to pull the details on the databases that have been backed up using the nsrinfo command. Consider the saveset id of the MSSQL: for client SQLClient as XXXX. First you will have to find the nsavetime for this saveset using the mminfo command.
mminfo -avot -q ssid=Client -r nsavetime
Thn use the nsrinfo command as follows:
nsrinfo -t -n mssql SQLClien
Hope this is what you wanted!!!
Rovin D'Souza.
noladave
1 Rookie
•
92 Posts
0
April 15th, 2010 12:00
When you do a report in NMC, it won't list individual databases with the "instance". This is what you get:
What I would like to see, is each individual database name, as it gets backed up. The only way I know to get this, is to not use the default "MSSQL:(instance name). You would have to list each individual database in the client configuration under saveset.
I haven't tried the other nsrinfo. I will give that a try. Thank you all.
Server name
MSSQL$XXXXX911
4/8/10 21:48
1925092960
255,008
Server name
MSSQL$XXXXX911
4/8/10 21:52
1874761318
218,604
Server name
MSSQL$XXXXX911
4/8/10 21:52
1824429677
490,044
Server name
MSSQL$XXXXX911
4/8/10 21:52
1740543602
3,186,024
Server name
MSSQL$XXXXX911
4/8/10 21:52
1706989175
842,772
Server name
MSSQL$XXXXX911
4/8/10 21:52
1673434747
6,093,652
Server name
MSSQL$XXXXX911
4/8/10 21:53
1623103127
6,723,108
Server name
MSSQL$XXXXX911
4/8/10 21:53
1589548700
5,871,044
Server name
MSSQL$XXXXX911
4/8/10 21:53
1555994273
214,444
Server name
MSSQL$XXXXX911
4/8/10 21:53
1539217076
6,723,096
Server name
MSSQL$XXXXX911
4/8/10 21:53
1522439865
5,584,908
Server name
MSSQL$XXXXX911
4/8/10 21:53
1505662654
3,642,528
Server name
MSSQL$XXXXX911
4/8/10 21:53
1488885441
19,156,652
Server name
MSSQL$XXXXX911
4/8/10 21:53
1472108231
329,932
Server name
MSSQL$XXXXX911
4/8/10 21:54
1455331019
415,204
Server name
MSSQL$XXXXX911
4/8/10 21:54
1438553806
1,573,668
Server name
MSSQL$XXXXX911
4/8/10 21:54
1421776593
18,245,264
Server name
MSSQL$XXXXX911
4/8/10 21:54
1388222166
226,896
Server name
MSSQL$XXXXX911
4/8/10 21:54
1354667739
5,463,136
Server name
MSSQL$XXXXX911
4/8/10 21:54
1337890526
932
Server name
MSSQL$XXXXX911
4/9/10 23:37
1170210954
255,008
Server name
MSSQL$XXXXX911
4/9/10 23:37
1153433753
218,604
Server name
MSSQL$XXXXX911
4/9/10 23:37
1136656539
490,044
Server name
MSSQL$XXXXX911
4/9/10 23:37
1103102110
3,186,024
Server name
MSSQL$XXXXX911
4/9/10 23:37
1086324898
842,772
Server name
MSSQL$XXXXX911
4/9/10 23:37
1069547685
6,159,196
Server name
MSSQL$XXXXX911
4/9/10 23:38
1052770475
6,788,652
Phil_1
20 Posts
0
April 20th, 2010 01:00
Sorted. I have found out how:
on SQL server the log file:
C:\Program Files\Legato\nsr\applogs\nsrsqlsv.log (default location)
contains the information.
findstr /C:"Start time" /C:"has completed successfully" /C:"Stop time" /C:"Processing %s failed" nsrsqlsv.raw
will provide something similar to:
43708 1271726152 1 0 0 7368 7896 0 sql001.client.com (pid7896) 14 Start time: %s 1 35 25 Tue Apr 20 02:15:52 2010
53084 1271726640 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 9 Directory
53084 1271726941 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 9 Northwind
53084 1271727415 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 8 Registry
53084 1271727715 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 12 distribution
53084 1271728015 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 6 master
53084 1271728469 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 5 model
53084 1271728769 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 4 msdb
53084 1271729226 5 0 0 7368 7896 0 sql001.client.com (pid7896) 47 Processing %s failed, the item will be skipped. 1 0 4 pubs
43709 1271729237 1 0 0 7368 7896 0 sql001.client.com (pid7896) 13 Stop time: %s 1 35 25 Tue Apr 20 03:07:17 2010
43708 1271747130 1 0 0 6756 3536 0 sql001.client.com (pid3536) 14 Start time: %s 1 35 25 Tue Apr 20 08:05:30 2010
37996 1271748667 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 9 Directory
37996 1271748670 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 9 Northwind
37996 1271748700 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 8 Registry
37996 1271748701 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 12 distribution
37996 1271748703 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 6 master
37996 1271748703 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 5 model
37996 1271748706 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 4 msdb
37996 1271748707 1 0 0 6756 3536 0 sql001.client.com (pid3536) 43 The backup of %s has completed successfully 1 23 4 pubs
43709 1271748707 1 0 0 6756 3536 0 sql001.client.com (pid3536) 13 Stop time: %s 1 35 25 Tue Apr 20 08:31:47 2010
Then use perl (or use perl to do the equivalent findstr) or other text manipulating script to reword. Any perl gurus, once you sort the perl script, can you add it here, as I could use a perl script to do the lot. I know awk (well did a long time ago), however that is not available in my environment.
Phil
joka2
144 Posts
0
April 20th, 2010 20:00
I'd be interested in a Perl script for this operation. Do you have it?
Johannes