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

Could not find server 'x' in sysservers

SQL2005:
Updating some tables in a database works fine. Updating others in the
same database fails with
"Could not find server 'picasso' in sysservers".
How come it goes for some and not others?
"picasso" is our old server that doesn't exist anymore. It was holding
a SQL-2000 that has been upgraded to SQL-2005...?
Thanks in advance for any help.
Regards /SnedkerIs it possible that you have a trigger on those tables that tries to
insert into 'picasso' ?
run EXEC sp_helptrigger TableName to find out if there are triggers on
those tables
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Morten Snedker wrote:
> SQL2005:
> Updating some tables in a database works fine. Updating others in the
> same database fails with
> "Could not find server 'picasso' in sysservers".
> How come it goes for some and not others?
> "picasso" is our old server that doesn't exist anymore. It was holding
> a SQL-2000 that has been upgraded to SQL-2005...?
>
> Thanks in advance for any help.
>
> Regards /Snedker|||On 7 Jun 2006 05:56:57 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:
Aaaahaaaaaaaaaaaaaaaaaaa ! :-)
Actually I already changed some tables for that exact same thing, but
seems like I didn't get all the way around. Thanks for getting me back
on track! ;-)
Regards /Snedker
>Is it possible that you have a trigger on those tables that tries to
>insert into 'picasso' ?
>run EXEC sp_helptrigger TableName to find out if there are triggers on
>those tables
>
>Denis the SQL Menace
>http://sqlservercode.blogspot.com/
>Morten Snedker wrote: