SQLAgentMail: backup

Showing posts with label backup. Show all posts

Monday, March 26, 2012

Question regarding backup of 2005 database to 2000.

I know it's not possible to backup a database in SQL2005, and restore to 2000. But, I've been asked if there are any 3rd party tools which can do this - such as SQL Litespeed - but I can't see anything regarding this on their website.

Does anyone know if litespeed or Redgate SQL Backup can restore to 2000 from 2005 ?

Thanks in advance,

You can do the following:

1. In SQL Management studio, generate the database script, you can specify that script be generated for SQL 2000.

2. Create the database and tables using the script.

3. DTS or SSIS the data from SQL 2005 tables to SQL 2000 tables.

4. Create the table contraints like indexes and foreign keys using the script generated in step 1.

5. Create rest of the objects.

hope that helps.

Jag

|||No. Every tool I have seen does a TSQL "BACKUP DATABASE" command to their own virtual file/drive.

The structure has changed and views, etc which are not compatible with 2000. There is no way to "backup" a 2005 and restore to 2000.

As mentioned you can create a script and output all the data and import it back into 2000.

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.
|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.
|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Monday, March 12, 2012

Question on multiserver environment in SQL Server 2005

I am creating a backup strategy for our non-production SQL Server 2005 data. We wish to utiliza the Master/Target Server scenario avaible in SQL Server 2005 with events directed to the Master Server. The question I have is what is the symptoms from the Target Servers if the Master Server fails and is unavailable for whatever reason there might be? I can change the event notification back to local server, but what other impact is there and are there any recommendations as to handle a Master Server outage so Maintenance plans can continue to execute as scheduled on Target Servers.

Thank you!

Here's a response, but one that you won't like. Virtually nobody I know uses the Master/Target option. I am sure there must be some people who do, but it is rare. It is not used because the feature is not very robust or flexible.

Most people use a third-party monitoring program to accomplish what are want to do. There are many available. In addition, there are various third-party backup programs that include backup monitoring built-in.

Question on multiserver environment in SQL Server 2005

Thank you!

Friday, March 9, 2012

Question on Log Backup and NORECOVERY

Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>

Question on Log Backup and NORECOVERY

Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>

Wednesday, March 7, 2012

Question on getting the date value into a filename

I am running the following sqllitespeed backup script on Windows 2003 Server
against SQL Server 2000

sqllitespeed -Bdatabase -T -DNorthwind -FH:\MicrosoftSQLServer\BACKUPS\DATABACKUPS\NorthWi nd\northwind.bak

How could I place the date variable into the output filename?

I've tried variations of %date% etc but nada.

Any thoughts?

Thanks in advance.

Gerryhow do you executye this? Command line?, xp_cmdshell?|||It would be in a script run by an external controller.
A batch file.|||OK, batch file

I would use T-SQL to create said batch fiile, and as a matter of fact I would have a sql server job lauch said batch file

Question on Error message

Running SQL Server 2000 and new to the DBMS. Getting a consistent error
messahe on a test system
'Backup failed to complete the command EmptyTransactionLog1"
I checked the documentation, and the OS logs but no additional info.
Seems to be happening either at startup ot shortly thereafter.
Any thoughts on this?
As a newbie, one (of many) things I find a bit confusing in SQL Server
is that if someone clicked through the database...selected all
tasks...create backup etc...that that backup would not show up in a
database maintenance plan. If you go into that wizard anything already
set to run does not show.
Is there any way to determine if somebody has any type of backup taking
place on a db other than log messages?
Thanks in advance.
GerryIn Enterprise Manager, Under Management, you will find both Backup jobs and
Maintenance plans.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> messahe on a test system
> 'Backup failed to complete the command EmptyTransactionLog1"
> I checked the documentation, and the OS logs but no additional info.
> Seems to be happening either at startup ot shortly thereafter.
> Any thoughts on this?
> As a newbie, one (of many) things I find a bit confusing in SQL Server
> is that if someone clicked through the database...selected all
> tasks...create backup etc...that that backup would not show up in a
> database maintenance plan. If you go into that wizard anything already
> set to run does not show.
> Is there any way to determine if somebody has any type of backup taking
> place on a db other than log messages?
> Thanks in advance.
> Gerry
>|||Arnie:
I don't see anything in either place. I assume that means that no
backups are taking place.
What I'm not clear on is if no backups of any type are taking
place...why am I getting the 'backup failed to complete the comand'
error message
Thanks
Gerry
Arnie Rowland wrote:
> In Enterprise Manager, Under Management, you will find both Backup jobs and
> Maintenance plans.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> > Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> > messahe on a test system
> >
> > 'Backup failed to complete the command EmptyTransactionLog1"
> >
> > I checked the documentation, and the OS logs but no additional info.
> > Seems to be happening either at startup ot shortly thereafter.
> >
> > Any thoughts on this?
> >
> > As a newbie, one (of many) things I find a bit confusing in SQL Server
> > is that if someone clicked through the database...selected all
> > tasks...create backup etc...that that backup would not show up in a
> > database maintenance plan. If you go into that wizard anything already
> > set to run does not show.
> >
> > Is there any way to determine if somebody has any type of backup taking
> > place on a db other than log messages?
> >
> > Thanks in advance.
> >
> > Gerry
> >|||Check to see if there are Maintenance Plans configured. Backup could be part
of a Maintenance Plan.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
> Arnie:
> I don't see anything in either place. I assume that means that no
> backups are taking place.
> What I'm not clear on is if no backups of any type are taking
> place...why am I getting the 'backup failed to complete the comand'
> error message
> Thanks
> Gerry
>
> Arnie Rowland wrote:
>> In Enterprise Manager, Under Management, you will find both Backup jobs
>> and
>> Maintenance plans.
>> --
>> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
>> > Running SQL Server 2000 and new to the DBMS. Getting a consistent error
>> > messahe on a test system
>> >
>> > 'Backup failed to complete the command EmptyTransactionLog1"
>> >
>> > I checked the documentation, and the OS logs but no additional info.
>> > Seems to be happening either at startup ot shortly thereafter.
>> >
>> > Any thoughts on this?
>> >
>> > As a newbie, one (of many) things I find a bit confusing in SQL Server
>> > is that if someone clicked through the database...selected all
>> > tasks...create backup etc...that that backup would not show up in a
>> > database maintenance plan. If you go into that wizard anything already
>> > set to run does not show.
>> >
>> > Is there any way to determine if somebody has any type of backup taking
>> > place on a db other than log messages?
>> >
>> > Thanks in advance.
>> >
>> > Gerry
>> >
>|||No, there aren't.
Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
command?
Arnie Rowland wrote:
> Check to see if there are Maintenance Plans configured. Backup could be part
> of a Maintenance Plan.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
> > Arnie:
> >
> > I don't see anything in either place. I assume that means that no
> > backups are taking place.
> >
> > What I'm not clear on is if no backups of any type are taking
> > place...why am I getting the 'backup failed to complete the comand'
> > error message
> >
> > Thanks
> > Gerry
> >
> >
> > Arnie Rowland wrote:
> >> In Enterprise Manager, Under Management, you will find both Backup jobs
> >> and
> >> Maintenance plans.
> >>
> >> --
> >> Arnie Rowland
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "DataPro" <datapro01@.yahoo.com> wrote in message
> >> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
> >> > Running SQL Server 2000 and new to the DBMS. Getting a consistent error
> >> > messahe on a test system
> >> >
> >> > 'Backup failed to complete the command EmptyTransactionLog1"
> >> >
> >> > I checked the documentation, and the OS logs but no additional info.
> >> > Seems to be happening either at startup ot shortly thereafter.
> >> >
> >> > Any thoughts on this?
> >> >
> >> > As a newbie, one (of many) things I find a bit confusing in SQL Server
> >> > is that if someone clicked through the database...selected all
> >> > tasks...create backup etc...that that backup would not show up in a
> >> > database maintenance plan. If you go into that wizard anything already
> >> > set to run does not show.
> >> >
> >> > Is there any way to determine if somebody has any type of backup taking
> >> > place on a db other than log messages?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Gerry
> >> >
> >|||DataPro wrote:
> No, there aren't.
> Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
> command?
>
Where are you seeing this error message? Can you post the ENTIRE error
message here?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I haven't seen or heard of such a command. I'm wondering if it is a
'comment' in a script, job, etc.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153478749.916109.113570@.i42g2000cwa.googlegroups.com...
> No, there aren't.
> Wouldn't the command 'EmptyTransactionLog' be some kind of truncation
> command?
>
> Arnie Rowland wrote:
>> Check to see if there are Maintenance Plans configured. Backup could be
>> part
>> of a Maintenance Plan.
>> --
>> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1153401613.227886.99740@.h48g2000cwc.googlegroups.com...
>> > Arnie:
>> >
>> > I don't see anything in either place. I assume that means that no
>> > backups are taking place.
>> >
>> > What I'm not clear on is if no backups of any type are taking
>> > place...why am I getting the 'backup failed to complete the comand'
>> > error message
>> >
>> > Thanks
>> > Gerry
>> >
>> >
>> > Arnie Rowland wrote:
>> >> In Enterprise Manager, Under Management, you will find both Backup
>> >> jobs
>> >> and
>> >> Maintenance plans.
>> >>
>> >> --
>> >> Arnie Rowland
>> >> Most good judgment comes from experience.
>> >> Most experience comes from bad judgment.
>> >> - Anonymous
>> >>
>> >>
>> >> "DataPro" <datapro01@.yahoo.com> wrote in message
>> >> news:1153322062.758721.87120@.m73g2000cwd.googlegroups.com...
>> >> > Running SQL Server 2000 and new to the DBMS. Getting a consistent
>> >> > error
>> >> > messahe on a test system
>> >> >
>> >> > 'Backup failed to complete the command EmptyTransactionLog1"
>> >> >
>> >> > I checked the documentation, and the OS logs but no additional info.
>> >> > Seems to be happening either at startup ot shortly thereafter.
>> >> >
>> >> > Any thoughts on this?
>> >> >
>> >> > As a newbie, one (of many) things I find a bit confusing in SQL
>> >> > Server
>> >> > is that if someone clicked through the database...selected all
>> >> > tasks...create backup etc...that that backup would not show up in a
>> >> > database maintenance plan. If you go into that wizard anything
>> >> > already
>> >> > set to run does not show.
>> >> >
>> >> > Is there any way to determine if somebody has any type of backup
>> >> > taking
>> >> > place on a db other than log messages?
>> >> >
>> >> > Thanks in advance.
>> >> >
>> >> > Gerry
>> >> >
>> >
>|||This is the message...now repeating every 30 minutes
BACKUP failed to complete the command EmptyTransactionLog;1
It seems to happen shortly after startup and follows this notation in
the log, before just repeating itself over and over
SQL global counter collection task is created.
I did a search on the web and found one reference to this message. But
no clue on what it means.
Thanks
Gerry|||Gerry,
What service pack do you have installed?
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1153765668.194365.252410@.m79g2000cwm.googlegroups.com...
> This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
>|||This is a multi-part message in MIME format.
--020206070802010300020903
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
DataPro wrote:
> This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
>
Hi Gerry
It sounds like you have a job scheduled to run every 30 minutes that
does something to a logfile. It could the that it tries to backup a
logfile for a database that has been set to SIMPLE recovery.
--
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--020206070802010300020903
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
DataPro wrote:
<blockquote
cite="mid1153765668.194365.252410@.m79g2000cwm.googlegroups.com"
type="cite">
<pre wrap="">This is the message...now repeating every 30 minutes
BACKUP failed to complete the command EmptyTransactionLog;1
It seems to happen shortly after startup and follows this notation in
the log, before just repeating itself over and over
SQL global counter collection task is created.
I did a search on the web and found one reference to this message. But
no clue on what it means.
Thanks
Gerry
</pre>
</blockquote>
Hi Gerry 
 
