question re: maintenance

Ok, I want to write a script that

1. backups my sql database
2. commits the transactionlog
3. shrinks the db
4. shrinks the db log

does anyone have a script that will do this or can show me the light?

thank you! (sorry, i'm a programmer and not a very good dba regarding maintenance!)SQL Server 2000?

I would recommend creating a maintenance plan in Enterprise Manager, as it is easier than scripting the whole thing out. Open the database in question and set to TaskPad view is the easiest. You can then click on the dropdown second from the bottom and choose maintenance plan.

I will have to look, as I am not sure this does 100% of what you want. If not, you can set up a job with multiple steps and run the stored procedures necessary to backup, et al. The SQL Books Online (installed with client tools) is a great source of knowledge.

Question on Transaction Log Backups

New to Sql Server, running SQL Server 2000.

Our transaction log file backups occasionally fail as the size of the
transaction log gets really huge. We'd like to schedule additional
transaction log backups. Does that require an exclusive on the database
or can the db be used during a transaction log backup?

Also, does switching to a bulk mode recovery model before a bulk
operation then switching back to full recovery mode after present any
issues?

Thanks in advance.DataPro wrote:
> New to Sql Server, running SQL Server 2000.
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?
> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

It doesn't make sense to switch back and forth. You can permanently
switch to bulk logged because different logging for bulk operations is
all this recovery model is about.

See Microsoft's documentation for more details:

http://technet2.microsoft.com/Windo...3.mspx?mfr=true

Kind regards

robert|||DataPro (datapro01@.yahoo.com) writes:
> Our transaction log file backups occasionally fail as the size of the
> transaction log gets really huge. We'd like to schedule additional
> transaction log backups. Does that require an exclusive on the database
> or can the db be used during a transaction log backup?

Translog backups can be taken without conflict for other users. Else
it would be a quite meaningless operations. Some sites back up their
transaction log every 15 minutes or so.

> Also, does switching to a bulk mode recovery model before a bulk
> operation then switching back to full recovery mode after present any
> issues?

In difference to Robert, I think this makes sense. I believe we do this
in the maintenance procedure that we ship with our product. We switch
to bulk-logged before starting reindexing of tables, and then switch
back to full when it's done.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
>> Also, does switching to a bulk mode recovery model before a bulk
>> operation then switching back to full recovery mode after present any
>> issues?
> In difference to Robert, I think this makes sense. I believe we do this
> in the maintenance procedure that we ship with our product. We switch
> to bulk-logged before starting reindexing of tables, and then switch
> back to full when it's done.

Hm, I can see that it makes sense if you do this for *some* bulk
operations only. My understanding of the question was whether it makes
sense to do it always for bulk operations. And in that case I don't
think it does: if I switch from "full" to "bulk logged" before any bulk
op and switch back afterwards I get the same behavior as if "bulk
logged" was used all the time.

Kind regards

robert|||Many thanks for the thoughts. Being brand new to SQL Server (coming
over from DB2) have much to read and learn.

Here is my situation. An application upgrade occurred over the weekend
which made several database changes. Now the active log is at 12 Gigs.
Taking a full database backup and a log backup is not truncating the
log. Issuing a backup log truncate only has no effect.

I ussed a dbcc loginfo against the database and it returned 308 rows. I
am assuming that these 'virtual logs' represent active non-committed
transactions.

Is that right?

We are talking to the application vendor asking him why this occured.
In the meantime,,,,

Any thoughts on how I can reduce the size of this log? From what I've
read about shrinkfile it doesn't appear to be relevant here.

Any thoughts or suggestions would be appreciated.|||DataPro (datapro01@.yahoo.com) writes:
> Here is my situation. An application upgrade occurred over the weekend
> which made several database changes. Now the active log is at 12 Gigs.
> Taking a full database backup and a log backup is not truncating the
> log. Issuing a backup log truncate only has no effect.

It has, but that will not shrink the log file. (Unless autoshrink is in
effect, but you don't want that.) Only give you a lot of free space in
the file.

Note that TRUNCATE_ONLY invalidates the transaction log, so you need to
take a full backup after this operation.

> Any thoughts on how I can reduce the size of this log? From what I've
> read about shrinkfile it doesn't appear to be relevant here.

DBCC SHRINKFILE is indeed your guy. Be sure to set a target size when
you run it, so you don't make the log too small. In that case, it will
have to grow again, and that will only take performance from the system.
And the file may become fragmented on disk.

For things like an application upgrade, the best strategy may be to set
the recovery mode to simple, and set back to full recovery when it the
upgrade is done. Again, you need to take a full backup in this situation.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is what the vendor is recommending:

1. Back up db and log via Windows Explorer or normal db backups
2. Stop services (application...EAS)
3. Disconnect/detach database
4. Delete log via Windows Explorer
5. Reconnect/reattach database....log will dynamically be allocated

Does this make sense?
They also want autoshrink turned on but its my understanding some SQL
DBA's don't want that. Your thoughts?

Thanks in advance.|||Well SQL Server definetely seems to be a different animal.

Here are some questions a newguy like myself would have about the
logging.

The documentation says that once a backup log truncate only is done, a
full backup should be taken as the recovery ability of the system has
been degraded. Why is that, if that command merely archives committed
transactions?

A SELECT @.@.TRANCOUNT yields a count of zero.
dbcc opentran(eas) says zero also.
dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
that they are open transactions. Why don't they show up in trancount
then?

If a log backup supposedly makes the log 'reusable' then why would a 12
gig log grow another Gig right within a few hours of the backup with
minimal database use?
Wouldn't most of that space be reusable.

Please straighten me out on my thinking :)

Thanks|||We shrink the transaction log every two hours without any noticeable
performance hit, but our total database size is less than 500 MB. I
have played with the simple logging one time and had problems with it,
so we have been sticking with our two hour shrink process (added to our
two hour backup process) which works fine for now.

DBCC SHRINKFILE(DBName_log, 1)
BACKUP LOG DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBName_log, 1)
go|||pb648174 (google@.webpaul.net) writes:
> We shrink the transaction log every two hours without any noticeable
> performance hit, but our total database size is less than 500 MB. I
> have played with the simple logging one time and had problems with it,
> so we have been sticking with our two hour shrink process (added to our
> two hour backup process) which works fine for now.
> DBCC SHRINKFILE(DBName_log, 1)
> BACKUP LOG DBName WITH TRUNCATE_ONLY
> DBCC SHRINKFILE(DBName_log, 1)

