Help with SQL backup verification, unable to verify date appended

Hi,
I need help with my SQL script for appending the current date to my backup
and then being ale to verify the daily changing DB backup name. Here is my
script, i realize that the convert part is probably part of teh problem but
are there any commands to take it place in order for the verify to happen.
please help it is sort of urgent. Thanks - Nik
USE [master]
/* points to database that will be used */
GO
DBCC SHRINKDATABASE(N'master' )
/* This line shrinks database and log file*/
GO
USE [master]
/* points to database that will be used */
GO
DBCC CHECKDB(N'master', NOINDEX)
/* This line does integrity check of database*/
GO
declare @.f sysname
set @.f=N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME =
N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* This line backups named database to location specified*/
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'master' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'master' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GO
Hi,
I did not test your script but I can give you some comments here:
1) The part to append the current date works fine
2) Store the filename in a variable instead of runing getdate() twice as the
date could be different the first and the second time (like just after
midnight)
3) Why do you need to verify data from msdb?
4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
Hope this helps,
Ben Nevarez
"lca1630" wrote:

> Hi,
> I need help with my SQL script for appending the current date to my backup
> and then being ale to verify the daily changing DB backup name. Here is my
> script, i realize that the convert part is probably part of teh problem but
> are there any commands to take it place in order for the verify to happen.
> please help it is sort of urgent. Thanks - Nik
> USE [master]
> /* points to database that will be used */
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
> GO
> USE [master]
> /* points to database that will be used */
> GO
> DBCC CHECKDB(N'master', NOINDEX)
> /* This line does integrity check of database*/
> GO
> declare @.f sysname
> set @.f=N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
> BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME =
> N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> /* This line backups named database to location specified*/
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'master' )
> if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> for database ''master'' not found.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
> WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
>
> GO
|||Thanks for looking at this Ben. I am using SQL Express therefore the need to
come up with a workaround maintanence plan etc. The verify portion is
basically me manually setting up the backup and then having the SQL
management studio express script it for me. This is what it comes up with.
The variable part I am not sure of what to set that too, if you have any
suggestions please let me know. Thanks - Nik
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi,
> I did not test your script but I can give you some comments here:
> 1) The part to append the current date works fine
> 2) Store the filename in a variable instead of runing getdate() twice as the
> date could be different the first and the second time (like just after
> midnight)
> 3) Why do you need to verify data from msdb?
> 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> Hope this helps,
> Ben Nevarez
>
> "lca1630" wrote:
|||What I mean is to use a variable for the date or the filename to make sure
that both BACKUP and RESTORE VERIFYONLY use the same file. For example when
you run the BACKUP getdate() may return 20080109 but by the time the job is
about to run RESTORE VERIFYONLY getdate may return 20080110 and will fail
because there is no such file. Something like
set @.filename = ... getdate(), 112 ...
...
backup database ... to disk = @.filename
...
restore verifyonly from disk = @.filename
Hope this helps,
Ben Nevarez
"lca1630" wrote:
[vbcol=seagreen]
> Thanks for looking at this Ben. I am using SQL Express therefore the need to
> come up with a workaround maintanence plan etc. The verify portion is
> basically me manually setting up the backup and then having the SQL
> management studio express script it for me. This is what it comes up with.
> The variable part I am not sure of what to set that too, if you have any
> suggestions please let me know. Thanks - Nik
> "Ben Nevarez" wrote:
|||"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:C6063C2C-D9F0-4082-BBDA-DE010216638E@.microsoft.com...
> Hi,
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
Another cmment: Don't do the above step.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Help with SQL backup verification, unable to verify date appended

