what do Backup and Restore Return

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

what do Backup and Restore Return

Author

Topic

gmetaj
Starting Member

33 Posts

Posted - 2004-05-17 : 17:35:02

What do backup and Restore command in SQL return. IS there a way to catch/return the text message that those commands return such as backup completed successfully or failed such as that.

thank you!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-17 : 19:50:50

It will produce an error if the backup fails. You can capture the fact an error occurred. If you want to capture the actual messages that SQL Server sets up, you can write the results to an output file from the job you are running. Just check the option to save output to a file. I do this for all my backups, so I have an audit file each day.

If you want, you can also use WITH STATS = 1 (or whatever percent) to see the percentage a backups has completed.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sorengi
Starting Member

48 Posts

Posted - 2004-05-19 : 16:01:03

The backups return information level messages. I know of no way in SQL Server to catch these, but you can from a calling application, for example:

-- Using ODBC and Java


catch(SQLException sex)
{
   String fatalError = "no";
   // go get all messages and check sql state for fatal or not
   while (sex != null)
   {
      System.out.println("> " + sex.getSQLState() +" "+ sex.getMessage());
      if(!sex.getSQLState().equals("01000")) // informational
      {
         fatalError = "yes";
      }
      sex = sex.getNextException();
  }
  // if fatal error then quit here
  if(fatalError.equals("yes"))
  {
     return;
  }
}

Michael D.

sorengi
Starting Member

48 Posts

Posted - 2004-05-19 : 16:42:18

I looked it up, and BACKUP DATABASE for example returns the following
Error:

error=4035
severity=10 -- informational
Processed %d pages for database '%ls', file '%ls' on file %d.

However, you can not test against BACKUP DATABASE. @@ERROR remains 0, althought @@ROWCOUNT increases, there is no way to Catch or Stop the Messages.

If you could catch the error id, you could look up the severity in master.dbo.sysmessages, but nothin' seems to work here...

Michael D.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources