Basic Question on SQL Server 2005 Backup Theory

I am replicating a large number of databases (the size changes
dynamically) from one sql server to another, all pragmatically.
Once a week, I do a full backup and restore it on the destination.
All other times I am just backing up the log files, copying them over,
and restoring them. Basically a glorified log ship.
About once a week, I get various exceptions stating the following:
********
System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
because there is no current database backup.
********
My question is: with this scenario, how often do I have to actually
perform a full backup? Does SQL Server 2005 really care?
Thanks a lot,
Michael Gorsuch
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
Only the very first time. Assuming you ship all transaction log backups, and don't do anything with
the originating database that break the log backup sequence (like put it in simple recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1177597505.788472.212590@.t39g2000prd.googlegr oups.com...
>I am replicating a large number of databases (the size changes
> dynamically) from one sql server to another, all pragmatically.
> Once a week, I do a full backup and restore it on the destination.
> All other times I am just backing up the log files, copying them over,
> and restoring them. Basically a glorified log ship.
> About once a week, I get various exceptions stating the following:
> ********
> System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
> because there is no current database backup.
> ********
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
> Thanks a lot,
> Michael Gorsuch
>

Basic Question on SQL Server 2005 Backup Theory

I am replicating a large number of databases (the size changes
dynamically) from one sql server to another, all pragmatically.
Once a week, I do a full backup and restore it on the destination.
All other times I am just backing up the log files, copying them over,
and restoring them. Basically a glorified log ship.
About once a week, I get various exceptions stating the following:
********
System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
because there is no current database backup.
********
My question is: with this scenario, how often do I have to actually
perform a full backup? Does SQL Server 2005 really care?
Thanks a lot,
Michael Gorsuch> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
Only the very first time. Assuming you ship all transaction log backups, and
don't do anything with
the originating database that break the log backup sequence (like put it in
simple recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1177597505.788472.212590@.t39g2000prd.googlegroups.com...
>I am replicating a large number of databases (the size changes
> dynamically) from one sql server to another, all pragmatically.
> Once a week, I do a full backup and restore it on the destination.
> All other times I am just backing up the log files, copying them over,
> and restoring them. Basically a glorified log ship.
> About once a week, I get various exceptions stating the following:
> ********
> System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
> because there is no current database backup.
> ********
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
> Thanks a lot,
> Michael Gorsuch
>

Basic Question on SQL Server 2005 Backup Theory

I am replicating a large number of databases (the size changes
dynamically) from one sql server to another, all pragmatically.
Once a week, I do a full backup and restore it on the destination.
All other times I am just backing up the log files, copying them over,
and restoring them. Basically a glorified log ship.
About once a week, I get various exceptions stating the following:
********
System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
because there is no current database backup.
********
My question is: with this scenario, how often do I have to actually
perform a full backup? Does SQL Server 2005 really care?
Thanks a lot,
Michael Gorsuch> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
Only the very first time. Assuming you ship all transaction log backups, and don't do anything with
the originating database that break the log backup sequence (like put it in simple recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1177597505.788472.212590@.t39g2000prd.googlegroups.com...
>I am replicating a large number of databases (the size changes
> dynamically) from one sql server to another, all pragmatically.
> Once a week, I do a full backup and restore it on the destination.
> All other times I am just backing up the log files, copying them over,
> and restoring them. Basically a glorified log ship.
> About once a week, I get various exceptions stating the following:
> ********
> System.Data.SqlClient.SqlException: BACKUP LOG cannot be performed
> because there is no current database backup.
> ********
> My question is: with this scenario, how often do I have to actually
> perform a full backup? Does SQL Server 2005 really care?
> Thanks a lot,
> Michael Gorsuch
>sql

Basic backup question

I'm doing a full backup of a SQL Server database with SSMS. When I try to
restore it I get the following exception:
*******
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "MYBACKUP" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*******
What is this message?
Should I backup the log before backing up the database?
Is the database backup enought to recover data?
Thanks!
Gaspar
Yes. I have schedules to backup the transaction log every 6 hours. I don't
know if this is a good practice.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>A couple of questions first:
> Are you doing regular transaction log backups?
> If not, is there a reason for you to have the database in full recovery
> model?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>
|||So, this reminder "The tail of the log for the database "MYBACKUP" has not
been backed up" is just letting me know that after I backuped up the data,
some changes where done.
Suppose I don't care about this changes (my compain can "recover" from a
one-day data loss):
1) Can I restore by only having the database backup?
2) How to avoid this message from appearing?
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
> This we can't answer since we don't know how much data your organization
> can tolerate to lose in case of a disaster...
> Anyhow, I do now know you do log backups. OK, consider below:
> 1 Backup db
> 2 Users doing modifications
> 3 Backup log
> 4 Users doing modifications
> 5 Backup log
> 6 Users doing modifications
> 7 Backup log
> 8 Users doing modifications
> And you now want to do RESTORE. But what about the modifications in step
> 8? This will be lost if you perform the restore. The error is SQL Server's
> way of reminding you that you probably want to do a log backup first:
> 9 Backup log
> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If
> you *want* to do RESTORE *without* doing this last log backup, you can use
> the REPLACE parameter for the RESTORE command.
> This behavior (the "reminder") is new for 2005, btw.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
|||This may help, it helped me.
http://www.associatedcontent.com/article/267055/how_to_setup_sql_server_2005_backups.html
On Dec 21, 6:44 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> Yes, the message states that after your lastbackup(database or logbackup) there has been
> modifications in the database.
>
> Yes. Again, consider below:
> 1Backupdb
> 2 Users doing modifications
> 3Backuplog
> 4 Users doing modifications
> 5Backuplog
> 6 Users doing modifications
> 7Backuplog
> 8 Users doing modifications
> You can restore from only 1. Of course you will lose all modifications done after 1.
>
> Specify the REPLACE option for your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Gaspar" <nore...@.nospamplease.com> wrote in messagenews:ux4xwO9QIHA.1164@.TK2MSFTNGP02.phx.gbl. ..
>
>
>
>
>
>
>
>
>

