How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")

Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.

works:
[build @.RestoreCmd]
exec (@.RestoreCmd)

doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder

Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]

Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder

Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.

Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.

Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.
Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.
|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

How to drop a full-text that references an invalid path

I have restored an SQL database on a different server and the Full-Text
catalogs are referencing the location and full-text indexes of the previous
server.
When trying to drop I receive the error message 15601: "Full-Text Search is
not enabled for the current database. Use sp_fulltext_database to enable
Full-Text Search."
When trying EXEC sp_fulltext_database 'enable', I receive the error message
7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
Full-text
search was not installed properly."
I also tried creating the path on the new server, without success: same
error message. How can I get rid of this index?
Hi Monica
This may help you to copy the full text catalogues to the new server.
http://support.microsoft.com/kb/240867/EN-US/
You may want to try and droping the catalogues and re-create them.
John
"Monica Rocha" wrote:

> I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>
|||drop the full text indexes on the tables, then try to drop the catalog. If
this does not word delete the offending entry from sysfulltextcatalogs. Then
create a new catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Monica Rocha" <Monica Rocha@.discussions.microsoft.com> wrote in message
news:C7A986AA-2228-4567-8B68-4358CB75391A@.microsoft.com...
>I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the
> previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search
> is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error
> message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>

How to drop a full-text that references an invalid path

I have restored an SQL database on a different server and the Full-Text
catalogs are referencing the location and full-text indexes of the previous
server.
When trying to drop I receive the error message 15601: "Full-Text Search is
not enabled for the current database. Use sp_fulltext_database to enable
Full-Text Search."
When trying EXEC sp_fulltext_database 'enable', I receive the error message
7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
Full-text
search was not installed properly."
I also tried creating the path on the new server, without success: same
error message. How can I get rid of this index?Hi Monica
This may help you to copy the full text catalogues to the new server.
http://support.microsoft.com/kb/240867/EN-US/
You may want to try and droping the catalogues and re-create them.
John
"Monica Rocha" wrote:

> I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the previou
s
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search i
s
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error messag
e
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>|||drop the full text indexes on the tables, then try to drop the catalog. If
this does not word delete the offending entry from sysfulltextcatalogs. Then
create a new catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Monica Rocha" <Monica Rocha@.discussions.microsoft.com> wrote in message
news:C7A986AA-2228-4567-8B68-4358CB75391A@.microsoft.com...
>I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the
> previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search
> is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error
> message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>

How to drop a full-text that references an invalid path

I have restored an SQL database on a different server and the Full-Text
catalogs are referencing the location and full-text indexes of the previous
server.
When trying to drop I receive the error message 15601: "Full-Text Search is
not enabled for the current database. Use sp_fulltext_database to enable
Full-Text Search."
When trying EXEC sp_fulltext_database 'enable', I receive the error message
7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
Full-text
search was not installed properly."
I also tried creating the path on the new server, without success: same
error message. How can I get rid of this index?Hi Monica
This may help you to copy the full text catalogues to the new server.
http://support.microsoft.com/kb/240867/EN-US/
You may want to try and droping the catalogues and re-create them.
John
"Monica Rocha" wrote:
> I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>|||drop the full text indexes on the tables, then try to drop the catalog. If
this does not word delete the offending entry from sysfulltextcatalogs. Then
create a new catalog.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Monica Rocha" <Monica Rocha@.discussions.microsoft.com> wrote in message
news:C7A986AA-2228-4567-8B68-4358CB75391A@.microsoft.com...
>I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the
> previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search
> is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error
> message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>