It sounds like you have a job scheduled to run every 30 minutes that
does something to a logfile. It could the that it tries to backup a
logfile for a database that has been set to SIMPLE recovery. 
 
-- 
Regards 
Steen Schlüter Persson 
Databaseadministrator / Systemadministrator 
 

</body>
</html>
--020206070802010300020903--|||Arnie:
Service pack 4
Gerry
Arnie Rowland wrote:
> Gerry,
> What service pack do you have installed?
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1153765668.194365.252410@.m79g2000cwm.googlegroups.com...
> > This is the message...now repeating every 30 minutes
> >
> > BACKUP failed to complete the command EmptyTransactionLog;1
> >
> > It seems to happen shortly after startup and follows this notation in
> > the log, before just repeating itself over and over
> >
> > SQL global counter collection task is created.
> >
> > I did a search on the web and found one reference to this message. But
> > no clue on what it means.
> >
> > Thanks
> > Gerry
> >|||Steen:
There is no reference in the message to which db might be involved.
However I do see that both databases they are using are set to Simple
mode.
When I look under SQL Agent/Jobs I only see job scheduled to run
every end of month.
Any thoughts ?
Thanks
Gerry
Steen Persson (DK) wrote:
> DataPro wrote:
> > This is the message...now repeating every 30 minutes
> >
> > BACKUP failed to complete the command EmptyTransactionLog;1
> >
> > It seems to happen shortly after startup and follows this notation in
> > the log, before just repeating itself over and over
> >
> > SQL global counter collection task is created.
> >
> > I did a search on the web and found one reference to this message. But
> > no clue on what it means.
> >
> > Thanks
> > Gerry
> >
> >
> Hi Gerry
> It sounds like you have a job scheduled to run every 30 minutes that
> does something to a logfile. It could the that it tries to backup a
> logfile for a database that has been set to SIMPLE recovery.
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
>
> --020206070802010300020903
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 1139
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> DataPro wrote:
> <blockquote
> cite=3D"mid1153765668.194365.252410@.m79g2000cwm.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">This is the message...now repeating every 30 minutes
> BACKUP failed to complete the command EmptyTransactionLog;1
> It seems to happen shortly after startup and follows this notation in
> the log, before just repeating itself over and over
> SQL global counter collection task is created.
> I did a search on the web and found one reference to this message. But
> no clue on what it means.
> Thanks
> Gerry
> </pre>
> </blockquote>
> Hi Gerry 
> 
> It sounds like you have a job scheduled to run every 30 minutes that
> does something to a logfile. It could the that it tries to backup a
> logfile for a database that has been set to SIMPLE recovery. 
> 
> -- 
> Regards 
> Steen Schlüter Persson 
> Databaseadministrator / Systemadministrator 
> 
> 
> </body>
> </html>
> > --020206070802010300020903--