Basic backup question

I'm doing a full backup of a SQL Server database with SSMS. When I try to
restore it I get the following exception:
*******
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "MYBACKUP" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*******
What is this message?
Should I backup the log before backing up the database?
Is the database backup enought to recover data?
Thanks!
GasparA couple of questions first:
Are you doing regular transaction log backups?
If not, is there a reason for you to have the database in full recovery model?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
> I'm doing a full backup of a SQL Server database with SSMS. When I try to restore it I get the
> following exception:
> *******
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "MYBACKUP" has not been backed up. Use BACKUP LOG WITH
> NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or
> WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> *******
> What is this message?
> Should I backup the log before backing up the database?
> Is the database backup enought to recover data?
> Thanks!
> Gaspar
>|||Yes. I have schedules to backup the transaction log every 6 hours. I don't
know if this is a good practice.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>A couple of questions first:
> Are you doing regular transaction log backups?
> If not, is there a reason for you to have the database in full recovery
> model?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try to
>> restore it I get the following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up.
>> Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you
>> do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the
>> RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>|||> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a good
> practice.
This we can't answer since we don't know how much data your organization can tolerate to lose in
case of a disaster...
Anyhow, I do now know you do log backups. OK, consider below:
1 Backup db
2 Users doing modifications
3 Backup log
4 Users doing modifications
5 Backup log
6 Users doing modifications
7 Backup log
8 Users doing modifications
And you now want to do RESTORE. But what about the modifications in step 8? This will be lost if you
perform the restore. The error is SQL Server's way of reminding you that you probably want to do a
log backup first:
9 Backup log
When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If you *want* to do RESTORE
*without* doing this last log backup, you can use the REPLACE parameter for the RESTORE command.
This behavior (the "reminder") is new for 2005, btw.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a good
> practice.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>>A couple of questions first:
>> Are you doing regular transaction log backups?
>> If not, is there a reason for you to have the database in full recovery model?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try to restore it I get the
>> following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up. Use BACKUP LOG WITH
>> NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE
>> or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>>
>|||So, this reminder "The tail of the log for the database "MYBACKUP" has not
been backed up" is just letting me know that after I backuped up the data,
some changes where done.
Suppose I don't care about this changes (my compain can "recover" from a
one-day data loss):
1) Can I restore by only having the database backup?
2) How to avoid this message from appearing?
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I
>> don't know if this is a good practice.
> This we can't answer since we don't know how much data your organization
> can tolerate to lose in case of a disaster...
> Anyhow, I do now know you do log backups. OK, consider below:
> 1 Backup db
> 2 Users doing modifications
> 3 Backup log
> 4 Users doing modifications
> 5 Backup log
> 6 Users doing modifications
> 7 Backup log
> 8 Users doing modifications
> And you now want to do RESTORE. But what about the modifications in step
> 8? This will be lost if you perform the restore. The error is SQL Server's
> way of reminding you that you probably want to do a log backup first:
> 9 Backup log
> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If
> you *want* to do RESTORE *without* doing this last log backup, you can use
> the REPLACE parameter for the RESTORE command.
> This behavior (the "reminder") is new for 2005, btw.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I
>> don't know if this is a good practice.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>>A couple of questions first:
>> Are you doing regular transaction log backups?
>> If not, is there a reason for you to have the database in full recovery
>> model?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try
>> to restore it I get the following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up.
>> Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work
>> you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of
>> the RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>>
>>
>|||> So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> letting me know that after I backuped up the data, some changes where done.
Yes, the message states that after your last backup (database or log backup) there has been
modifications in the database.
> Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> 1) Can I restore by only having the database backup?
Yes. Again, consider below:
1 Backup db
2 Users doing modifications
3 Backup log
4 Users doing modifications
5 Backup log
6 Users doing modifications
7 Backup log
8 Users doing modifications
You can restore from only 1. Of course you will lose all modifications done after 1.
> 2) How to avoid this message from appearing?
Specify the REPLACE option for your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:ux4xwO9QIHA.1164@.TK2MSFTNGP02.phx.gbl...
> So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> letting me know that after I backuped up the data, some changes where done.
> Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> 1) Can I restore by only having the database backup?
> 2) How to avoid this message from appearing?
> Thanks again
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a
>> good practice.
>> This we can't answer since we don't know how much data your organization can tolerate to lose in
>> case of a disaster...
>> Anyhow, I do now know you do log backups. OK, consider below:
>> 1 Backup db
>> 2 Users doing modifications
>> 3 Backup log
>> 4 Users doing modifications
>> 5 Backup log
>> 6 Users doing modifications
>> 7 Backup log
>> 8 Users doing modifications
>> And you now want to do RESTORE. But what about the modifications in step 8? This will be lost if
>> you perform the restore. The error is SQL Server's way of reminding you that you probably want to
>> do a log backup first:
>> 9 Backup log
>> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If you *want* to do
>> RESTORE *without* doing this last log backup, you can use the REPLACE parameter for the RESTORE
>> command.
>> This behavior (the "reminder") is new for 2005, btw.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a
>> good practice.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>>A couple of questions first:
>> Are you doing regular transaction log backups?
>> If not, is there a reason for you to have the database in full recovery model?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try to restore it I get the
>> following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up. Use BACKUP LOG WITH
>> NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE
>> or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>>
>>
>>
>|||This may help, it helped me.
http://www.associatedcontent.com/article/267055/how_to_setup_sql_server_2005_backups.html
On Dec 21, 6:44 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> > letting me know that after I backuped up the data, some changes where done.
> Yes, the message states that after your lastbackup(database or logbackup) there has been
> modifications in the database.
> > Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> > 1) Can I restore by only having the databasebackup?
> Yes. Again, consider below:
> 1Backupdb
> 2 Users doing modifications
> 3Backuplog
> 4 Users doing modifications
> 5Backuplog
> 6 Users doing modifications
> 7Backuplog
> 8 Users doing modifications
> You can restore from only 1. Of course you will lose all modifications done after 1.
> > 2) How to avoid this message from appearing?
> Specify the REPLACE option for your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Gaspar" <nore...@.nospamplease.com> wrote in messagenews:ux4xwO9QIHA.1164@.TK2MSFTNGP02.phx.gbl...
> > So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> > letting me know that after I backuped up the data, some changes where done.
> > Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> > 1) Can I restore by only having the databasebackup?
> > 2) How to avoid this message from appearing?
> > Thanks again
> > "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in message
> >news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
> >> Yes. I have schedules tobackupthe transaction log every 6 hours. I don't know if this is a
> >> good practice.
> >> This we can't answer since we don't know how much data your organization can tolerate to lose in
> >> case of a disaster...
> >> Anyhow, I do now know you do log backups. OK, consider below:
> >> 1Backupdb
> >> 2 Users doing modifications
> >> 3Backuplog
> >> 4 Users doing modifications
> >> 5Backuplog
> >> 6 Users doing modifications
> >> 7Backuplog
> >> 8 Users doing modifications
> >> And you now want to do RESTORE. But what about the modifications in step 8? This will be lost if
> >> you perform the restore. The error is SQL Server's way of reminding you that you probably want to
> >> do a logbackupfirst:
> >> 9Backuplog
> >> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If you *want* to do
> >> RESTORE *without* doing this last logbackup, you can use the REPLACE parameter for the RESTORE
> >> command.
> >> This behavior (the "reminder") is new for 2005, btw.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Gaspar" <nore...@.nospamplease.com> wrote in message
> >>news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
> >> Yes. I have schedules tobackupthe transaction log every 6 hours. I don't know if this is a
> >> good practice.
> >> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in message
> >>news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
> >>A couple of questions first:
> >> Are you doing regular transaction log backups?
> >> If not, is there a reason for you to have the database in full recovery model?
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Gaspar" <nore...@.nospamplease.com> wrote in message
> >>news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
> >> I'm doing a fullbackupof a SQL Server database with SSMS. When I try to restore it I get the
> >> following exception:
> >> *******
> >> Msg 3159, Level 16, State 1, Line 1
> >> The tail of the log for the database "MYBACKUP" has not been backed up. UseBACKUPLOG WITH
> >> NORECOVERY tobackupthe log if it contains work you do not want to lose. Use the WITH REPLACE
> >> or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
> >> Msg 3013, Level 16, State 1, Line 1
> >> RESTORE DATABASE is terminating abnormally.
> >> *******
> >> What is this message?
> >> Should Ibackupthe log before backing up the database?
> >> Is the databasebackupenought to recover data?
> >> Thanks!
> >> Gaspar