Hi,
I need help with my SQL script for appending the current date to my backup
and then being ale to verify the daily changing DB backup name. Here is my
script, i realize that the convert part is probably part of teh problem but
are there any commands to take it place in order for the verify to happen.
please help it is sort of urgent. Thanks - Nik
USE [master]
/* points to database that will be used */
GO
DBCC SHRINKDATABASE(N'master' )
/* This line shrinks database and log file*/
GO
USE [master]
/* points to database that will be used */
GO
DBCC CHECKDB(N'master', NOINDEX)
/* This line does integrity check of database*/
GO
declare @.f sysname
set @.f=N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* This line backups named database to location specified*/
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'master' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'master' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GOHi,
I did not test your script but I can give you some comments here:
1) The part to append the current date works fine
2) Store the filename in a variable instead of runing getdate() twice as the
date could be different the first and the second time (like just after
midnight)
3) Why do you need to verify data from msdb?
4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
Hope this helps,
Ben Nevarez
"lca1630" wrote:
> Hi,
> I need help with my SQL script for appending the current date to my backup
> and then being ale to verify the daily changing DB backup name. Here is my
> script, i realize that the convert part is probably part of teh problem but
> are there any commands to take it place in order for the verify to happen.
> please help it is sort of urgent. Thanks - Nik
> USE [master]
> /* points to database that will be used */
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
> GO
> USE [master]
> /* points to database that will be used */
> GO
> DBCC CHECKDB(N'master', NOINDEX)
> /* This line does integrity check of database*/
> GO
> declare @.f sysname
> set @.f=N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME => N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> /* This line backups named database to location specified*/
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'master' )
> if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> for database ''master'' not found.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
>
> GO|||Thanks for looking at this Ben. I am using SQL Express therefore the need to
come up with a workaround maintanence plan etc. The verify portion is
basically me manually setting up the backup and then having the SQL
management studio express script it for me. This is what it comes up with.
The variable part I am not sure of what to set that too, if you have any
suggestions please let me know. Thanks - Nik
"Ben Nevarez" wrote:
> Hi,
> I did not test your script but I can give you some comments here:
> 1) The part to append the current date works fine
> 2) Store the filename in a variable instead of runing getdate() twice as the
> date could be different the first and the second time (like just after
> midnight)
> 3) Why do you need to verify data from msdb?
> 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> Hope this helps,
> Ben Nevarez
>
> "lca1630" wrote:
> > Hi,
> >
> > I need help with my SQL script for appending the current date to my backup
> > and then being ale to verify the daily changing DB backup name. Here is my
> > script, i realize that the convert part is probably part of teh problem but
> > are there any commands to take it place in order for the verify to happen.
> > please help it is sort of urgent. Thanks - Nik
> >
> > USE [master]
> > /* points to database that will be used */
> > GO
> > DBCC SHRINKDATABASE(N'master' )
> > /* This line shrinks database and log file*/
> > GO
> > USE [master]
> > /* points to database that will be used */
> > GO
> > DBCC CHECKDB(N'master', NOINDEX)
> > /* This line does integrity check of database*/
> > GO
> > declare @.f sysname
> > set @.f=N'C:\SQL
> > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME => > N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> > /* This line backups named database to location specified*/
> > GO
> > declare @.backupSetId as int
> > select @.backupSetId = position from msdb..backupset where
> > database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset where database_name=N'master' )
> > if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> > for database ''master'' not found.', 16, 1) end
> > RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
> >
> >
> > GO|||What I mean is to use a variable for the date or the filename to make sure
that both BACKUP and RESTORE VERIFYONLY use the same file. For example when
you run the BACKUP getdate() may return 20080109 but by the time the job is
about to run RESTORE VERIFYONLY getdate may return 20080110 and will fail
because there is no such file. Something like
set @.filename = ... getdate(), 112 ...
...
backup database ... to disk = @.filename
...
restore verifyonly from disk = @.filename
Hope this helps,
Ben Nevarez
"lca1630" wrote:
> Thanks for looking at this Ben. I am using SQL Express therefore the need to
> come up with a workaround maintanence plan etc. The verify portion is
> basically me manually setting up the backup and then having the SQL
> management studio express script it for me. This is what it comes up with.
> The variable part I am not sure of what to set that too, if you have any
> suggestions please let me know. Thanks - Nik
> "Ben Nevarez" wrote:
> >
> > Hi,
> >
> > I did not test your script but I can give you some comments here:
> >
> > 1) The part to append the current date works fine
> > 2) Store the filename in a variable instead of runing getdate() twice as the
> > date could be different the first and the second time (like just after
> > midnight)
> > 3) Why do you need to verify data from msdb?
> > 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> > "lca1630" wrote:
> >
> > > Hi,
> > >
> > > I need help with my SQL script for appending the current date to my backup
> > > and then being ale to verify the daily changing DB backup name. Here is my
> > > script, i realize that the convert part is probably part of teh problem but
> > > are there any commands to take it place in order for the verify to happen.
> > > please help it is sort of urgent. Thanks - Nik
> > >
> > > USE [master]
> > > /* points to database that will be used */
> > > GO
> > > DBCC SHRINKDATABASE(N'master' )
> > > /* This line shrinks database and log file*/
> > > GO
> > > USE [master]
> > > /* points to database that will be used */
> > > GO
> > > DBCC CHECKDB(N'master', NOINDEX)
> > > /* This line does integrity check of database*/
> > > GO
> > > declare @.f sysname
> > > set @.f=N'C:\SQL
> > > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > > BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME => > > N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> > > /* This line backups named database to location specified*/
> > > GO
> > > declare @.backupSetId as int
> > > select @.backupSetId = position from msdb..backupset where
> > > database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> > > msdb..backupset where database_name=N'master' )
> > > if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> > > for database ''master'' not found.', 16, 1) end
> > > RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> > > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > > WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
> > >
> > >
> > > GO|||"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:C6063C2C-D9F0-4082-BBDA-DE010216638E@.microsoft.com...
> Hi,
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
Another cmment: Don't do the above step.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Help with Restore

