Could this be run using OSQL script

If there a way to detect if backup fails? I like to add better error
handling... I need to be able to run using a script not thru the Sql
Scheduler.
DECLARE @.name VARCHAR(50) -- database name
DECLARE @.path VARCHAR(256) -- path for backup files
DECLARE @.fileName VARCHAR(256) -- filename for backup
DECLARE @.fileDate VARCHAR(20) -- used for file name
SET @.path = 'C:\Backup\'
SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
BACKUP DATABASE @.name TO DISK = @.fileName
FETCH NEXT FROM db_cursor INTO @.name
END
CLOSE db_cursor
DEALLOCATE db_cursorHi
You could test @.@.ERROR after performing the backup to see if the backup
command worked and store a list of failures, but some errors will abort the
batch and therefore subsequent databases will not be backed up. SQL2005 has
better error handling. The script can be run using OSQL with the -i flag if
you save the script to a file. The -b flag reports as a command line error
any error value so it could be trapped using ERRORLEVEL it will be 1 if one
of the backups failed. You may also want to use the -n flag to suppress line
numbers. If using SQL Agent you do not need to use a command prompt.
Your backups will append to any files that are already present with the
given name you may want to specify the INIT command if you want to overwrite
these.
John
"AHartman" wrote:
> If there a way to detect if backup fails? I like to add better error
> handling... I need to be able to run using a script not thru the Sql
> Scheduler.
> DECLARE @.name VARCHAR(50) -- database name
> DECLARE @.path VARCHAR(256) -- path for backup files
> DECLARE @.fileName VARCHAR(256) -- filename for backup
> DECLARE @.fileDate VARCHAR(20) -- used for file name
> SET @.path = 'C:\Backup\'
> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
> DECLARE db_cursor CURSOR FOR
> SELECT name
> FROM master.dbo.sysdatabases
> WHERE name NOT IN ('master','model','msdb','tempdb')
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
> BACKUP DATABASE @.name TO DISK = @.fileName
> FETCH NEXT FROM db_cursor INTO @.name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
>|||Thanks... for the info
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:45510EB6-452B-4597-8775-99F7FC501054@.microsoft.com...
> Hi
> You could test @.@.ERROR after performing the backup to see if the backup
> command worked and store a list of failures, but some errors will abort
> the
> batch and therefore subsequent databases will not be backed up. SQL2005
> has
> better error handling. The script can be run using OSQL with the -i flag
> if
> you save the script to a file. The -b flag reports as a command line error
> any error value so it could be trapped using ERRORLEVEL it will be 1 if
> one
> of the backups failed. You may also want to use the -n flag to suppress
> line
> numbers. If using SQL Agent you do not need to use a command prompt.
> Your backups will append to any files that are already present with the
> given name you may want to specify the INIT command if you want to
> overwrite
> these.
> John
> "AHartman" wrote:
>> If there a way to detect if backup fails? I like to add better error
>> handling... I need to be able to run using a script not thru the Sql
>> Scheduler.
>> DECLARE @.name VARCHAR(50) -- database name
>> DECLARE @.path VARCHAR(256) -- path for backup files
>> DECLARE @.fileName VARCHAR(256) -- filename for backup
>> DECLARE @.fileDate VARCHAR(20) -- used for file name
>> SET @.path = 'C:\Backup\'
>> SELECT @.fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
>> DECLARE db_cursor CURSOR FOR
>> SELECT name
>> FROM master.dbo.sysdatabases
>> WHERE name NOT IN ('master','model','msdb','tempdb')
>> OPEN db_cursor
>> FETCH NEXT FROM db_cursor INTO @.name
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.fileName = @.path + @.name + '_' + @.fileDate + '.BAK'
>> BACKUP DATABASE @.name TO DISK = @.fileName
>> FETCH NEXT FROM db_cursor INTO @.name
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>>sql

corrupted tables