Basic backup question

I'm doing a full backup of a SQL Server database with SSMS. When I try to
restore it I get the following exception:
*******
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "MYBACKUP" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*******
What is this message?
Should I backup the log before backing up the database?
Is the database backup enought to recover data?
Thanks!
GasparA couple of questions first:
Are you doing regular transaction log backups?
If not, is there a reason for you to have the database in full recovery mode
l?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.
gbl...
> I'm doing a full backup of a SQL Server database with SSMS. When I try to
restore it I get the
> following exception:
> *******
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "MYBACKUP" has not been backed up. Us
e BACKUP LOG WITH
> NORECOVERY to backup the log if it contains work you do not want to lose.
Use the WITH REPLACE or
> WITH STOPAT clause of the RESTORE statement to just overwrite the contents
of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> *******
> What is this message?
> Should I backup the log before backing up the database?
> Is the database backup enought to recover data?
> Thanks!
> Gaspar
>|||Yes. I have schedules to backup the transaction log every 6 hours. I don't
know if this is a good practice.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>A couple of questions first:
> Are you doing regular transaction log backups?
> If not, is there a reason for you to have the database in full recovery
> model?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>|||> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is
a good
> practice.
This we can't answer since we don't know how much data your organization can
tolerate to lose in
case of a disaster...
Anyhow, I do now know you do log backups. OK, consider below:
1 Backup db
2 Users doing modifications
3 Backup log
4 Users doing modifications
5 Backup log
6 Users doing modifications
7 Backup log
8 Users doing modifications
And you now want to do RESTORE. But what about the modifications in step 8?
This will be lost if you
perform the restore. The error is SQL Server's way of reminding you that you
probably want to do a
log backup first:
9 Backup log
When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If yo
u *want* to do RESTORE
*without* doing this last log backup, you can use the REPLACE parameter for
the RESTORE command.
This behavior (the "reminder") is new for 2005, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.
gbl...
> Yes. I have schedules to backup the transaction log every 6 hours. I don't
know if this is a good
> practice.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>|||So, this reminder "The tail of the log for the database "MYBACKUP" has not
been backed up" is just letting me know that after I backuped up the data,
some changes where done.
Suppose I don't care about this changes (my compain can "recover" from a
one-day data loss):
1) Can I restore by only having the database backup?
2) How to avoid this message from appearing?
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
> This we can't answer since we don't know how much data your organization
> can tolerate to lose in case of a disaster...
> Anyhow, I do now know you do log backups. OK, consider below:
> 1 Backup db
> 2 Users doing modifications
> 3 Backup log
> 4 Users doing modifications
> 5 Backup log
> 6 Users doing modifications
> 7 Backup log
> 8 Users doing modifications
> And you now want to do RESTORE. But what about the modifications in step
> 8? This will be lost if you perform the restore. The error is SQL Server's
> way of reminding you that you probably want to do a log backup first:
> 9 Backup log
> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If
> you *want* to do RESTORE *without* doing this last log backup, you can use
> the REPLACE parameter for the RESTORE command.
> This behavior (the "reminder") is new for 2005, btw.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
>|||> So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up"
is just
> letting me know that after I backuped up the data, some changes where done.[/vbcol
]
Yes, the message states that after your last backup (database or log backup)
there has been
modifications in the database.
[vbcol=seagreen]
> Suppose I don't care about this changes (my compain can "recover" from a o
ne-day data loss):
> 1) Can I restore by only having the database backup?
Yes. Again, consider below:
1 Backup db
2 Users doing modifications
3 Backup log
4 Users doing modifications
5 Backup log
6 Users doing modifications
7 Backup log
8 Users doing modifications
You can restore from only 1. Of course you will lose all modifications done
after 1.