Question on Error message

Question on Different Types of Backups for SQL Server 2000

Does it matter which backup method you use with SQL Server
2000, as far as SQL Server recognizing the backup, and
keeping the transaction log file at a reasonable size?
You can backup your database in Enterprise Manager using the
Backup Wizard, the Backup Database Menu Option, or through a
Maintenance Plan.
What my documentation doesn't tell me, is this: Does it
matter which backup method you use, in order for the
transaction log to remain at a reasonable size (based on the
last full backup)?
In other words, will SQL Server 2000 recognize any of the
backups as full backups, and allow the transaction log to be
shrunk, back to the point of the last full backup?
Appreciate any help/advice.
jj
p.s. If you put the DB Backup on a SCHEDULE, using either
the Wizard, or the Backup Database menu option in EM, does
this require SQL Server Agent to be running? Or do you only
need Agent to run, when backing up via a Maintenance Plan?
Just wondering if ANY scheduled backups require Agent to be
running?Hello,
To answer your question, no it does not matter.
Personally I do it though Database Maintenance Plan,
purely cos i'm a GUI kinda guy
:D
Peter
>--Original Message--
>Does it matter which backup method you use with SQL
Server
>2000, as far as SQL Server recognizing the backup, and
>keeping the transaction log file at a reasonable size?
>You can backup your database in Enterprise Manager using
the
>Backup Wizard, the Backup Database Menu Option, or
through a
> Maintenance Plan.
>What my documentation doesn't tell me, is this: Does it
>matter which backup method you use, in order for the
>transaction log to remain at a reasonable size (based on
the
>last full backup)?
>In other words, will SQL Server 2000 recognize any of the
>backups as full backups, and allow the transaction log to
be
>shrunk, back to the point of the last full backup?
>Appreciate any help/advice.
>jj
>p.s. If you put the DB Backup on a SCHEDULE, using
either
>the Wizard, or the Backup Database menu option in EM,
does
>this require SQL Server Agent to be running? Or do you
only
>need Agent to run, when backing up via a Maintenance
Plan?
>Just wondering if ANY scheduled backups require Agent to
be
>running?
>.
>|||Thanks for the info. Very helpful. The reason I asked
this, is because each backup type uses a different naming
convention. I just need to make sure that, if I do a full
DB back, using either of the 3 methods, I'll be able to do a
DB RESTORE, using any of the backup files (regardless of
their naming convention, or which method I used to backup
the DB).
Is this correct?
Sorry for this one last question. I just need to make sure,
and my documentation doesn't provide answer to this
question. Many thanks.
jj
=============Kevin Brooks wrote:
> Do FULL DATABASE BACKUPS and LOG BACKUPS. We do a full backup once a day
> and backup the logs every 5min to 1 hour, depending on which database. For
> safety we also have a job that manually checks % log used(DBCC
> SQLPERF(LOGSPACE) not agent counters) every 30mins across all databases and
> will fire off the appropiate log backup and email us if they are getting
> full. A full backup will not clear the log and log size will always vary
> depending on usage. This can all be done through the GUI, but the GUI
> stinks. It is all about coding for best performance and most control, then
> again we had to build customer log shipping because SQL logshipping is not
> stable. Remember - if your logfile grows and you need to shrink the
> physical file, you have to use DBCC SHRINKFILE. This is all assuming you
> are running in FULL RECOVERY mode. HTH.
>
> "JJay" <jjj.nospam@.mindspring.com> wrote in message
> news:D4q_a.10456$M6.888848@.newsread1.prod.itd.earthlink.net...
>>Does it matter which backup method you use with SQL Server
>>2000, as far as SQL Server recognizing the backup, and
>>keeping the transaction log file at a reasonable size?
>>You can backup your database in Enterprise Manager using the
>>Backup Wizard, the Backup Database Menu Option, or through a
>> Maintenance Plan.
>>What my documentation doesn't tell me, is this: Does it
>>matter which backup method you use, in order for the
>>transaction log to remain at a reasonable size (based on the
>>last full backup)?
>>In other words, will SQL Server 2000 recognize any of the
>>backups as full backups, and allow the transaction log to be
>>shrunk, back to the point of the last full backup?
>>Appreciate any help/advice.
>>jj
>>p.s. If you put the DB Backup on a SCHEDULE, using either
>>the Wizard, or the Backup Database menu option in EM, does
>>this require SQL Server Agent to be running? Or do you only
>>need Agent to run, when backing up via a Maintenance Plan?
>>Just wondering if ANY scheduled backups require Agent to be
>>running?
>
>