This appears completely pointless to me. If you have full recovery,
you are losing all the benefits by truncating the transaction log
every two hours. If you have simple recovery, there should not be
any need to shrink the file, unless there has been any extraordinary
actions going on.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DataPro (datapro01@.yahoo.com) writes:
> Here is what the vendor is recommending:
> 1. Back up db and log via Windows Explorer or normal db backups
> 2. Stop services (application...EAS)
> 3. Disconnect/detach database
> 4. Delete log via Windows Explorer
> 5. Reconnect/reattach database....log will dynamically be allocated
> Does this make sense?

No! Never delete a log file, unless you are told so by a Microsoft
support professional. There are so many people who have lost their
database, because they deleted the log. I guess it's safe if you
detach first, but I would like to take a gamble with a production
database.

> They also want autoshrink turned on but its my understanding some SQL
> DBA's don't want that. Your thoughts?

Shrinking a database file only make sense if there has been some
exceptional activity. Running a schema upgrade certainly counts as one.
But doing it as a matter of routine, or with autoshrink, only serves
to degrade the performance of the application in three different
ways:

1) The shrinking takes resources.
2) The autogrow takes resources.
3) The log file becomes fragmented on disk.

Autogrow is a mixed blessing. Up to SQL Server 6.5 there was no autogrow,
which meant that if the data or log segment was filled up, it was a
hard stop. Autogrow that was added in SQL 7 avoids these situations,
as long as the disk has space. However, for the DBA there is not
really any difference: he should should still monitor database size
amd increase the size in due time. A 10% autogrow in a 200 GB database
is very painfully noticeable fot the users.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||DataPro (datapro01@.yahoo.com) writes:
> Well SQL Server definetely seems to be a different animal.
> Here are some questions a newguy like myself would have about the
> logging.
> The documentation says that once a backup log truncate only is done, a
> full backup should be taken as the recovery ability of the system has
> been degraded. Why is that, if that command merely archives committed
> transactions?