> 2) How to avoid this message from appearing?
Specify the REPLACE option for your RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:ux4xwO9QIHA.1164@.TK2MSFTNGP02.phx.
gbl...
> So, this reminder "The tail of the log for the database "MYBACKUP" has not
been backed up" is just
> letting me know that after I backuped up the data, some changes where done
.
> Suppose I don't care about this changes (my compain can "recover" from a o
ne-day data loss):
> 1) Can I restore by only having the database backup?
> 2) How to avoid this message from appearing?
> Thanks again
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
>|||This may help, it helped me.
http://www.associatedcontent.com/ar...
ps.html
On Dec 21, 6:44 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> Yes, the message states that after your lastbackup(database or logbackup)
there has been
> modifications in the database.
>
>
> Yes. Again, consider below:
> 1Backupdb
> 2 Users doing modifications
> 3Backuplog
> 4 Users doing modifications
> 5Backuplog
> 6 Users doing modifications
> 7Backuplog
> 8 Users doing modifications
> You can restore from only 1. Of course you will lose all modifications don
e after 1.
>
> Specify the REPLACE option for your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Gaspar" <nore...@.nospamplease.com> wrote in messagenews:ux4xwO9QIHA.1164@.
TK2MSFTNGP02.phx.gbl...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Based on backup and recovery