Folks,
I inherited a database that was not maintained correctly. I have learned
that the previous DBA had not created any SQL backup procedures, so they've
been running for at least 6 months with no safety net. Of course, some data
has been deleted by users and I need to find a way to restore the data as
they are screaming bloody murder.
I do, however, have backups of the data and log files which are from the
regular server backup. Can I create a new database of the correct name on
another server, and then restore those files to that database? Or is it
expecting the server to have the same name as before?
Any and all suggestions are appreciated.
Thanks,
George
George
You definitely can restore a database to onother server.
If you don't have the same files path look at WITH MOVE option in the BOL.
"George Hutto" <yougott@.bekidding.com> wrote in message
news:urzyAmZxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Folks,
> I inherited a database that was not maintained correctly. I have learned
> that the previous DBA had not created any SQL backup procedures, so
they've
> been running for at least 6 months with no safety net. Of course, some
data
> has been deleted by users and I need to find a way to restore the data as
> they are screaming bloody murder.
> I do, however, have backups of the data and log files which are from the
> regular server backup. Can I create a new database of the correct name on
> another server, and then restore those files to that database? Or is it
> expecting the server to have the same name as before?
> Any and all suggestions are appreciated.
> Thanks,
> George
>
|||Hi
Just to add, if SQL was shut down at the time of backup, you can use them,
otherwise you don't have anything as the files would have been in use.
Regards
Mike
"George Hutto" wrote:

> Folks,
> I inherited a database that was not maintained correctly. I have learned
> that the previous DBA had not created any SQL backup procedures, so they've
> been running for at least 6 months with no safety net. Of course, some data
> has been deleted by users and I need to find a way to restore the data as
> they are screaming bloody murder.
> I do, however, have backups of the data and log files which are from the
> regular server backup. Can I create a new database of the correct name on
> another server, and then restore those files to that database? Or is it
> expecting the server to have the same name as before?
> Any and all suggestions are appreciated.
> Thanks,
> George
>
>
|||Uri,
I don't have .BAK files, what I have are copies of the .DAT and .LOG files
from a particular date. I don't know if we are both talking about the same
sort of scenario.
George
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23UXz6pZxEHA.1524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> George
> You definitely can restore a database to onother server.
> If you don't have the same files path look at WITH MOVE option in the BOL.
>
>
> "George Hutto" <yougott@.bekidding.com> wrote in message
> news:urzyAmZxEHA.908@.TK2MSFTNGP11.phx.gbl...
learned[vbcol=seagreen]
> they've
> data
as[vbcol=seagreen]
on
>
|||Really Mike? That will make things darned difficult.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:C563452E-54A2-46F6-9E13-7DFE3332B7E1@.microsoft.com...[vbcol=seagreen]
> Hi
> Just to add, if SQL was shut down at the time of backup, you can use them,
> otherwise you don't have anything as the files would have been in use.
> Regards
> Mike
> "George Hutto" wrote:
learned[vbcol=seagreen]
they've[vbcol=seagreen]
data[vbcol=seagreen]
as[vbcol=seagreen]
on[vbcol=seagreen]