Question on Differencial Backup

Hi,
I am setting an differencial backup/restore for the SQL Server, I have two
questions need help,
1. If I do a whole database backup on the first day of each month, and the
time is 3:00AM, then how to configure the differencial backup, should I
exclude the day of doing the whole database backup? How to do a whole month
differencial backup except only one day?
2. If the differencial backup is to use the append to media, how to control
the size of the back up? (I plan to backup to the hard disk).
Thanks in advance
Frank
Frank
Pls read this article
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Frank" <wangping@.lucent.com> wrote in message
news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole
month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to
control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
|||The differential contains all changes since the last full backup so there's
no point in doing one straight after the full (but it wouldn't hurt just
won't have much to do).
I wouldn't append the backups as if you have a corrupt file you lose all of
them. It's easier to handle if every backup is in it's own file with a
datestamp.
see
http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
Which will do full, log and diff backups for all databases and delete old
backup files.
A full backup once a month is not usual though - why have you chosen that?
Usually it is during a quiete period so every night or at weekends.
Often if you leave too long between full backups then a lot of pages get
changed and the diff ends up backing up nearly the whole database anyway.
Very important.
Make sure you test restore the full backup. This is even more important with
your scenario as if you don't have log backups then a corrupt full backup
will invalidate all the following diffs and you could end up losing a couple
of months of data.
"Frank" wrote:

> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
>
|||Thanks Uri but I could not open the linked website.
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBJJaxCDFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Frank
> Pls read this article
>
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1[vbcol=seagreen]
>
>
>
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
two[vbcol=seagreen]
the
> month
> control
>
|||Thanks so much for your detailed information.
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]
|||Nigel,
There are many useful tips and articles in your webpage.
Thanks again
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]