hello,
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..

as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).

Base Functionality Script

Hello, everyone:
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Thanks
ZYTA "base functionality script" is a batch of code that can be used to quickly demonstrate the successful functioning of the database from a specific application's perspective. Most commonly this is a .sql file with batched SQL commands run into the server from ISQL. For other applications, a .bat file is more appropriate because it can contain BCP and ISQL commands. This base functionality script is very application-specific, and can take many different forms. For example, on a Decision Support/Reporting system, the script may merely be a copy of a couple of your key reporting queries; for an online transaction processing (OLTP) application it may be the execution of a batch of stored procedures to execute INSERT, UPDATE, and DELETE statements. The goal is to confirm, from a gross perspective, that everything is working as intended. The base functionality script provides a nice tool for the SA or DBA to be able to see that the database is back in a viable state, without depending on the end users for verification.

Bakup dosen't delete files

When using SQLserver to take backup throw a maintance plan
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill at
>MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>

BAK, TRN files

Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagine
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im going
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long to keep them, and with
this, consider hoe you store these files on tape.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have been
> so far moving the BAK and TRN files to different disks but as you can imagine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as your
> only shooting yourself inthe foot should the db go down, but at this Im going
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks

BAK, TRN files

Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagin
e
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im goin
g
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long
to keep them, and with
this, consider hoe you store these files on tape.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have bee
n
> so far moving the BAK and TRN files to different disks but as you can imag
ine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10g
bs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as yo
ur
> only shooting yourself inthe foot should the db go down, but at this Im go
ing
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
Laurence
Hi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegr oups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>
|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegr oups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>
|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
David Portas
SQL Server MVP

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
David Portas
SQL Server MVP
--

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
--
David Portas
SQL Server MVP
--

Backward Compatability in Yukon using backup devices?

My peers have informed me that I should no longer code my backup routine
using a backup alias via sp_addumpdevice because it is 1) Provided only for
backward compatability and 2) Will not be supported in the future.
Should I not be using a backup alias for these reasons? Yukon provides the
commands but no where have I seen where it's only there to support backward
compatability and that it is targeted to be phased out. Can someone show me
this?
Thanks.
-Cqlboy
I know nothing of dump devices going away any time soon.
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
> My peers have informed me that I should no longer code my backup routine
> using a backup alias via sp_addumpdevice because it is 1) Provided only
> for
> backward compatability and 2) Will not be supported in the future.
> Should I not be using a backup alias for these reasons? Yukon provides
> the
> commands but no where have I seen where it's only there to support
> backward
> compatability and that it is targeted to be phased out. Can someone show
> me
> this?
> Thanks.
> -Cqlboy
|||Thanx. Thought so. My fellow peers
are misinformed.
-Cqlboy
"Andrew J. Kelly" wrote:

> I know nothing of dump devices going away any time soon.
> --
> Andrew J. Kelly SQL MVP
>
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
>
>
|||I'm with Andrew. Ask your colleagues for a reference. It is easy to make claims you can't back up...
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:B4044417-68A3-401F-B1AF-7BCE37AE1BC9@.microsoft.com...[vbcol=seagreen]
> Thanx. Thought so. My fellow peers
> are misinformed.
> -Cqlboy
> "Andrew J. Kelly" wrote:

Backward Compatability in Yukon using backup devices?

My peers have informed me that I should no longer code my backup routine
using a backup alias via sp_addumpdevice because it is 1) Provided only for
backward compatability and 2) Will not be supported in the future.
Should I not be using a backup alias for these reasons? Yukon provides the
commands but no where have I seen where it's only there to support backward
compatability and that it is targeted to be phased out. Can someone show me
this?
Thanks.
-CqlboyI know nothing of dump devices going away any time soon.
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
> My peers have informed me that I should no longer code my backup routine
> using a backup alias via sp_addumpdevice because it is 1) Provided only
> for
> backward compatability and 2) Will not be supported in the future.
> Should I not be using a backup alias for these reasons? Yukon provides
> the
> commands but no where have I seen where it's only there to support
> backward
> compatability and that it is targeted to be phased out. Can someone show
> me
> this?
> Thanks.
> -Cqlboy|||Thanx. Thought so. My fellow peers
are misinformed.
-Cqlboy
"Andrew J. Kelly" wrote:

> I know nothing of dump devices going away any time soon.
> --
> Andrew J. Kelly SQL MVP
>
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
>
>|||I'm with Andrew. Ask your colleagues for a reference. It is easy to make cla
ims you can't back up...
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:B4044417-68A3-401F-B1AF-7BCE37AE1BC9@.microsoft.com...[vbcol=seagreen]
> Thanx. Thought so. My fellow peers
> are misinformed.
> -Cqlboy
> "Andrew J. Kelly" wrote:
>

Backward Compatability in Yukon using backup devices?

My peers have informed me that I should no longer code my backup routine
using a backup alias via sp_addumpdevice because it is 1) Provided only for
backward compatability and 2) Will not be supported in the future.
Should I not be using a backup alias for these reasons? Yukon provides the
commands but no where have I seen where it's only there to support backward
compatability and that it is targeted to be phased out. Can someone show me
this?
Thanks.
-CqlboyI know nothing of dump devices going away any time soon.
--
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
> My peers have informed me that I should no longer code my backup routine
> using a backup alias via sp_addumpdevice because it is 1) Provided only
> for
> backward compatability and 2) Will not be supported in the future.
> Should I not be using a backup alias for these reasons? Yukon provides
> the
> commands but no where have I seen where it's only there to support
> backward
> compatability and that it is targeted to be phased out. Can someone show
> me
> this?
> Thanks.
> -Cqlboy|||Thanx. Thought so. My fellow peers
are misinformed.
-Cqlboy
"Andrew J. Kelly" wrote:
> I know nothing of dump devices going away any time soon.
> --
> Andrew J. Kelly SQL MVP
>
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
> > My peers have informed me that I should no longer code my backup routine
> > using a backup alias via sp_addumpdevice because it is 1) Provided only
> > for
> > backward compatability and 2) Will not be supported in the future.
> >
> > Should I not be using a backup alias for these reasons? Yukon provides
> > the
> > commands but no where have I seen where it's only there to support
> > backward
> > compatability and that it is targeted to be phased out. Can someone show
> > me
> > this?
> >
> > Thanks.
> >
> > -Cqlboy
>
>|||I'm with Andrew. Ask your colleagues for a reference. It is easy to make claims you can't back up...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:B4044417-68A3-401F-B1AF-7BCE37AE1BC9@.microsoft.com...
> Thanx. Thought so. My fellow peers
> are misinformed.
> -Cqlboy
> "Andrew J. Kelly" wrote:
>> I know nothing of dump devices going away any time soon.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
>> news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
>> > My peers have informed me that I should no longer code my backup routine
>> > using a backup alias via sp_addumpdevice because it is 1) Provided only
>> > for
>> > backward compatability and 2) Will not be supported in the future.
>> >
>> > Should I not be using a backup alias for these reasons? Yukon provides
>> > the
>> > commands but no where have I seen where it's only there to support
>> > backward
>> > compatability and that it is targeted to be phased out. Can someone show
>> > me
>> > this?
>> >
>> > Thanks.
>> >
>> > -Cqlboy
>>

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
--
Paul G
Software engineer.Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
--
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > Hi,
> > I was just wondering if you build a database in sql2005 and then save the
> > file(backup dbase) would it work to perform a restore from this file with
> > sql2000?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||oops, someone asked the same question just a few posts earlier!
--
Paul G
Software engineer.
"Paul" wrote:
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
> > No.
> >
> > Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> > will restore to 2000 and 2005, but not 2008 (forthcoming).
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > > Hi,
> > > I was just wondering if you build a database in sql2005 and then save the
> > > file(backup dbase) would it work to perform a restore from this file with
> > > sql2000?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
Paul G
Software engineer.
Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
Paul G
Software engineer.
"Geoff N. Hiten" wrote:

> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
>
|||oops, someone asked the same question just a few posts earlier!
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:

Backup-to-file SQL2k Question/NT Tape Backups

Dear All,
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>