Help with Restore

Folks,
I inherited a database that was not maintained correctly. I have learned
that the previous DBA had not created any SQL backup procedures, so they've
been running for at least 6 months with no safety net. Of course, some data
has been deleted by users and I need to find a way to restore the data as
they are screaming bloody murder.
I do, however, have backups of the data and log files which are from the
regular server backup. Can I create a new database of the correct name on
another server, and then restore those files to that database? Or is it
expecting the server to have the same name as before?
Any and all suggestions are appreciated.
Thanks,
GeorgeGeorge
You definitely can restore a database to onother server.
If you don't have the same files path look at WITH MOVE option in the BOL.
"George Hutto" <yougott@.bekidding.com> wrote in message
news:urzyAmZxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Folks,
> I inherited a database that was not maintained correctly. I have learned
> that the previous DBA had not created any SQL backup procedures, so
they've
> been running for at least 6 months with no safety net. Of course, some
data
> has been deleted by users and I need to find a way to restore the data as
> they are screaming bloody murder.
> I do, however, have backups of the data and log files which are from the
> regular server backup. Can I create a new database of the correct name on
> another server, and then restore those files to that database? Or is it
> expecting the server to have the same name as before?
> Any and all suggestions are appreciated.
> Thanks,
> George
>|||Hi
Just to add, if SQL was shut down at the time of backup, you can use them,
otherwise you don't have anything as the files would have been in use.
Regards
Mike
"George Hutto" wrote:
> Folks,
> I inherited a database that was not maintained correctly. I have learned
> that the previous DBA had not created any SQL backup procedures, so they've
> been running for at least 6 months with no safety net. Of course, some data
> has been deleted by users and I need to find a way to restore the data as
> they are screaming bloody murder.
> I do, however, have backups of the data and log files which are from the
> regular server backup. Can I create a new database of the correct name on
> another server, and then restore those files to that database? Or is it
> expecting the server to have the same name as before?
> Any and all suggestions are appreciated.
> Thanks,
> George
>
>|||Uri,
I don't have .BAK files, what I have are copies of the .DAT and .LOG files
from a particular date. I don't know if we are both talking about the same
sort of scenario.
George
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23UXz6pZxEHA.1524@.TK2MSFTNGP09.phx.gbl...
> George
> You definitely can restore a database to onother server.
> If you don't have the same files path look at WITH MOVE option in the BOL.
>
>
> "George Hutto" <yougott@.bekidding.com> wrote in message
> news:urzyAmZxEHA.908@.TK2MSFTNGP11.phx.gbl...
> > Folks,
> >
> > I inherited a database that was not maintained correctly. I have
learned
> > that the previous DBA had not created any SQL backup procedures, so
> they've
> > been running for at least 6 months with no safety net. Of course, some
> data
> > has been deleted by users and I need to find a way to restore the data
as
> > they are screaming bloody murder.
> >
> > I do, however, have backups of the data and log files which are from the
> > regular server backup. Can I create a new database of the correct name
on
> > another server, and then restore those files to that database? Or is it
> > expecting the server to have the same name as before?
> >
> > Any and all suggestions are appreciated.
> >
> > Thanks,
> >
> > George
> >
> >
>|||Really Mike? That will make things darned difficult.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:C563452E-54A2-46F6-9E13-7DFE3332B7E1@.microsoft.com...
> Hi
> Just to add, if SQL was shut down at the time of backup, you can use them,
> otherwise you don't have anything as the files would have been in use.
> Regards
> Mike
> "George Hutto" wrote:
> > Folks,
> >
> > I inherited a database that was not maintained correctly. I have
learned
> > that the previous DBA had not created any SQL backup procedures, so
they've
> > been running for at least 6 months with no safety net. Of course, some
data
> > has been deleted by users and I need to find a way to restore the data
as
> > they are screaming bloody murder.
> >
> > I do, however, have backups of the data and log files which are from the
> > regular server backup. Can I create a new database of the correct name
on
> > another server, and then restore those files to that database? Or is it
> > expecting the server to have the same name as before?
> >
> > Any and all suggestions are appreciated.
> >
> > Thanks,
> >
> > George
> >
> >
> >

