Backing up SQL to a different server

I am trying to send backups of SQL databases and
transaction logs to a different server. I thought it
would work if the UNC name and path were used. So, first
of all, I don't if this is correct. I think it's trying
to do the backup, but the error I am getting
is "BackupDeviceFile::Backup device "\\erlfs1
\sqlbackups\..." failed to create. Operating system error
=5 (Access is denied.)" I have given the Everyone group
full control, and I still get the same error. Does anyone
have recommendations for sending backups to a different
machine?Make sure you are running SQL under a domain-level account and that the
account has permissions to create and write files on the remote location.
Logging in to the console of the SQL server as the service account and
copying files is a good way to test remote file access.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"teresa" <anonymous@.discussions.microsoft.com> wrote in message
news:1454601c3c33c$58da9540$a601280a@.phx.gbl...
> I am trying to send backups of SQL databases and
> transaction logs to a different server. I thought it
> would work if the UNC name and path were used. So, first
> of all, I don't if this is correct. I think it's trying
> to do the backup, but the error I am getting
> is "BackupDeviceFile::Backup device "\\erlfs1
> \sqlbackups\..." failed to create. Operating system error
> =5 (Access is denied.)" I have given the Everyone group
> full control, and I still get the same error. Does anyone
> have recommendations for sending backups to a different
> machine?|||The SQL server is logged in under the domain admin account
and the domain admin has full control on the remote folder
that SQL is trying to back up to. Also, SQL Server Agent
service is started under the domain admin account and the
particular job is owned by the domain admin. I was able
to copy a file from the console to the backup server.
Looking at the event viewer, after a job has failed, one
error says "operating system eror = 5 (access is denied),"
another one says that the job failed and that it was
invoked by user 'sa'. I mention this because I don't
if 'sa' trying to invoke the job is the problem.
>--Original Message--
>Make sure you are running SQL under a domain-level
account and that the
>account has permissions to create and write files on the
remote location.
>Logging in to the console of the SQL server as the
service account and
>copying files is a good way to test remote file access.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>
>"teresa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1454601c3c33c$58da9540$a601280a@.phx.gbl...
>> I am trying to send backups of SQL databases and
>> transaction logs to a different server. I thought it
>> would work if the UNC name and path were used. So,
first
>> of all, I don't if this is correct. I think it's trying
>> to do the backup, but the error I am getting
>> is "BackupDeviceFile::Backup device "\\erlfs1
>> \sqlbackups\..." failed to create. Operating system
error
>> =5 (Access is denied.)" I have given the Everyone group
>> full control, and I still get the same error. Does
anyone
>> have recommendations for sending backups to a different
>> machine?
>
>.
>|||Ping the second server from the first using UNC (ping
Servername). If you can not ping, use the IP address (To
find IP...Ipconfig).
Second Check the the 'Domain Admins' group is added
to 'Administrators' group of the second server.
>--Original Message--
>The SQL server is logged in under the domain admin
account
>and the domain admin has full control on the remote
folder
>that SQL is trying to back up to. Also, SQL Server Agent
>service is started under the domain admin account and the
>particular job is owned by the domain admin. I was able
>to copy a file from the console to the backup server.
>Looking at the event viewer, after a job has failed, one
>error says "operating system eror = 5 (access is
denied),"
>another one says that the job failed and that it was
>invoked by user 'sa'. I mention this because I don't
>if 'sa' trying to invoke the job is the problem.
>>--Original Message--
>>Make sure you are running SQL under a domain-level
>account and that the
>>account has permissions to create and write files on the
>remote location.
>>Logging in to the console of the SQL server as the
>service account and
>>copying files is a good way to test remote file access.
>>--
>>Geoff N. Hiten
>>Microsoft SQL Server MVP
>>Senior Database Administrator
>>Careerbuilder.com
>>
>>"teresa" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:1454601c3c33c$58da9540$a601280a@.phx.gbl...
>> I am trying to send backups of SQL databases and
>> transaction logs to a different server. I thought it
>> would work if the UNC name and path were used. So,
>first
>> of all, I don't if this is correct. I think it's
trying
>> to do the backup, but the error I am getting
>> is "BackupDeviceFile::Backup device "\\erlfs1
>> \sqlbackups\..." failed to create. Operating system
>error
>> =5 (Access is denied.)" I have given the Everyone
group
>> full control, and I still get the same error. Does
>anyone
>> have recommendations for sending backups to a different
>> machine?
>>
>>.
>.
>sql