Up-to-the point recovery. You take a full backup at midnight, then
you backup the transaction log once an hour. at 10:23 you run an
UPDATE statement, but forget the WHERE clause. At this point you
can back up the transaction log.

You can the restore the full backup and reapply the transaction log
dumps but stop at 10:22, so you can recover the lost data. If you
somewhere along the line has backed up the transaction log with
NO_LOG or TRUNCATE_ONLY, the log chain is broken, and it's not
possible to continue to apply logs.

> A SELECT @.@.TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?
> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your replies.
In regards to:

"> A SELECT @.@.TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?

> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.

I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database. "

1.) What I'm not clear on is this...a dbcc loginfo yeilds 308 rows
marked with a 2.
Are those 'active' uncommitted transactions? What do those rows
represent?

2.) If backing up a log makes the space within a log file 'reusable'
why did the log grow in size if the space in the log was made reusable?
Wouldn't the minimal activity I referenced use that reclaimed space
within the log instead of making it bigger?

3) And finally what is the relationship between the results of a dbcc
loginfo command, a dbcc opentran and a A SELECT @.@.TRANCOUNT|||DataPro (datapro01@.yahoo.com) writes:
> 1.) What I'm not clear on is this...a dbcc loginfo yeilds 308 rows
> marked with a 2.
> Are those 'active' uncommitted transactions? What do those rows
> represent?

DBCC LOGINFO is not documented, but I learn a little from SQL Server
MVP Tibor Karasiz's article on shrinking:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp. (I recommend
that you read this article in whole, although I summarise some details
below.)

Each row represents a VLF, a virtual log file, and 2 indicates that
that VLF is in use. You cannot shrink the log past the last active
VLF. And if all VLF's have a 2, then all VLFs are in use, and obviously
nothing can be shrunk.

You should backup the transaction log, either to disk or just toss it
with TRUNCATE_ONLY.

> 2.) If backing up a log makes the space within a log file 'reusable'
> why did the log grow in size if the space in the log was made reusable?

As I discussed above, the log is apparently not reusable.

> 3) And finally what is the relationship between the results of a dbcc
> loginfo command, a dbcc opentran and a A SELECT @.@.TRANCOUNT

@.@.trancount only reflects the state of the current process.

DBCC OPENTRAN reveals if there are any open transactions. The log
cannot be truncated past the oldest active transaction.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks much|||This has been a really usefull thread. I have the same problem, and
debated about starting new thread or continue this one:
DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
of 2)
DBCC OPENTRAN indicates no active open transactions
My file is 89% empty, Ive backed up the log files repeatedly (first one
was large 40+ GB, now they are < 500K)
I've tried taking the DB offline and back online. The last log entry
still shows in use and I cant shrink.
I read the article, which helps identify why I cant shrink (log file in
use) but now I'm stumped.
TIA
Rob|||rcamarda (robc390@.hotmail.com) writes:
> This has been a really usefull thread. I have the same problem, and
> debated about starting new thread or continue this one:
> DBCC LOGINFO shows my last log entry is in use (as indicated by STATUS
> of 2)
> DBCC OPENTRAN indicates no active open transactions
> My file is 89% empty, Ive backed up the log files repeatedly (first one
> was large 40+ GB, now they are < 500K)
> I've tried taking the DB offline and back online. The last log entry
> still shows in use and I cant shrink.
> I read the article, which helps identify why I cant shrink (log file in
> use) but now I'm stumped.

Is the last VLF the only that has status = 2? Or are there several?

Did you try running BACKUP TRANSACTION WITH NO_LOG? (Since you lose the
log chain, you need a full backup as well.)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

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
>

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 db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.

I have to set up database maintenance scripts, db and log backups, optimizations etc in batch files so that they can be run by an external scheduler.

When I produce a script from the SQL Server database maintenance wizards and save it off in a bat file, the script will not work when run manually, probably due to the difference in syntax.

I have some examples of backup scripts using sqlmaint.exe, but can't find anything on how to script optimization/reorg jobs so that they can be run by an external scheduler.

Can anybody point me to any docs or guidance on how to produce backup and reorg scripts that can be run externally?