Question on Differencial Backup

Hi,
I am setting an differencial backup/restore for the SQL Server, I have two
questions need help,
1. If I do a whole database backup on the first day of each month, and the
time is 3:00AM, then how to configure the differencial backup, should I
exclude the day of doing the whole database backup? How to do a whole month
differencial backup except only one day?
2. If the differencial backup is to use the append to media, how to control
the size of the back up? (I plan to backup to the hard disk).
Thanks in advance
FrankFrank
Pls read this article
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
"Frank" <wangping@.lucent.com> wrote in message
news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole
month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to
control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>|||The differential contains all changes since the last full backup so there's
no point in doing one straight after the full (but it wouldn't hurt just
won't have much to do).
I wouldn't append the backups as if you have a corrupt file you lose all of
them. It's easier to handle if every backup is in it's own file with a
datestamp.
see
http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
Which will do full, log and diff backups for all databases and delete old
backup files.
A full backup once a month is not usual though - why have you chosen that?
Usually it is during a quiete period so every night or at weekends.
Often if you leave too long between full backups then a lot of pages get
changed and the diff ends up backing up nearly the whole database anyway.
Very important.
Make sure you test restore the full backup. This is even more important with
your scenario as if you don't have log backups then a corrupt full backup
will invalidate all the following diffs and you could end up losing a couple
of months of data.
"Frank" wrote:
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
>|||Thanks Uri but I could not open the linked website.
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBJJaxCDFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Frank
> Pls read this article
>
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> > --administaiting best practices
>
>
>
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I am setting an differencial backup/restore for the SQL Server, I have
two
> > questions need help,
> > 1. If I do a whole database backup on the first day of each month, and
the
> > time is 3:00AM, then how to configure the differencial backup, should I
> > exclude the day of doing the whole database backup? How to do a whole
> month
> > differencial backup except only one day?
> > 2. If the differencial backup is to use the append to media, how to
> control
> > the size of the back up? (I plan to backup to the hard disk).
> >
> > Thanks in advance
> > Frank
> >
> >
>|||Thanks so much for your detailed information.
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple
> of months of data.
>
> "Frank" wrote:
> > Hi,
> > I am setting an differencial backup/restore for the SQL Server, I have
two
> > questions need help,
> > 1. If I do a whole database backup on the first day of each month, and
the
> > time is 3:00AM, then how to configure the differencial backup, should I
> > exclude the day of doing the whole database backup? How to do a whole
month
> > differencial backup except only one day?
> > 2. If the differencial backup is to use the append to media, how to
control
> > the size of the back up? (I plan to backup to the hard disk).
> >
> > Thanks in advance
> > Frank
> >
> >
> >|||Nigel,
There are many useful tips and articles in your webpage.
Thanks again
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple
> of months of data.
>
> "Frank" wrote:
> > Hi,
> > I am setting an differencial backup/restore for the SQL Server, I have
two
> > questions need help,
> > 1. If I do a whole database backup on the first day of each month, and
the
> > time is 3:00AM, then how to configure the differencial backup, should I
> > exclude the day of doing the whole database backup? How to do a whole
month
> > differencial backup except only one day?
> > 2. If the differencial backup is to use the append to media, how to
control
> > the size of the back up? (I plan to backup to the hard disk).
> >
> > Thanks in advance
> > Frank
> >
> >
> >

Question on Differencial Backup

Hi,
I am setting an differencial backup/restore for the SQL Server, I have two
questions need help,
1. If I do a whole database backup on the first day of each month, and the
time is 3:00AM, then how to configure the differencial backup, should I
exclude the day of doing the whole database backup? How to do a whole month
differencial backup except only one day?
2. If the differencial backup is to use the append to media, how to control
the size of the back up? (I plan to backup to the hard disk).
Thanks in advance
FrankFrank
Pls read this article
<http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Frank" <wangping@.lucent.com> wrote in message
news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole
month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to
control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>|||The differential contains all changes since the last full backup so there's
no point in doing one straight after the full (but it wouldn't hurt just
won't have much to do).
I wouldn't append the backups as if you have a corrupt file you lose all of
them. It's easier to handle if every backup is in it's own file with a
datestamp.
see
http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
Which will do full, log and diff backups for all databases and delete old
backup files.
A full backup once a month is not usual though - why have you chosen that?
Usually it is during a quiete period so every night or at weekends.
Often if you leave too long between full backups then a lot of pages get
changed and the diff ends up backing up nearly the whole database anyway.
Very important.
Make sure you test restore the full backup. This is even more important with
your scenario as if you don't have log backups then a corrupt full backup
will invalidate all the following diffs and you could end up losing a couple
of months of data.
"Frank" wrote:

> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole mont
h
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to contro
l
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
>|||Thanks Uri but I could not open the linked website.
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBJJaxCDFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Frank
> Pls read this article
>
<http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
>
>
>
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
two[vbcol=seagreen]
the[vbcol=seagreen]
> month
> control
>|||Thanks so much for your detailed information.
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
>
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]|||Nigel,
There are many useful tips and articles in your webpage.
Thanks again
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
>
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]

Monday, February 20, 2012

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
.xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BCRun a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
the database(s) selected in the plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a backup of a database that has been running for quite a while, successfully.
> I went in to the Database Maintenance plan, and created a new plan to optimize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to having a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT , NOUNLOAD , NAME => N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, but
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.|||I realized that after posting. I was confused by the missing BACKUP DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>> Run a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
>> know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
>> the database(s) selected in the plan.
> sfa in our case is the database name.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
.xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BCRun a Profiler trace and you will see the TSQL commands submitted from the m
aint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted d
oes a database backup for
the database(s) selected in the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.ph
x.gbl...
>I have a backup of a database that has been running for quite a while, succ
essfully.
> I went in to the Database Maintenance plan, and created a new plan to opti
mize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job
section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to ha
ving a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH IN
IT , NOUNLOAD , NAME =
> N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyI
fClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, bu
t
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.|||I realized that after posting. I was confused by the missing BACKUP DATABASE
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.ph
x.gbl...
> Tibor Karaszi wrote:
> sfa in our case is the database name.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
..xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BC
Run a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
the database(s) selected in the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a backup of a database that has been running for quite a while, successfully.
> I went in to the Database Maintenance plan, and created a new plan to optimize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to having a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT , NOUNLOAD , NAME =
> N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC
|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, but
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.
|||I realized that after posting. I was confused by the missing BACKUP DATABASE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> sfa in our case is the database name.

Question on Backup/Restore