Backing up log when recovery set to Simple

I have a job that I created that backs up transaction logs on a server that has many databases on it. If a db recovery model is set to simple, I want the job to skip over that database, rather then trying to backup the log.

It would be easy to just let it try, and fail, however, the job status will show failed, even if it backs up all database logs, and only fails on one. Is there a way to easily programatically detect if a db is set to simple, and then I can skip backing up that db log?

Thanks
TommySELECT DATABASEPROPERTYEX('Northwind','Recovery')|||Thanks brett, that's what I needed, in case anyone else is interested, here's the syntax I'm using:

Declare @.Recovery_Model nvarchar(20),
Select @.Recovery_Model = CONVERT(nvarchar(20), DATABASEPROPERTYEX( @.Database_Name , 'Recovery' ))
if (@.Recovery_Model = 'SIMPLE')
PRINT 'Simple Mode so skip'
else
PRINT 'do the needful'

Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backupYou could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
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
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.

> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>|||Hi Peter,
Thanks for you reply to the first part of my question..

database[vbcol=seagreen]
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
>
> database
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>

Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backup
You could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>
|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
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
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>
|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?
|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.

> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>
|||Hi Peter,
Thanks for you reply to the first part of my question..
[vbcol=seagreen]
database
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.
|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
> database
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>

Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backupYou could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
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
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>|||Hi Peter,
Thanks for you reply to the first part of my question..
>> Can I say it is safe to NOT do at least one full
database
>> backup before doing a log ?
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
> >> Can I say it is safe to NOT do at least one full
> database
> >> backup before doing a log ?
> >
> >What do you mean by 'doing a log'?
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations, and conditionally
handling a mirrored database *inside* a backup task is not available. Consider wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL task before each which
check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > I would like some advice on a way to do this or whether I am barking
> > up the wrong tree and should try a different angle - perhaps defining
> > my own backup process (I really want to avoid doing this).
> This is what you would have to do. Maint plans are for simpler installations, and conditionally
> handling a mirrored database *inside* a backup task is not available. Consider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL task before each which
> check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
> plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing up and Truncating a transaction log in SQL

I have a transaction log on on of my databases that is very big, and I need to backup the logs and truncate them to get some free disk space. The backup of the log needs to be placed on one of my remote backup servers. I would like to perform this task in SQL so I can put the code in a scheduled job. Thanks in advance for any help.Let me know if you mange to arrive at a defined process !!!
I do have the same issue|||Do you care about what is in the log? What I mean is do you think you will have to recover from the log backup or not? If not, you can just dump it without creating a backup file. Then you can shrink the log file using DBCC Shrinkfile.|||My company likes to keep a backup of all logs no matter what for auditing purposes.|||You need to set up a shared folder in the reomote server and run the following script,

BACKUP LOG mydb TO DISK = '\\remoteservername\sharedfoledername\mydb_log_bac kup' WITH INIT, NOUNLOAD, NAME = 'mydb_log_backup', NOSKIP , STATS = 10, NOFORMAT|||Would that script also truncate the logs, or do I have to run BACKUP LOG DB WITH TRUNCATE_ONLY afterwards. Also what extension do I use for the backup log file. Example: DB_Logs_2003.bak. Is that OK? Thanks for all you help so far.|||??!! That's what I was asking you about. 'With Truncate only' will only dump the log for you without actually backing up the log for you. If you need keep the backup file then you should not use that option. The script DOES backup. Extension doesn't matter.|||Oh! I see what you are saying. Now I fully understand. Thanks for all the help.

Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnt
tractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.
|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.
|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnttractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Back up the transaction log for the database to free up some log space.