Thanks in advance.
GerryIt sounds like you want to use the osql utility. Go to BOL and look it up uner the index tab. When you double-click on the index utility, chose the "osql utility" under the Command Pronpt Utilities.

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
Gerry
Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>
|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegr oups.com...
|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instea
d of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly
. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just l
et the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execu
te that package.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So inst
ead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe direct
ly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just
let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to exe
cute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has tha
t feature. Can you
elaborate?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has t
hat feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE T
ABLE or DELETE command
using the /Q switch for OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE
TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has that feature. Can you
elaborate?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
>> better control and options.
>> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>>
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:
> > How would you approach a db maintenance job that was job dependent?
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> > Tibor:
> >
> > Thanks for the advice. I will do that.
> >
> > On 2005 we have a scheduler (Control M) running business processing
> > jobs outside of SQL Server and I will have to schedule jobs that are
> > dependent on these jobs finishing.
> >
> > How would you approach a db maintenance job that was job dependent?
> >
> >
> > Tibor Karaszi wrote:
> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> >> better control and options.
> >>
> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
> >>
> >>
> >|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
using the /Q switch for OSQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>> > How would you approach a db maintenance job that was job dependent?
>>
>> Not sure I understand your question, as Control M apparently already has that feature. Can you
>> elaborate?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
>> > Tibor:
>> >
>> > Thanks for the advice. I will do that.
>> >
>> > On 2005 we have a scheduler (Control M) running business processing
>> > jobs outside of SQL Server and I will have to schedule jobs that are
>> > dependent on these jobs finishing.
>> >
>> > How would you approach a db maintenance job that was job dependent?
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you
>> >> much
>> >> better control and options.
>> >>
>> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>> >>
>> >>
>> >
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much

Question on backups

I have a database that is fairly large (12gb) that needs to be backed
up, and all things considered, I'd like to have it backed up to another
box on our network - one with much more space available so that I could
keep several instances on that source at one time.
Database maintenance plans only give me the option to select a local drive.
Is there a way to back up across the network from a server to a second
one, and if so how?
Thanks
BCYou can write the UNC path in the maint wizard. Or you can create "regular" agent job that executes
the BACKUP command using the UNC naming. The SQL Server service account need permissions to the
share.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up, and all things
>considered, I'd like to have it backed up to another box on our network - one with much more space
>available so that I could keep several instances on that source at one time.
> Database maintenance plans only give me the option to select a local drive.
> Is there a way to back up across the network from a server to a second one, and if so how?
> Thanks
> BC|||See if this helps:
http://vyaskn.tripod.com/administration_faq.htm#q17
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
I have a database that is fairly large (12gb) that needs to be backed
up, and all things considered, I'd like to have it backed up to another
box on our network - one with much more space available so that I could
keep several instances on that source at one time.
Database maintenance plans only give me the option to select a local drive.
Is there a way to back up across the network from a server to a second
one, and if so how?
Thanks
BC|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up,
>and all things considered, I'd like to have it backed up to another box on
>our network - one with much more space available so that I could keep
>several instances on that source at one time.
> Database maintenance plans only give me the option to select a local
> drive.
> Is there a way to back up across the network from a server to a second
> one, and if so how?
> Thanks
> BC|||In addition to the others you might consider using one of the 3rd party
backup tools that do in-line compression. This will shorten the backup time
since the file written over the network is much smaller. And you will be
able to store even more files with the same space.
--
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up,
>and all things considered, I'd like to have it backed up to another box on
>our network - one with much more space available so that I could keep
>several instances on that source at one time.
> Database maintenance plans only give me the option to select a local
> drive.
> Is there a way to back up across the network from a server to a second
> one, and if so how?
> Thanks
> BC|||To everyone - thank you for the suggestions, links and advice. I
believe I can make it work.
Andrew J. Kelly wrote:
> In addition to the others you might consider using one of the 3rd party
> backup tools that do in-line compression. This will shorten the backup time
> since the file written over the network is much smaller. And you will be
> able to store even more files with the same space.
>

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 database that is fairly large (12gb) that needs to be backed
up, and all things considered, I'd like to have it backed up to another
box on our network - one with much more space available so that I could
keep several instances on that source at one time.
Database maintenance plans only give me the option to select a local drive.
Is there a way to back up across the network from a server to a second
one, and if so how?
Thanks
BCYou can write the UNC path in the maint wizard. Or you can create "regular"
agent job that executes
the BACKUP command using the UNC naming. The SQL Server service account need
permissions to the
share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up, a
nd all things
>considered, I'd like to have it backed up to another box on our network - o
ne with much more space
>available so that I could keep several instances on that source at one time
.
> Database maintenance plans only give me the option to select a local drive
.
> Is there a way to back up across the network from a server to a second one
, and if so how?
> Thanks
> BC|||See if this helps:
http://vyaskn.tripod.com/administration_faq.htm#q17
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
I have a database that is fairly large (12gb) that needs to be backed
up, and all things considered, I'd like to have it backed up to another
box on our network - one with much more space available so that I could
keep several instances on that source at one time.
Database maintenance plans only give me the option to select a local drive.
Is there a way to back up across the network from a server to a second
one, and if so how?
Thanks
BC|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up,
>and all things considered, I'd like to have it backed up to another box on
>our network - one with much more space available so that I could keep
>several instances on that source at one time.
> Database maintenance plans only give me the option to select a local
> drive.
> Is there a way to back up across the network from a server to a second
> one, and if so how?
> Thanks
> BC|||In addition to the others you might consider using one of the 3rd party
backup tools that do in-line compression. This will shorten the backup time
since the file written over the network is much smaller. And you will be
able to store even more files with the same space.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up,
>and all things considered, I'd like to have it backed up to another box on
>our network - one with much more space available so that I could keep
>several instances on that source at one time.
> Database maintenance plans only give me the option to select a local
> drive.
> Is there a way to back up across the network from a server to a second
> one, and if so how?
> Thanks
> BC|||To everyone - thank you for the suggestions, links and advice. I
believe I can make it work.
Andrew J. Kelly wrote:
> In addition to the others you might consider using one of the 3rd party
> backup tools that do in-line compression. This will shorten the backup ti
me
> since the file written over the network is much smaller. And you will be
> able to store even more files with the same space.
>

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 backups

I have a database that is fairly large (12gb) that needs to be backed
up, and all things considered, I'd like to have it backed up to another
box on our network - one with much more space available so that I could
keep several instances on that source at one time.
Database maintenance plans only give me the option to select a local drive.
Is there a way to back up across the network from a server to a second
one, and if so how?
Thanks
BC
You can write the UNC path in the maint wizard. Or you can create "regular" agent job that executes
the BACKUP command using the UNC naming. The SQL Server service account need permissions to the
share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up, and all things
>considered, I'd like to have it backed up to another box on our network - one with much more space
>available so that I could keep several instances on that source at one time.
> Database maintenance plans only give me the option to select a local drive.
> Is there a way to back up across the network from a server to a second one, and if so how?
> Thanks
> BC
|||See if this helps:
http://vyaskn.tripod.com/administration_faq.htm#q17
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
I have a database that is fairly large (12gb) that needs to be backed
up, and all things considered, I'd like to have it backed up to another
box on our network - one with much more space available so that I could
keep several instances on that source at one time.
Database maintenance plans only give me the option to select a local drive.
Is there a way to back up across the network from a server to a second
one, and if so how?
Thanks
BC
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up,
>and all things considered, I'd like to have it backed up to another box on
>our network - one with much more space available so that I could keep
>several instances on that source at one time.
> Database maintenance plans only give me the option to select a local
> drive.
> Is there a way to back up across the network from a server to a second
> one, and if so how?
> Thanks
> BC
|||In addition to the others you might consider using one of the 3rd party
backup tools that do in-line compression. This will shorten the backup time
since the file written over the network is much smaller. And you will be
able to store even more files with the same space.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23jWObAbcFHA.3828@.tk2msftngp13.phx.gbl...
>I have a database that is fairly large (12gb) that needs to be backed up,
>and all things considered, I'd like to have it backed up to another box on
>our network - one with much more space available so that I could keep
>several instances on that source at one time.
> Database maintenance plans only give me the option to select a local
> drive.
> Is there a way to back up across the network from a server to a second
> one, and if so how?
> Thanks
> BC
|||To everyone - thank you for the suggestions, links and advice. I
believe I can make it work.
Andrew J. Kelly wrote:
> In addition to the others you might consider using one of the 3rd party
> backup tools that do in-line compression. This will shorten the backup time
> since the file written over the network is much smaller. And you will be
> able to store even more files with the same space.
>