New to SQL Server. is it true, as it seems to be, that SQL Server
cannot do a point in time recovery to a point in time that would exceed
the time of the last transaction log backup?
That is, it cannot use the active log in conjunction with the archived
logs?
Second question, if the server fails and is rebooted, will the system
catalog tables 'remember' the times of the transaction log backups?
Thanks in advance.
GerryDataPro wrote:
> New to SQL Server. is it true, as it seems to be, that SQL Server
> cannot do a point in time recovery to a point in time that would exceed
> the time of the last transaction log backup?
> That is, it cannot use the active log in conjunction with the archived
> logs?
> Second question, if the server fails and is rebooted, will the system
> catalog tables 'remember' the times of the transaction log backups?
> Thanks in advance.
> Gerry
>
1. No, it cannot use the "active" log for this. You must take a final
transaction log backup, which will contain the data from the active log,
which you can then use in your point-in-time restore.
2. Yes, this information is retained between reboots.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||
> 1. No, it cannot use the "active" log for this. You must take a final
> transaction log backup, which will contain the data from the active log,
> which you can then use in your point-in-time restore.
> 2. Yes, this information is retained between reboots.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks Tracy|||Note, though that even though you need to do a log backup (you cannot use th
e active log), you still
have plenty of options. What is required is that the log file exists. I'm su
re this is the same as
in other systems. If the database is inaccessible, you can still produce a l
og backup using the
NO_TRUNCATE option of the BACKUP LOG command. And even if you can't start th
e SQL Server, you can
handle this situation:
Create a database on some other SQL Server.
Stop that SQL Server.
Delete the database files.
Copy your "production" ldf file where the newly created log file used to be.
Start SQL Server.
Do a log backup using NO_TRUNCATE.
You have now produced that "last log backup".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165419474.732740.263160@.f1g2000cwa.googlegroups.com...
>
> Thanks Tracy
>|||Thanks Tibor;;
OK after I do the restore of a user database...even though the restore
is marked complete on the percentage bar graph the database was marked
as 'Loading'
Baffled me...thought it was because it was being rolled forward
through transaction logs.
I ended up issuing a
RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
But that does not leave me with a warm and fuzzy.
Is this typical behavior in SQL Server?[vbcol=seagreen]|||When you do RESTORE, you specify NORECOVERY on all restores (db, log, log et
c) but the last one.
Until you've done restore using RECOVERY, the database is in "restoring" sta
te (reported as
"loading" by earlier tools). If you forgot to do RECOVERY on your last backu
p, you can handle the
situation in just the way you did. If you have a case where you do, for inst
ance:
RESTORE DATABASE ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH RECOVERY
And the database is still in "restoring" state (inaccessible, and not only a
refresh problem in EM),
you should report this as a bug to MS.
Technical sidenote: The only difference between NORECOVERY and RECOVERY is w
hether the UNDO phase is
performed by the restore process. The prior REDO phase is always performed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165424865.858239.168580@.73g2000cwn.googlegroups.com...
> Thanks Tibor;;
> OK after I do the restore of a user database...even though the restore
> is marked complete on the percentage bar graph the database was marked
> as 'Loading'
> Baffled me...thought it was because it was being rolled forward
> through transaction logs.
> I ended up issuing a
> RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
> But that does not leave me with a warm and fuzzy.
> Is this typical behavior in SQL Server?
>|||Tibor Karaszi wrote:
> When you do RESTORE, you specify NORECOVERY on all restores (db, log, log
etc) but the last one.
> Until you've done restore using RECOVERY, the database is in "restoring" s
tate (reported as
> "loading" by earlier tools). If you forgot to do RECOVERY on your last bac
kup, you can handle the
> situation in just the way you did. If you have a case where you do, for in
stance:
> RESTORE DATABASE ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH RECOVERY
> And the database is still in "restoring" state (inaccessible, and not only
a refresh problem in EM),
> you should report this as a bug to MS.
> Technical sidenote: The only difference between NORECOVERY and RECOVERY is
whether the UNDO phase is
> performed by the restore process. The prior REDO phase is always performed
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Thanks Tibor

Monday, March 26, 2012

Question regarding backup of 2005 database to 2000.

Question regarding .LDF and .MDF files and backup

Question regarding .LDF and .MDF files and backup

Monday, March 12, 2012

Question on multiserver environment in SQL Server 2005

Question on multiserver environment in SQL Server 2005

Friday, March 9, 2012

Question on Log Backup and NORECOVERY

Question on Log Backup and NORECOVERY

Wednesday, March 7, 2012

Question on getting the date value into a filename

Question on Error message

Question on Error message

Question on Different Types of Backups for SQL Server 2000

Question on Differencial Backup

Question on Differencial Backup

Question on Differencial Backup

Monday, February 20, 2012

Question on backups

Question on backups

Question on backups

Question on Backup/Restore

Question on Backup/Restore

SQLAgentMail

Blog Archive

About Me