Guys,
I got this message and immediately backup the transaction log. It
fixed the problem.
We are using a third party (Legato) backup which is doing a full backup
of the sql. I am not too thrilled about this product.
Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
this problem? Do I still need to backup the transaction seperate?
Also, do I want to append to media or over write existing if I were to
backup the transaction only(I backed the server up daily, so the flat
files would go onto tape)?
I am on SQL 2000 SP4.
Thanks advance.
Tnttractng@.gmail.com wrote:
> Guys,
> I got this message and immediately backup the transaction log. It
> fixed the problem.
>
> We are using a third party (Legato) backup which is doing a full backup
> of the sql. I am not too thrilled about this product.
> Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> this problem? Do I still need to backup the transaction seperate?
> Also, do I want to append to media or over write existing if I were to
> backup the transaction only(I backed the server up daily, so the flat
> files would go onto tape)?
> I am on SQL 2000 SP4.
>
> Thanks advance.
> Tnt
Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
database backups. Create a scheduled SQL Agent job for each. Don't
use third-party agents, like Legato, for direct database backups, they
tend to be awkward to restore from if necessary. Use the built-in
commands to dump the database (and log) backups to disk, then use
Legato to backup those disk files.|||Tracy,
Would backing up using the GUI be enough? Does the GUI "Full Backup"
take care of the log problem that I had stated earlier or do I have to
do the transaction log backup separate?
Still new to the backup command so I don't want to use it yet till I
test it.
Thanks,
tnt
Tracy McKibben wrote:
> tractng@.gmail.com wrote:
> > Guys,
> >
> > I got this message and immediately backup the transaction log. It
> > fixed the problem.
> >
> >
> > We are using a third party (Legato) backup which is doing a full backup
> > of the sql. I am not too thrilled about this product.
> >
> > Anyways, if I use MS backup wizard, would choosing "Full Backup" fix
> > this problem? Do I still need to backup the transaction seperate?
> > Also, do I want to append to media or over write existing if I were to
> > backup the transaction only(I backed the server up daily, so the flat
> > files would go onto tape)?
> >
> > I am on SQL 2000 SP4.
> >
> >
> > Thanks advance.
> > Tnt
> Use the built-in BACKUP DATABASE and BACKUP LOG commands to do your
> database backups. Create a scheduled SQL Agent job for each. Don't
> use third-party agents, like Legato, for direct database backups, they
> tend to be awkward to restore from if necessary. Use the built-in
> commands to dump the database (and log) backups to disk, then use
> Legato to backup those disk files.|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
>
A full backup does not truncate the transaction log. I'm guessing,
since you're asking these questions, that you don't fully understand how
the transaction log is used. You have a couple of options:
1. Spend some time in Books Online, learning about how that mechanism
works, and how it protects you from data loss. Backing up the
transaction log at regular intervals gives you the ability to restore a
database to virtually any point in time. You first have to understand
how the log is used, and what to do with those backups if/when you need
them.
or
2. Put your databases into Simple mode, eliminating the need to
truncate the transaction log. You don't have to perform transaction log
backups, but you also have a higher risk of data loss.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
tractng@.gmail.com wrote:
> Tracy,
> Would backing up using the GUI be enough? Does the GUI "Full Backup"
> take care of the log problem that I had stated earlier or do I have to
> do the transaction log backup separate?
> Still new to the backup command so I don't want to use it yet till I
> test it.
> Thanks,
> tnt
>
No. As Tracy suggested, if you don't understand backups you should
study the documentation carefully before you do anything. If you fail
to do that then your data may be at risk.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Back up the transaction log for the database to free up some log s

I have a server with 80 GB free space. Logfile for the database is set to
autogrow (10%), unrestricted. The log file itself is right now about 25 MB.
This message can't be accurate, so I'm wondering what could cause this.
There was no activity that would cause the logfile to grow, certainly not 80
GB.
I've seen this intermittently, no standard, easily reproducible cause. In
this particular case, I was doing a defrag of the file system, and trying to
parse a query in an SSIS package.
I've googled this (of course) and found a few people who've seen similar
error messages with large amounts of free space, but nothing conclusive.
TIA
jdnIt is probably because your log file is so small and can't hold all the
transactions you are trying to put into it. That will force an autogrow. IF
the file can not grow fast enough you can get a timeout and this message.
Always keep plenty of free space in both the log and data files. And most
importantly do not turn on AutoShrink or run any jobs to shrink the files.
So grow your log file to a size that is large enough to hold all the
transactions you will do in the time period between the log backups and
leave it there. Also change the growth for a % to a fixed size of say40 or
50MB (your mileage may vary).
--
Andrew J. Kelly SQL MVP
"jdn" <jdn@.discussions.microsoft.com> wrote in message
news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
>I have a server with 80 GB free space. Logfile for the database is set to
> autogrow (10%), unrestricted. The log file itself is right now about 25
> MB.
> This message can't be accurate, so I'm wondering what could cause this.
> There was no activity that would cause the logfile to grow, certainly not
> 80
> GB.
> I've seen this intermittently, no standard, easily reproducible cause. In
> this particular case, I was doing a defrag of the file system, and trying
> to
> parse a query in an SSIS package.
> I've googled this (of course) and found a few people who've seen similar
> error messages with large amounts of free space, but nothing conclusive.
> TIA
> jdn|||Well, that makes as much sense as anything else I've read. We'll set that up
and see if the error recurs.
Thanks.
jdn
"Andrew J. Kelly" wrote:
> It is probably because your log file is so small and can't hold all the
> transactions you are trying to put into it. That will force an autogrow. IF
> the file can not grow fast enough you can get a timeout and this message.
> Always keep plenty of free space in both the log and data files. And most
> importantly do not turn on AutoShrink or run any jobs to shrink the files.
> So grow your log file to a size that is large enough to hold all the
> transactions you will do in the time period between the log backups and
> leave it there. Also change the growth for a % to a fixed size of say40 or
> 50MB (your mileage may vary).
> --
> Andrew J. Kelly SQL MVP
>
> "jdn" <jdn@.discussions.microsoft.com> wrote in message
> news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
> >I have a server with 80 GB free space. Logfile for the database is set to
> > autogrow (10%), unrestricted. The log file itself is right now about 25
> > MB.
> >
> > This message can't be accurate, so I'm wondering what could cause this.
> > There was no activity that would cause the logfile to grow, certainly not
> > 80
> > GB.
> >
> > I've seen this intermittently, no standard, easily reproducible cause. In
> > this particular case, I was doing a defrag of the file system, and trying
> > to
> > parse a query in an SSIS package.
> >
> > I've googled this (of course) and found a few people who've seen similar
> > error messages with large amounts of free space, but nothing conclusive.
> >
> > TIA
> > jdn
>
>

