ONTAP Discussions

streaming backup

cheche030
5,159 Views

Hi,

     Is it posible to make a streaming backup of an user database with SnapManager for SQL?

5 REPLIES 5

abhisek
5,159 Views

Hi ,

Streaming backup is initiated only for System databases by SMSQL . For user databases we use snapshots and copy only backup methods.

Regards,

Abhishek

cheche030
5,159 Views

Then I'll use copy only backup method. Server is used only for SISS an has one user table for backup.

Thanks.

curtish
5,159 Views

If you place your user databases in the SAME volume as your system databases, SMSQL will stream backup the user databases to the SnapInfo LUN.....

Make sure you size the SnapInfo LUN accordingly.

Thanks,

Tom Hamilton

cheche030
5,159 Views

Yes, I have that configuration but backup fails in user database: [09:35:05.023]    WARNING: Database [SQLHealth] of [CFSDTS8\S177] is not in valid configuration, and will not be included in this backup.

This is the complete report:

[09:34:26.500]  *** SnapManager for SQL Server Report

[09:34:26.504]  Backup Time Stamp: 09-07-2012_09.34.26
[09:34:26.504]  Getting SQL Server Database Information, please wait...
[09:34:26.505]  Getting virtual disks information...
[09:34:35.105]  Querying SQL Server instances installed...
[09:34:35.108]  Creating threads for enumerating databases of different SQL Server instances.
[09:34:35.109]  Waiting for the threads to finish enumerating databases.
[09:34:35.328]  Connecting to SQL Server instance - CFSDTS8\S177...
[09:34:35.351]  Getting SQL Server version information...
[09:34:35.367]  Querying database information...
[09:34:35.464]  All threads finished enumerating databases.
[09:34:35.464]  Validating database configuration...
[09:34:35.464]  Setting database disk configuration...
[09:34:35.464]  Setting database locating on SQLRoot path...
[09:34:35.467]  Querying database disk volume configuration...
[09:34:35.467]  Getting SnapInfo directories configuration...

[09:34:35.477]  Checking dataset availability, connecting to SnapDrive...
[09:34:35.950]  Dataset feature is currently unavailable.
[09:34:35.950]  Getting SnapManager dataset info from...
[09:34:35.950]  Dataset info file has not yet created.
[09:34:35.951]  Getting SnapMirror Destinations Volumes...
[09:35:04.844]  Update selected destination volumes information for SQL Server CFSDTS8\S177 succeeded.
[09:35:05.020]  Get all destination volumes state successfully.
[09:35:05.023]    WARNING: Database [SQLHealth] of [CFSDTS8\S177] is not in valid configuration, and will not be included in this backup.
[09:35:05.051]  Getting current backup set groups...


[09:35:05.051]  Full database backup
[09:35:05.051]  Maximum databases of concurrent backup: 35
[09:35:05.051]  Minimum databases per backupset: 35
[09:35:05.051]  Naming convention: Generic
[09:35:05.051]  Run transaction log backup after full database backup: Yes
[09:35:05.051]  Transaction logs will be truncated after backup: Yes
[09:35:05.051]  Archive backup to SnapVault secondary: No
[09:35:05.051]  Delete older full backup: Yes
[09:35:05.051]  The snapshot older than 9 days 0 hours will be deleted.
[09:35:05.051]  Delete older SnapInfo snapshots: Yes
[09:35:05.051]  The SnapInfo snapshot older than 9 days 0 hours will be deleted. (same as full)
[09:35:05.051]  Retain up-to-minute restore ability for the latest backups number of days: 7
[09:35:05.051]  Create snapshot of SnapInfo drive after backup: Yes (always done on full backups)
[09:35:05.052]  Leave database attached after DBCC: No
[09:35:05.052]  Run DBCC CHECKDB with Live Database Before Backup: No
[09:35:05.052]  Run DBCC CHECKDB with Live Database After Backup: No
[09:35:05.052]  Backup Management Group: Daily
[09:35:05.052]  Update SnapMirror after operation: No
[09:35:05.052]  Run Command before the operation: No
[09:35:05.052]  Run Command after the operation: No
[09:35:05.052] 
[09:35:05.052]  Backup Database 1: CFSDTS8\S177 : master
[09:35:05.052]  Backup Database 2: CFSDTS8\S177 : model
[09:35:05.052]  Backup Database 3: CFSDTS8\S177 : msdb
[09:35:05.052]  Backup Database 4: CFSDTS8\S177 : SQLHealth


[09:35:05.052]  Backup database list:
[09:35:05.052]  1 - CFSDTS8\S177 : master
[09:35:05.052]  2 - CFSDTS8\S177 : model
[09:35:05.052]  3 - CFSDTS8\S177 : msdb