Does anyone make a program that will detect and attempt to
correct corrupted tables in a sql database?
DBCC CHECKDB ?
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||Hi,
Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
status of your database.
If you found any errors on any table, you could use
DBCC CHECKTABLE with repair options to clear the errors. Please verify books
online for
command usage.
Thanks
Hari
MCDBA
"dave" <davidn@.carlsonpaving.com> wrote in message
news:986601c478b1$ca55c660$a301280a@.phx.gbl...
> Does anyone make a program that will detect and attempt to
> correct corrupted tables in a sql database?
>
|||You should always try to restore from your backups rather than run repair -
as repair does not preserve the constraints and business logic inherent in
your data.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute DBCC cHECKDB('dbname'), it executes and gives you the cuirrent
> status of your database.
> If you found any errors on any table, you could use
> DBCC CHECKTABLE with repair options to clear the errors. Please verify
books
> online for
> command usage.
> Thanks
> Hari
> MCDBA
>
> "dave" <davidn@.carlsonpaving.com> wrote in message
> news:986601c478b1$ca55c660$a301280a@.phx.gbl...
>
|||Hi Paul,
MS documentation states that DBCC with REPAIR_REBUILD option will never ends
in a data loss. One instance I had solved an issue
in my production server with out any data loss using the REPAIR_REBUILD
option. That is the reason I recommended Repair_option.
I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
data loss.
So will you please recommend the usage of Repair option?
Correct me if my logic is wrong.
Thanks
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> You should always try to restore from your backups rather than run
repair -
> as repair does not preserve the constraints and business logic inherent in
> your data.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:#OM7iIPeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> books
>
|||You are correct that REPAIR_REBUILD will not result in data loss - however,
with all due respect, you didn't say that in your reply - you just said 'If
you found any errors on any table, you could use DBCC CHECKTABLE with repair
options to clear the errors.'
Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration of
the effects. This is spelled out load and clear in the BOL for SQL Server
2005.
Some other things to consider:
1) using a repair option requires the database to be in single_user mode,
i.e. the whole thing is offline, not just the index being rebuilt
2) when any problems are found, root cause analysis should be done rather
than blindly fixing them using repair, even for relatively innocuous
problems involving non-clustered indexes.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hi Paul,
> MS documentation states that DBCC with REPAIR_REBUILD option will never
ends[vbcol=seagreen]
> in a data loss. One instance I had solved an issue
> in my production server with out any data loss using the REPAIR_REBUILD
> option. That is the reason I recommended Repair_option.
> I know that if you use the option "REPAIR_ALLOW_DATA_LOSS" will result in
> data loss.
> So will you please recommend the usage of Repair option?
> Correct me if my logic is wrong.
> Thanks
> Hari
> MCDBA
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:OQvVGkBhEHA.3980@.TK2MSFTNGP12.phx.gbl...
> repair -
in
> rights.
>
|||Than
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||THan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>
|||Thanks Paul Randal for the detailed information.
Regards
Hari
MCDBA
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OtxQk8HhEHA.2052@.tk2msftngp13.phx.gbl...
> You are correct that REPAIR_REBUILD will not result in data loss -
however,
> with all due respect, you didn't say that in your reply - you just said
'If
> you found any errors on any table, you could use DBCC CHECKTABLE with
repair
> options to clear the errors.'
> Most people just default to REPAIR_ALLOW_DATA_LOSS with no consideration
of
> the effects. This is spelled out load and clear in the BOL for SQL Server
> 2005.
> Some other things to consider:
> 1) using a repair option requires the database to be in single_user mode,
> i.e. the whole thing is offline, not just the index being rebuilt
> 2) when any problems are found, root cause analysis should be done rather
> than blindly fixing them using repair, even for relatively innocuous
> problems involving non-clustered indexes.
> Thanks and regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:egebSIEhEHA.2952@.TK2MSFTNGP09.phx.gbl...
> ends
in[vbcol=seagreen]
inherent[vbcol=seagreen]
> in
cuirrent[vbcol=seagreen]
verify
>