Back up the transaction log for the database to free up some log s

I have a server with 80 GB free space. Logfile for the database is set to
autogrow (10%), unrestricted. The log file itself is right now about 25 MB.
This message can't be accurate, so I'm wondering what could cause this.
There was no activity that would cause the logfile to grow, certainly not 80
GB.
I've seen this intermittently, no standard, easily reproducible cause. In
this particular case, I was doing a defrag of the file system, and trying to
parse a query in an SSIS package.
I've googled this (of course) and found a few people who've seen similar
error messages with large amounts of free space, but nothing conclusive.
TIA
jdn
It is probably because your log file is so small and can't hold all the
transactions you are trying to put into it. That will force an autogrow. IF
the file can not grow fast enough you can get a timeout and this message.
Always keep plenty of free space in both the log and data files. And most
importantly do not turn on AutoShrink or run any jobs to shrink the files.
So grow your log file to a size that is large enough to hold all the
transactions you will do in the time period between the log backups and
leave it there. Also change the growth for a % to a fixed size of say40 or
50MB (your mileage may vary).
Andrew J. Kelly SQL MVP
"jdn" <jdn@.discussions.microsoft.com> wrote in message
news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
>I have a server with 80 GB free space. Logfile for the database is set to
> autogrow (10%), unrestricted. The log file itself is right now about 25
> MB.
> This message can't be accurate, so I'm wondering what could cause this.
> There was no activity that would cause the logfile to grow, certainly not
> 80
> GB.
> I've seen this intermittently, no standard, easily reproducible cause. In
> this particular case, I was doing a defrag of the file system, and trying
> to
> parse a query in an SSIS package.
> I've googled this (of course) and found a few people who've seen similar
> error messages with large amounts of free space, but nothing conclusive.
> TIA
> jdn

Back up the transaction log for the database to free up some log s

I have a server with 80 GB free space. Logfile for the database is set to
autogrow (10%), unrestricted. The log file itself is right now about 25 MB.
This message can't be accurate, so I'm wondering what could cause this.
There was no activity that would cause the logfile to grow, certainly not 80
GB.
I've seen this intermittently, no standard, easily reproducible cause. In
this particular case, I was doing a defrag of the file system, and trying to
parse a query in an SSIS package.
I've googled this (of course) and found a few people who've seen similar
error messages with large amounts of free space, but nothing conclusive.
TIA
jdnIt is probably because your log file is so small and can't hold all the
transactions you are trying to put into it. That will force an autogrow. IF
the file can not grow fast enough you can get a timeout and this message.
Always keep plenty of free space in both the log and data files. And most
importantly do not turn on AutoShrink or run any jobs to shrink the files.
So grow your log file to a size that is large enough to hold all the
transactions you will do in the time period between the log backups and
leave it there. Also change the growth for a % to a fixed size of say40 or
50MB (your mileage may vary).
Andrew J. Kelly SQL MVP
"jdn" <jdn@.discussions.microsoft.com> wrote in message
news:AD74E005-810D-42E0-8704-F56C0157CD2E@.microsoft.com...
>I have a server with 80 GB free space. Logfile for the database is set to
> autogrow (10%), unrestricted. The log file itself is right now about 25
> MB.
> This message can't be accurate, so I'm wondering what could cause this.
> There was no activity that would cause the logfile to grow, certainly not
> 80
> GB.
> I've seen this intermittently, no standard, easily reproducible cause. In
> this particular case, I was doing a defrag of the file system, and trying
> to
> parse a query in an SSIS package.
> I've googled this (of course) and found a few people who've seen similar
> error messages with large amounts of free space, but nothing conclusive.
> TIA
> jdn

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
Thanks
You cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks
|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks
|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be back
ed up. What are some of the practices of backing up System DBs as far as me
thod, frequency and what type of situation would there be a need to restore
from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =
recovery model.
Daily full backup should easily suffice unless you are in the habit of =
creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =
be backed up. What are some of the practices of backing up System DBs =
as far as method, frequency and what type of situation would there be a =
need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log
backup (because of
backup history is stored there). As Agent set msdb to simple recovery at sta
rtup, I just have a job
scheduled to autostart that set msdb to full recovery.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up.
What are some
of the practices of backing up System DBs as far as method, frequency and wh
at type of situation
would there be a need to restore from them?
> Thanks

Back up system DBs

I noticed the transaction logs for master and other system dbs can't be backed up. What are some of the practices of backing up System DBs as far as method, frequency and what type of situation would there be a need to restore from them?
ThanksYou cannot backup logs for the syetm dbs because they are in simple =recovery model.
Daily full backup should easily suffice unless you are in the habit of =creating many databases or logins per day.
Mike John
"Niles" <Niles@.discussions.microsoft.com> wrote in message =news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't =be backed up. What are some of the practices of backing up System DBs =as far as method, frequency and what type of situation would there be a =need to restore from them?
> Thanks|||Hi,
Backup the system databases once a day. I recommend you to take a backup of
Master database inbetween
if you add a new database/new login/ adding roles / changing
configurations/....
Take the backup of MSDB inbetween if you add or modify a Job, add
operator/alert, replications , Logshipping...
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be
backed up. What are some of the practices of backing up System DBs as far
as method, frequency and what type of situation would there be a need to
restore from them?
> Thanks|||I prefer to do only db backup for master and model. For msdb, I also do log backup (because of
backup history is stored there). As Agent set msdb to simple recovery at startup, I just have a job
scheduled to autostart that set msdb to full recovery.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:3BE5FB23-C2DC-42CB-9572-BB7A0C7A03DF@.microsoft.com...
> I noticed the transaction logs for master and other system dbs can't be backed up. What are some
of the practices of backing up System DBs as far as method, frequency and what type of situation
would there be a need to restore from them?
> Thanks

Back up File Location

I have created a schedule of transaction log backups, 1
every hour from 9am to 8pm with a differential back up
every 4 hours 1pm, 5pm and 9pm and full back ups once a
week. Under the advice of tech support the transaction log
backups should go to an area on another Server rather than
a tape. I want to try and keep the back up sets together,
so i will have a history of Full backup, Differential
backup and Transaction Log backs up in a single set. So i
want to use the same location for the Full and
differential backups. However tech support are going
bananas, saying that i shouldn't be moving so much data
accros the network. They can see that hourly transaction
logs should not be done to tape but are arguing that
weekly full backups should. The databases are not very big
in my eyes), a total of about 600mb and the full backups
run out of hours on a (sunday). As i said before, i would
like to have on backup set, not Full bacckups in one set
on tape and transaction log backups in another set on disk.
Any thoughts will be much appreciated as its me against
the world at the moment...Why not quite simply backup to disk and have those files picked up by your
tape software, like most of us are doing? :-)
For extra safety, you can add a job step which copies the backup file to
another machine directly after the backup is performed. (Personally, I don't
do the backup over the network if I can avoid. I prefer to backup locally
and copy the file after. If database size permits, of course.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"MAT" <anonymous@.discussions.microsoft.com> wrote in message
news:247501c3fc77$17ead6c0$a001280a@.phx.gbl...
> I have created a schedule of transaction log backups, 1
> every hour from 9am to 8pm with a differential back up
> every 4 hours 1pm, 5pm and 9pm and full back ups once a
> week. Under the advice of tech support the transaction log
> backups should go to an area on another Server rather than
> a tape. I want to try and keep the back up sets together,
> so i will have a history of Full backup, Differential
> backup and Transaction Log backs up in a single set. So i
> want to use the same location for the Full and
> differential backups. However tech support are going
> bananas, saying that i shouldn't be moving so much data
> accros the network. They can see that hourly transaction
> logs should not be done to tape but are arguing that
> weekly full backups should. The databases are not very big
> in my eyes), a total of about 600mb and the full backups
> run out of hours on a (sunday). As i said before, i would
> like to have on backup set, not Full bacckups in one set
> on tape and transaction log backups in another set on disk.
> Any thoughts will be much appreciated as its me against
> the world at the moment...
>|||Create a backup LAN between SQL server and backup destination server.
Backup on backup LAN IP address.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"MAT" <anonymous@.discussions.microsoft.com> wrote in message
news:247501c3fc77$17ead6c0$a001280a@.phx.gbl...
> I have created a schedule of transaction log backups, 1
> every hour from 9am to 8pm with a differential back up
> every 4 hours 1pm, 5pm and 9pm and full back ups once a
> week. Under the advice of tech support the transaction log
> backups should go to an area on another Server rather than
> a tape. I want to try and keep the back up sets together,
> so i will have a history of Full backup, Differential
> backup and Transaction Log backs up in a single set. So i
> want to use the same location for the Full and
> differential backups. However tech support are going
> bananas, saying that i shouldn't be moving so much data
> accros the network. They can see that hourly transaction
> logs should not be done to tape but are arguing that
> weekly full backups should. The databases are not very big
> in my eyes), a total of about 600mb and the full backups
> run out of hours on a (sunday). As i said before, i would
> like to have on backup set, not Full bacckups in one set
> on tape and transaction log backups in another set on disk.
> Any thoughts will be much appreciated as its me against
> the world at the moment...
>

Back to basic question

And forgive me for asking this over and over again..
My distribution agent states " 1 transaction with 100,000 commands were
delivered "
My default profile has the Commitbatchsize as 100 and CommitBatchThreshold
as 1000.
So in this case, how does distribution agent work ? Does it read/buffer all
the 100,000 commands from msrepl_commands since its 1 transaction before it
fires them off to the subscriber ?
And how does the default profile play a role here ? If it does read/buffers
100,000 commands before it pushes it across, does it commit 1000 commands at
a time on the subscriber due to the commitbatchthreshold ? And if if fails
halfway saying after inserting 50,000 commands, does it just rollback the
last few commands that were not committed or does it rollback all 50,000
commands ?
Using transactional replication
Hassan,
why not do a test of these parameters? If you have Lumigent's logreader, you
can use it on the the live subscriber transaction log to verify the
behaviour for yourself. Create and publish a 100 record test table on the
publisher, modify the Commitbatchsize and CommitBatchThreshold parameters
and make an update of the 100 records. There's no need for an explicit
transaction, just an update of the table that affects all rows is ordinarily
logged as 100 updates with a begin and commit surrounding it. Start the
distribution agent then check Lumigent's output. The same setup can be used
for interrupted commands.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

back log dbname with no_log

Hi,

I'm issuing the following statements to reduce the size of transaction log, My database is in simple recovery mode:

CHECKPOINT

GO

backup log TMDATABASE with truncate_only

go

dbcc shrinkfile (TMDATABASE_Log,2)

GO

The log file size still remains the same. Even though there are no active or open transactions...

Any insight greatly appreciated....

Thanks,

Razi

Perhaps the Log file is at either its defined size, or there is no free space.

These are useful sources:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

Also refer to Books Online, Topic: 'DBCC ShrinkFile'.

The File Does Not Shrink

If the shrink operation runs without error, but the file does not appear to have changed in size, verify that the file has adequate free space to remove by performing one of the following operations:

Run the following query.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

Run the DBCC SQLPERF command to return the space used in the transaction log.

|||

Hi,

Thanks for the reply but my log is still not shrinking. I tried using

"DBCC SQLPERF(logspace)" which shows Logsize = 499.875 and Log used = 0.04083052 0

This database in under compatibilty mode 80.

Also tried altering database file sizes using the GUI and ALTER DATABASE command but that didnt work since the initial size was larger.

There are no current connections to the database. Any insight?

Regards,

Razi

Back Issue

Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed, status =
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)