Help with Restore

Folks,
I inherited a database that was not maintained correctly. I have learned
that the previous DBA had not created any SQL backup procedures, so they've
been running for at least 6 months with no safety net. Of course, some data
has been deleted by users and I need to find a way to restore the data as
they are screaming bloody murder.
I do, however, have backups of the data and log files which are from the
regular server backup. Can I create a new database of the correct name on
another server, and then restore those files to that database? Or is it
expecting the server to have the same name as before?
Any and all suggestions are appreciated.
Thanks,
GeorgeGeorge
You definitely can restore a database to onother server.
If you don't have the same files path look at WITH MOVE option in the BOL.
"George Hutto" <yougott@.bekidding.com> wrote in message
news:urzyAmZxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Folks,
> I inherited a database that was not maintained correctly. I have learned
> that the previous DBA had not created any SQL backup procedures, so
they've
> been running for at least 6 months with no safety net. Of course, some
data
> has been deleted by users and I need to find a way to restore the data as
> they are screaming bloody murder.
> I do, however, have backups of the data and log files which are from the
> regular server backup. Can I create a new database of the correct name on
> another server, and then restore those files to that database? Or is it
> expecting the server to have the same name as before?
> Any and all suggestions are appreciated.
> Thanks,
> George
>|||Hi
Just to add, if SQL was shut down at the time of backup, you can use them,
otherwise you don't have anything as the files would have been in use.
Regards
Mike
"George Hutto" wrote:

> Folks,
> I inherited a database that was not maintained correctly. I have learned
> that the previous DBA had not created any SQL backup procedures, so they'v
e
> been running for at least 6 months with no safety net. Of course, some da
ta
> has been deleted by users and I need to find a way to restore the data as
> they are screaming bloody murder.
> I do, however, have backups of the data and log files which are from the
> regular server backup. Can I create a new database of the correct name on
> another server, and then restore those files to that database? Or is it
> expecting the server to have the same name as before?
> Any and all suggestions are appreciated.
> Thanks,
> George
>
>|||Uri,
I don't have .BAK files, what I have are copies of the .DAT and .LOG files
from a particular date. I don't know if we are both talking about the same
sort of scenario.
George
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23UXz6pZxEHA.1524@.TK2MSFTNGP09.phx.gbl...
> George
> You definitely can restore a database to onother server.
> If you don't have the same files path look at WITH MOVE option in the BOL.
>
>
> "George Hutto" <yougott@.bekidding.com> wrote in message
> news:urzyAmZxEHA.908@.TK2MSFTNGP11.phx.gbl...
learned[vbcol=seagreen]
> they've
> data
as[vbcol=seagreen]
on[vbcol=seagreen]
>|||Really Mike? That will make things darned difficult.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:C563452E-54A2-46F6-9E13-7DFE3332B7E1@.microsoft.com...[vbcol=seagreen]
> Hi
> Just to add, if SQL was shut down at the time of backup, you can use them,
> otherwise you don't have anything as the files would have been in use.
> Regards
> Mike
> "George Hutto" wrote:
>
learned[vbcol=seagreen]
they've[vbcol=seagreen]
data[vbcol=seagreen]
as[vbcol=seagreen]
on[vbcol=seagreen]

Help with Recovery