[09:35:05.057]  CHECK SNAPMANAGER LICENSE ON LUN.
[09:35:05.059]  Querying LUN license: K:\SnapInfo\...
[09:35:05.172]  Querying LUN license: K...


[09:35:05.252]  *** FULL DATABASE BACKUP


[09:35:05.254]  FULL DATABASE BACKUP [#1]


[09:35:05.254]  Full database backup location for database [master]:
[09:35:05.254]  K:\SnapInfo\SMSQL_SnapInfo\SQL__S177\DB__master\FG__\StreamFullBackup\09-07-2012_09.34.26__mast__Daily.FBK


[09:35:05.254]  Connecting to SQL Server CFSDTS8\S177...
[09:35:05.278]  Starting full database backup for database [master]...
[09:35:05.478]  Database [CFSDTS8\S177:master] Percent: 6por ciento procesado.6
[09:35:05.478]  Database [CFSDTS8\S177:master] Percent: 10por ciento procesado.10
[09:35:05.478]  Database [CFSDTS8\S177:master] Percent: 16por ciento procesado.16
[09:35:05.478]  Database [CFSDTS8\S177:master] Percent: 21por ciento procesado.21
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 25por ciento procesado.25
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 31por ciento procesado.31
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 35por ciento procesado.35
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 40por ciento procesado.40
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 46por ciento procesado.46
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 50por ciento procesado.50
[09:35:05.479]  Database [CFSDTS8\S177:master] Percent: 55por ciento procesado.55
[09:35:05.485]  Database [CFSDTS8\S177:master] Percent: 61por ciento procesado.61
[09:35:05.505]  Database [CFSDTS8\S177:master] Percent: 65por ciento procesado.65
[09:35:05.518]  Database [CFSDTS8\S177:master] Percent: 71por ciento procesado.71
[09:35:05.520]  Database [CFSDTS8\S177:master] Percent: 76por ciento procesado.76
[09:35:05.523]  Database [CFSDTS8\S177:master] Percent: 80por ciento procesado.80
[09:35:05.525]  Database [CFSDTS8\S177:master] Percent: 86por ciento procesado.86
[09:35:05.529]  Database [CFSDTS8\S177:master] Percent: 91por ciento procesado.91
[09:35:05.553]  Database [CFSDTS8\S177:master] Percent: 95por ciento procesado.95
[09:35:05.564]  Database [CFSDTS8\S177:master] Percent: 100por ciento procesado.100
[09:35:05.634]  Database [CFSDTS8\S177:master] BACKUP DATABASE procesó correctamente 378 páginas en 0.206 segundos (14.330 MB/s).
[09:35:05.636]  Full database backup of database [master] completed.

[09:35:05.643]  Setting operation result to registry...
[09:35:05.643]  Backup operation on SQL instance [CFSDTS8\S177] database [master]


[09:35:05.880]  FULL DATABASE BACKUP [#2]


[09:35:05.880]  Full database backup location for database [model]:
[09:35:05.880]  K:\SnapInfo\SMSQL_SnapInfo\SQL__S177\DB__model\FG__\StreamFullBackup\09-07-2012_09.34.26__mode__Daily.FBK
[09:35:05.880]  Starting full database backup for database [model]...
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 9por ciento procesado.9
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 13por ciento procesado.13
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 18por ciento procesado.18
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 22por ciento procesado.22
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 27por ciento procesado.27
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 31por ciento procesado.31
[09:35:05.981]  Database [CFSDTS8\S177:model] Percent: 36por ciento procesado.36
[09:35:05.982]  Database [CFSDTS8\S177:model] Percent: 40por ciento procesado.40
[09:35:05.982]  Database [CFSDTS8\S177:model] Percent: 45por ciento procesado.45
[09:35:05.984]  Database [CFSDTS8\S177:model] Percent: 54por ciento procesado.54
[09:35:05.986]  Database [CFSDTS8\S177:model] Percent: 58por ciento procesado.58
[09:35:05.987]  Database [CFSDTS8\S177:model] Percent: 63por ciento procesado.63
[09:35:05.991]  Database [CFSDTS8\S177:model] Percent: 67por ciento procesado.67
[09:35:06.005]  Database [CFSDTS8\S177:model] Percent: 72por ciento procesado.72
[09:35:06.012]  Database [CFSDTS8\S177:model] Percent: 76por ciento procesado.76
[09:35:06.012]  Database [CFSDTS8\S177:model] Percent: 81por ciento procesado.81
[09:35:06.012]  Database [CFSDTS8\S177:model] Percent: 85por ciento procesado.85
[09:35:06.013]  Database [CFSDTS8\S177:model] Percent: 90por ciento procesado.90
[09:35:06.014]  Database [CFSDTS8\S177:model] Percent: 99por ciento procesado.99
[09:35:06.030]  Database [CFSDTS8\S177:model] Percent: 100por ciento procesado.100
[09:35:06.092]  Database [CFSDTS8\S177:model] BACKUP DATABASE procesó correctamente 177 páginas en 0.096 segundos (14.404 MB/s).
[09:35:06.093]  Full database backup of database [model] completed.

[09:35:06.098]  Setting operation result to registry...
[09:35:06.098]  Backup operation on SQL instance [CFSDTS8\S177] database [model]


[09:35:06.155]  FULL DATABASE BACKUP [#3]


[09:35:06.155]  Full database backup location for database [msdb]:
[09:35:06.155]  K:\SnapInfo\SMSQL_SnapInfo\SQL__S177\DB__msdb\FG__\StreamFullBackup\09-07-2012_09.34.26__msdb__Daily.FBK
[09:35:06.156]  Starting full database backup for database [msdb]...
[09:35:06.586]  Database [CFSDTS8\S177:msdb] Percent: 5por ciento procesado.5
[09:35:06.633]  Database [CFSDTS8\S177:msdb] Percent: 10por ciento procesado.10
[09:35:06.664]  Database [CFSDTS8\S177:msdb] Percent: 16por ciento procesado.16
[09:35:06.766]  Database [CFSDTS8\S177:msdb] Percent: 21por ciento procesado.21
[09:35:06.795]  Database [CFSDTS8\S177:msdb] Percent: 27por ciento procesado.27
[09:35:06.813]  Database [CFSDTS8\S177:msdb] Percent: 32por ciento procesado.32
[09:35:06.844]  Database [CFSDTS8\S177:msdb] Percent: 35por ciento procesado.35
[09:35:07.017]  Database [CFSDTS8\S177:msdb] Percent: 40por ciento procesado.40
[09:35:07.116]  Database [CFSDTS8\S177:msdb] Percent: 46por ciento procesado.46
[09:35:07.217]  Database [CFSDTS8\S177:msdb] Percent: 51por ciento procesado.51
[09:35:07.266]  Database [CFSDTS8\S177:msdb] Percent: 57por ciento procesado.57
[09:35:07.286]  Database [CFSDTS8\S177:msdb] Percent: 62por ciento procesado.62
[09:35:07.422]  Database [CFSDTS8\S177:msdb] Percent: 65por ciento procesado.65
[09:35:07.469]  Database [CFSDTS8\S177:msdb] Percent: 70por ciento procesado.70
[09:35:07.538]  Database [CFSDTS8\S177:msdb] Percent: 76por ciento procesado.76
[09:35:07.581]  Database [CFSDTS8\S177:msdb] Percent: 81por ciento procesado.81
[09:35:07.642]  Database [CFSDTS8\S177:msdb] Percent: 87por ciento procesado.87
[09:35:07.670]  Database [CFSDTS8\S177:msdb] Percent: 92por ciento procesado.92
[09:35:07.720]  Database [CFSDTS8\S177:msdb] Percent: 95por ciento procesado.95
[09:35:07.723]  Database [CFSDTS8\S177:msdb] Percent: 100por ciento procesado.100
[09:35:07.787]  Database [CFSDTS8\S177:msdb] BACKUP DATABASE procesó correctamente 4699 páginas en 1.421 segundos (25.830 MB/s).
[09:35:07.788]  Full database backup of database [msdb] completed.

[09:35:07.794]  Setting operation result to registry...
[09:35:07.794]  Backup operation on SQL instance [CFSDTS8\S177] database [msdb]
[09:35:07.849]  *** TRANSACTION LOG BACKUP AFTER FULL DATABASE BACKUP

[09:35:07.852]  Transaction log backup for database [master] skipped (Simple Recovery Model).
[09:35:07.852]  Transaction log backup for database [model] skipped (Simple Recovery Model).
[09:35:07.852]  Transaction log backup for database [msdb] skipped (Simple Recovery Model).
[09:35:07.852] 
[09:35:07.852]  *** BACKUP SNAPINFO DISK
[09:35:07.857] 
[09:35:07.857]  UPDATE MOST RECENT SNAPINFO DRIVE SNAPSHOT NAME

[09:35:13.381]  Rename snapshot successfully completed.
[09:35:13.383]  SNAPSHOT SNAPINFO DRIVE

[09:35:13.384]  Creating Snapshot of LUN(s):
[09:35:13.384]  LUN [K:\SnapInfo\]
[09:35:13.384]  Snapshot name: sqlinfo__cfsdts8__recent
[09:35:23.258]  Snapshot SnapInfo drive(s) successfully completed.


[09:35:23.263]  *** DELETE THE OLDEST STREAM BASED BACKUP DATA SETS
[09:35:23.263]  Current time: 07/09/2012 9:34:26
[09:35:23.263]  Backups older than [12960] minutes ago will be deleted.
[09:35:23.263]  Backups older than [29/08/2012 9:34:26] may get deleted.
[09:35:23.263] 
[09:35:23.263]  *** DELETING PREVIOUS BACKUPS: master

[09:35:23.263]  Querying stream based full database backup for [master]...
[09:35:23.266]  Current time: 07/09/2012 9:34:26
[09:35:23.266]  Backups older than [10080] minutes ago will be deleted.
[09:35:23.266] 
[09:35:23.266]  DELETING TRANSACTION LOG BACKUPS [#1]: master

[09:35:23.266]  Transaction log backups older than [08-30-2012_03.12.18] (last full backup) will be deleted.

[09:35:23.267]  No transaction log backup sets for master were detected.
[09:35:23.267]  No transaction log backup of [master] will be deleted.
[09:35:23.267] 
[09:35:23.267]  *** DELETING PREVIOUS BACKUPS: model

[09:35:23.267]  Querying stream based full database backup for [model]...
[09:35:23.269]  Current time: 07/09/2012 9:34:26
[09:35:23.269]  Backups older than [10080] minutes ago will be deleted.
[09:35:23.269] 
[09:35:23.269]  DELETING TRANSACTION LOG BACKUPS [#2]: model

[09:35:23.269]  Transaction log backups older than [08-30-2012_03.12.18] (last full backup) will be deleted.

[09:35:23.270]  No transaction log backup sets for model were detected.
[09:35:23.270]  No transaction log backup of [model] will be deleted.
[09:35:23.270] 
[09:35:23.270]  *** DELETING PREVIOUS BACKUPS: msdb

[09:35:23.270]  Querying stream based full database backup for [msdb]...
[09:35:23.272]  Current time: 07/09/2012 9:34:26
[09:35:23.272]  Backups older than [10080] minutes ago will be deleted.
[09:35:23.272] 
[09:35:23.273]  DELETING TRANSACTION LOG BACKUPS [#3]: msdb

[09:35:23.273]  Transaction log backups older than [08-30-2012_03.12.18] (last full backup) will be deleted.

[09:35:23.273]  No transaction log backup sets for msdb were detected.
[09:35:23.273]  No transaction log backup of [msdb] will be deleted.
[09:35:23.273] 
[09:35:23.273]  *** DELETING LOG SNAPINFO DISK SNAPSHOT

[09:35:23.278]  Current time: 07/09/2012 9:34:26
[09:35:23.278]  Backups older than [12960] minutes ago will be deleted.
[09:35:23.278]  Backups older than [29/08/2012 9:34:26] will be deleted.
[09:35:23.278]  Get log snapshot list from LUN [K:\SnapInfo\]: ...


[09:35:23.596]  There are total [11] snapinfo snapshot for LUN [K:\SnapInfo\].
[09:35:23.596]  No snapinfo snapshots for this backup set will be deleted because 9.00 days must be retained.

[09:35:23.599] 
[09:35:23.599]  **** FULL DATABASE BACKUP RESULT SUMMARY ****
[09:35:23.599]  Backup Time: 09-07-2012_09.34.26


[09:35:23.599]  Backup Group [#1]:
[09:35:23.599]  #1 : [CFSDTS8\S177 - master] : OK
[09:35:23.599]  #2 : [CFSDTS8\S177 - model] : OK
[09:35:23.599]  #3 : [CFSDTS8\S177 - msdb] : OK


[09:35:23.599]  Databases skipped in the Backup:


[09:35:23.599]  #1 : [CFSDTS8\S177 - SQLHealth] backup did not complete.
[09:35:23.599] 
[09:35:23.599]  *** SNAPMANAGER BACKUP JOB ENDED AT: [09-07-2012 09.35.23]
[09:35:23.599]  The full database backup partially completed. Backup of one or more databases was skipped. See the backup report for details.

sethm
5,159 Views

Having the user databases on the same LUN as the system databases is an invalid layout (see http://media.netapp.com/documents/tr-3941.pdf for a great diagram of the proper layout).  The SMSQL Configuration Wizard provides a method to relocate databases, or you can relocate them yourself, but one way or the other you need to get to a supported/proper layout.

Public