Backup to Network Drive: level

Showing posts with label level. Show all posts

Sunday, March 25, 2012

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

Monday, March 19, 2012

Bad page ID

<<Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:6195408) has invalid
PFS_PAGE page header values.
Type is 0. Check type, object ID and page ID on the page.>>
The above error seems to indicate that I can check the
header values of this page.
How do I do that?
Many thanks.The message should really be "Have your support provider check the ..."
You can search for DBCC PAGE on the Net.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Karl" <kekurzenknabe@.hotmail.com> wrote in message news:2f6f01c373d6$f67b09c0$a601280a@.phx.gbl...
> <<Server: Msg 8946, Level 16, State 12, Line 2
> Table error: Allocation page (1:6195408) has invalid
> PFS_PAGE page header values.
> Type is 0. Check type, object ID and page ID on the page.>>
> The above error seems to indicate that I can check the
> header values of this page.
> How do I do that?
> Many thanks.|||Looks like you've got a corrupt PFS page. This problem cannot be
automatically repaired (even with REPAIR_ALLOW_DATA_LOSS) so you need to
restore your database from a backup after working out why the problem
occurred (check your event logs, the errorlog, run hardware diagnostics) -
or call PSS to help you.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eH7TZYhdDHA.416@.tk2msftngp13.phx.gbl...
> The message should really be "Have your support provider check the ..."
> You can search for DBCC PAGE on the Net.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Karl" <kekurzenknabe@.hotmail.com> wrote in message
news:2f6f01c373d6$f67b09c0$a601280a@.phx.gbl...
> > <<Server: Msg 8946, Level 16, State 12, Line 2
> > Table error: Allocation page (1:6195408) has invalid
> > PFS_PAGE page header values.
> > Type is 0. Check type, object ID and page ID on the page.>>
> >
> > The above error seems to indicate that I can check the
> > header values of this page.
> >
> > How do I do that?
> >
> > Many thanks.
>

Sunday, February 19, 2012

BACKUP/RESTORE compatibility between 2000/2005

Hi
If I take a backup of a database with compatibility level 80 from SQL Server
2005, will I be able to restore that backup into a SQL Server 2000
installation?
Thanks
MarkNo. Compatibility level is for client connection behavior. On-disk it is
still SQL 2005.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi
> If I take a backup of a database with compatibility level 80 from SQL
> Server 2005, will I be able to restore that backup into a SQL Server 2000
> installation?
> Thanks
> Mark
>|||Oh. Boo.
Side-by-side installation for me then.
Thanks for the speedy response.
Mark|||What if I detach a 80 compatibility database on a Sql2005 server, can I
attach on a SQL2000 server?
We are running some compatibility testing against a vendors database and
they are not facilitating our migration to Sql2005. So we need an ability to
get them a zipped up copy of a 80 compatibility, Sql 2000 backup but want to
run in Sql 2005 so we can take advantage of database mirroring. (yes I know
it won't be supported by MS in production for another month or two or three,
that works with our migration schedule)
thanks
"Geoff N. Hiten" wrote:
> No. Compatibility level is for client connection behavior. On-disk it is
> still SQL 2005.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
> news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > If I take a backup of a database with compatibility level 80 from SQL
> > Server 2005, will I be able to restore that backup into a SQL Server 2000
> > installation?
> >
> > Thanks
> > Mark
> >
>
>|||No, once it is attached or restored to a 2005 instance it can not go back
intact. The only option is to keep a 2000 db that is empty and import the
data from 2005 to 2000. That is assuming you haven't changed the schema or
used any 2005 features such as new datatypes etc.
--
Andrew J. Kelly SQL MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:3F5102B7-DB1D-4367-8B0B-C5D69B758498@.microsoft.com...
> What if I detach a 80 compatibility database on a Sql2005 server, can I
> attach on a SQL2000 server?
> We are running some compatibility testing against a vendors database and
> they are not facilitating our migration to Sql2005. So we need an ability
> to
> get them a zipped up copy of a 80 compatibility, Sql 2000 backup but want
> to
> run in Sql 2005 so we can take advantage of database mirroring. (yes I
> know
> it won't be supported by MS in production for another month or two or
> three,
> that works with our migration schedule)
> thanks
> "Geoff N. Hiten" wrote:
>> No. Compatibility level is for client connection behavior. On-disk it
>> is
>> still SQL 2005.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
>> news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>> > Hi
>> >
>> > If I take a backup of a database with compatibility level 80 from SQL
>> > Server 2005, will I be able to restore that backup into a SQL Server
>> > 2000
>> > installation?
>> >
>> > Thanks
>> > Mark
>> >
>>

BACKUP/RESTORE compatibility between 2000/2005

Hi
If I take a backup of a database with compatibility level 80 from SQL Server
2005, will I be able to restore that backup into a SQL Server 2000
installation?
Thanks
MarkNo. Compatibility level is for client connection behavior. On-disk it is
still SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi
> If I take a backup of a database with compatibility level 80 from SQL
> Server 2005, will I be able to restore that backup into a SQL Server 2000
> installation?
> Thanks
> Mark
>|||Oh. Boo.
Side-by-side installation for me then.
Thanks for the speedy response.
Mark|||What if I detach a 80 compatibility database on a Sql2005 server, can I
attach on a SQL2000 server?
We are running some compatibility testing against a vendors database and
they are not facilitating our migration to Sql2005. So we need an ability t
o
get them a zipped up copy of a 80 compatibility, Sql 2000 backup but want to
run in Sql 2005 so we can take advantage of database mirroring. (yes I know
it won't be supported by MS in production for another month or two or three,
that works with our migration schedule)
thanks
"Geoff N. Hiten" wrote:

> No. Compatibility level is for client connection behavior. On-disk it is
> still SQL 2005.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
> news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>
>|||No, once it is attached or restored to a 2005 instance it can not go back
intact. The only option is to keep a 2000 db that is empty and import the
data from 2005 to 2000. That is assuming you haven't changed the schema or
used any 2005 features such as new datatypes etc.
Andrew J. Kelly SQL MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:3F5102B7-DB1D-4367-8B0B-C5D69B758498@.microsoft.com...[vbcol=seagreen]
> What if I detach a 80 compatibility database on a Sql2005 server, can I
> attach on a SQL2000 server?
> We are running some compatibility testing against a vendors database and
> they are not facilitating our migration to Sql2005. So we need an ability
> to
> get them a zipped up copy of a 80 compatibility, Sql 2000 backup but want
> to
> run in Sql 2005 so we can take advantage of database mirroring. (yes I
> know
> it won't be supported by MS in production for another month or two or
> three,
> that works with our migration schedule)
> thanks
> "Geoff N. Hiten" wrote:
>

BACKUP/RESTORE compatibility between 2000/2005

Hi
If I take a backup of a database with compatibility level 80 from SQL Server
2005, will I be able to restore that backup into a SQL Server 2000
installation?
Thanks
Mark
No. Compatibility level is for client connection behavior. On-disk it is
still SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi
> If I take a backup of a database with compatibility level 80 from SQL
> Server 2005, will I be able to restore that backup into a SQL Server 2000
> installation?
> Thanks
> Mark
>
|||Oh. Boo.
Side-by-side installation for me then.
Thanks for the speedy response.
Mark
|||What if I detach a 80 compatibility database on a Sql2005 server, can I
attach on a SQL2000 server?
We are running some compatibility testing against a vendors database and
they are not facilitating our migration to Sql2005. So we need an ability to
get them a zipped up copy of a 80 compatibility, Sql 2000 backup but want to
run in Sql 2005 so we can take advantage of database mirroring. (yes I know
it won't be supported by MS in production for another month or two or three,
that works with our migration schedule)
thanks
"Geoff N. Hiten" wrote:

> No. Compatibility level is for client connection behavior. On-disk it is
> still SQL 2005.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Mark Rendle" <m.a.r.k@.big.pink.pig.com> wrote in message
> news:uER4IyW5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>
>
|||No, once it is attached or restored to a 2005 instance it can not go back
intact. The only option is to keep a 2000 db that is empty and import the
data from 2005 to 2000. That is assuming you haven't changed the schema or
used any 2005 features such as new datatypes etc.
Andrew J. Kelly SQL MVP
"paul" <paul@.discussions.microsoft.com> wrote in message
news:3F5102B7-DB1D-4367-8B0B-C5D69B758498@.microsoft.com...[vbcol=seagreen]
> What if I detach a 80 compatibility database on a Sql2005 server, can I
> attach on a SQL2000 server?
> We are running some compatibility testing against a vendors database and
> they are not facilitating our migration to Sql2005. So we need an ability
> to
> get them a zipped up copy of a 80 compatibility, Sql 2000 backup but want
> to
> run in Sql 2005 so we can take advantage of database mirroring. (yes I
> know
> it won't be supported by MS in production for another month or two or
> three,
> that works with our migration schedule)
> thanks
> "Geoff N. Hiten" wrote:

Monday, February 13, 2012

Backup trouble with system database model

Hello all,
Al of a sudden I cannot backup my model database.
When I try to backup the database I get the following message:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'model' is full. Back up the transaction log for t
he database to free up some log space.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Could not write a CHECKPOINT record in database ID 3 because the log is out
of space.
I have 2 questions:
(1) To my knowledge the model database has not been changed since the SQL se
rver installation (+/- 1 year ago), accept a weekly "Optimization". The mode
l database has recovery model "Simple". How did the log file get filled?
(2) How do I empty the log file? I have tried to empty the log file via:
--> DUMP TRANSACTION model WITH NO_LOG
--> BACKUP LOG model WITH NO_LOG
However with no success.
Any suggestion is welcome, thanks,
Annick van den BroekHi,
Have a look into this article;
http://support.microsoft.com/defaul...kb;en-us;317375
Thanks
Hari
MCDBA
"Annick van den Broek" <anonymous@.discussions.microsoft.com> wrote in
message news:5F6BC6C8-BFCD-4A7C-96A4-753A3F27AF12@.microsoft.com...
> Hello all,
> Al of a sudden I cannot backup my model database.
> When I try to backup the database I get the following message:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'model' is full. Back up the transaction log for
the database to free up some log space.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> Could not write a CHECKPOINT record in database ID 3 because the log is
out of space.
> I have 2 questions:
> (1) To my knowledge the model database has not been changed since the SQL
server installation (+/- 1 year ago), accept a weekly "Optimization". The
model database has recovery model "Simple". How did the log file get filled?
> (2) How do I empty the log file? I have tried to empty the log file via:
> --> DUMP TRANSACTION model WITH NO_LOG
> --> BACKUP LOG model WITH NO_LOG
> However with no success.
> Any suggestion is welcome, thanks,
> Annick van den Broek

Sunday, February 12, 2012

backup to tape failed

I was trying to do a backup to tape but it failed with the following message:

Msg 3229, Level 16, State 2, Line 2

Request for device '\\.\Tape0' timed out.

Msg 3013, Level 16, State 1, Line 2

BACKUP DATABASE is terminating abnormally.

Any clues why this happened?

Any help is appreciated.

Take a look at the system event log and sql errorlogs.

You may have a hardware issue with the tape drive (or the connection to it...).

If the OS returned an error code to sql server, it will have been logged in the errorlog.

That 3229 is also raised if a tape mount was pending but a new tape was not loaded prior to the timeout. However, that instance should have "state = 1" and not 2 as you report.

|||

Thanks for the help. The problem was with the MEDIANAME. i was specifying a new Medianame.

If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volume.

Thanks Anyways

Sunday, March 25, 2012

Basic backup question

Basic backup question

Basic backup question

Monday, March 19, 2012

Bad page ID

Sunday, February 19, 2012

BACKUP/RESTORE compatibility between 2000/2005

BACKUP/RESTORE compatibility between 2000/2005

BACKUP/RESTORE compatibility between 2000/2005

Monday, February 13, 2012

Backup trouble with system database model

Sunday, February 12, 2012

backup to tape failed

Backup to Network Drive

Blog Archive

About Me