My W2K Server crashed during a long update operation and now the
database is marked Suspect. I have no backup.
Is there any way I can revive the database from the .mdf and .ldf files
?
Please help.
try
==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============
<Iyengar.Raghu@.gmail.com> wrote in message
news:1158843367.407948.245680@.b28g2000cwb.googlegr oups.com...
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.
>
|||try sp_resetstatus
Iyengar.Raghu@.gmail.com wrote:
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.
|||Hi,
DBCC REBUILD_LOG is an undocumented method and this will create a new log
file. This might cause some inconsistancy in data.
Since you do not have the backup you can an go with this method as Uri
pointed out. But please do a perfect backup strategy
(Full db backup and Transaction log backup) to ensure that you could
recover the database at any point.
THanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uKBI24X3GHA.3508@.TK2MSFTNGP03.phx.gbl...
> try
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system
> tables:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on
> the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegr oups.com...
>
|||Thank you Uri.
I followed your process step by step the DBCC Check returned a few
warnings but I did not notice anything fatal.
I have completed all the 7 steps but the database is still in the
suspect status.
Is there anything else I should be doing ?
Raghu
Uri Dimant wrote:[vbcol=seagreen]
> try
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system tables:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegr oups.com...
|||Thank you all. I worked.
bazoorg@.gmail.com wrote:[vbcol=seagreen]
> try sp_resetstatus
>
> Iyengar.Raghu@.gmail.com wrote:

Help with Recovery

My W2K Server crashed during a long update operation and now the
database is marked Suspect. I have no backup.
Is there any way I can revive the database from the .mdf and .ldf files
?
Please help.try
==========1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============<Iyengar.Raghu@.gmail.com> wrote in message
news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.
>|||try sp_resetstatus
Iyengar.Raghu@.gmail.com wrote:
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.|||Hi,
DBCC REBUILD_LOG is an undocumented method and this will create a new log
file. This might cause some inconsistancy in data.
Since you do not have the backup you can an go with this method as Uri
pointed out. But please do a perfect backup strategy
(Full db backup and Transaction log backup) to ensure that you could
recover the database at any point.
THanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uKBI24X3GHA.3508@.TK2MSFTNGP03.phx.gbl...
> try
> ==========> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system
> tables:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on
> the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
>> My W2K Server crashed during a long update operation and now the
>> database is marked Suspect. I have no backup.
>> Is there any way I can revive the database from the .mdf and .ldf files
>> ?
>> Please help.
>|||Thank you Uri.
I followed your process step by step the DBCC Check returned a few
warnings but I did not notice anything fatal.
I have completed all the 7 steps but the database is still in the
suspect status.
Is there anything else I should be doing ?
Raghu
Uri Dimant wrote:
> try
> ==========> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system tables:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
> > My W2K Server crashed during a long update operation and now the
> > database is marked Suspect. I have no backup.
> >
> > Is there any way I can revive the database from the .mdf and .ldf files
> > ?
> >
> > Please help.
> >|||Thank you all. I worked.
bazoorg@.gmail.com wrote:
> try sp_resetstatus
>
> Iyengar.Raghu@.gmail.com wrote:
> > My W2K Server crashed during a long update operation and now the
> > database is marked Suspect. I have no backup.
> >
> > Is there any way I can revive the database from the .mdf and .ldf files
> > ?
> >
> > Please help.

Help with Recovery

My W2K Server crashed during a long update operation and now the
database is marked Suspect. I have no backup.
Is there any way I can revive the database from the .mdf and .ldf files
?
Please help.try
==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============
<Iyengar.Raghu@.gmail.com> wrote in message
news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.
>|||try sp_resetstatus
Iyengar.Raghu@.gmail.com wrote:
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.|||Hi,
DBCC REBUILD_LOG is an undocumented method and this will create a new log
file. This might cause some inconsistancy in data.
Since you do not have the backup you can an go with this method as Uri
pointed out. But please do a perfect backup strategy
(Full db backup and Transaction log backup) to ensure that you could
recover the database at any point.
THanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uKBI24X3GHA.3508@.TK2MSFTNGP03.phx.gbl...
> try
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system
> tables:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on
> the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
>|||Thank you Uri.
I followed your process step by step the DBCC Check returned a few
warnings but I did not notice anything fatal.
I have completed all the 7 steps but the database is still in the
suspect status.
Is there anything else I should be doing ?
Raghu
Uri Dimant wrote:[vbcol=seagreen]
> try
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system table
s:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on th
e
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...|||Thank you all. I worked.
bazoorg@.gmail.com wrote:[vbcol=seagreen]
> try sp_resetstatus
>
> Iyengar.Raghu@.gmail.com wrote:

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail