Backup solution - question about transaction log

I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.

Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.

The T-SQL being run in each case was

Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:

BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD

5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,

BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db')
RESTORE VERIFYONLY FROM <device> WITH FILE = @.i

This is then backed up to tape each night.

Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.

If I issue a

BACKUP LOG <database> to <device> WITH INIT, SKIP

then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?

Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?

Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?

Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated!
Matt

By default in the backup database command if you do not specify anything it will append ,eg...

backup database xxxx to disk='F:\test\xxxx.bak' and check the bak file size

now again reissue the same command,

backup database xxxx to disk='F:\test\xxxx.bak' and once again check the bak file size.......it will be double the size

next time issue the same command using WITH INIT option,

backup database xxxx to disk='F:\test\xxxx.bak' WITH INIT now see the bak file size it will be the same size as in 1st case.........if you do not specify anything SQL server will use the default With NOINIT option..........so you need to specify them explicitly if you needed to overwrite........

the log gets truncated if you specify the command,

backup log dbname with truncate_only.........this command will be depreciated in future versions........once you issue this comand, all the committed transactions will be rolled forward and written to data file and uncommitted trans will be rolled back.......

since you have a mission critical db i suggest you go for Log shipping or Database mirroring in sql 2005.........instead of relying on your db backups.........if you have sufficient disk space go for full backup and if you feel the db is important and if you want to have db consistency you can have tran log backups and differential backups else not required........

|||Am I right about when the transaction log should get truncated - i.e., on a full database backup?|||

You are right, that the transaction log should be truncated after a full backup. However, note that the contents of the log will be truncated and the physical file will maintain its size. Therefore, you may see a large transaction log file although it won't be full. In order to reduce the size of the file, you'll need to run some form of SHRINKFILE operation.

HTH!

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileNameAS VARCHAR(200)
DECLARE @.HoursAS CHAR(02)
DECLARE @.MinsAS CHAR(02)
DECLARE @.SecsAS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTimeAS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.
Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:

> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileNameAS VARCHAR(200)
> DECLARE @.HoursAS CHAR(02)
> DECLARE @.MinsAS CHAR(02)
> DECLARE @.SecsAS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTimeAS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although th
e
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:

> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script
is
> running well, but the old backups are not deleted from the disk, although
the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script t
o
> ensure that backups are deleted successfully? Thanks in advance for your
> help.

Backup Script Revisions Help

We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.sql

Backup Questions

If I have a full backup scheduled at 6pm and a scheduled transaction log backup begins during the 15 minutes the full takes to complete, how would I use the transaction log backup during a recovery sequence?

Does the transaction log backup contain transactions that are captured in the full recovery since they both occured concurrently? Using RESTORE HEADERONLY I see they both are stamped as finishing at the same time.

Would I restore the full and then restore this tran log backup or go to the next tran log backup?

Thanks,
KenThe rule is that tran log backups can only apply to the previous full backup. You should adjust your tran log schedule so that it's not running while the full backup is running. Your tran log backp, the one that finished 'at the same time' as the full backup is of no use. However, if you use Enterprise Manager and go throught the Restore window and show history, you can see the sequence of tran log backups relative to the full backup.

If you use Maintenance Plans, you can create a backup plan. However, if you decide to do it yourself using jobs, dump devices and so on, you will have more work on your hands to sort it out correctly. I personally think the extra work is worthwhile expecially for important systems - the ones you can't afford to lose.

My advice is to understand these issues very clearly and do some test restores into another database using differing database recovery scenarios.

Clive

Backup Question 100234987492874

Hi all,
The database in question uses Full Recovery Model but my maintenance plan
dictates to only keep 2 days of transaction log files. This has always
worked great.
For no obvious reason the backup no longer removes transaction files older
than 2 days so I am quickly running out of disk space. Again, nothing has
changed on this server, we keep detailed change management logs. I am
assuming that if I just blast the old .TRN files I will be fine but am still
puzzled why this behavior started and how to correctly stop it.
ThanksHi,
I have noticed this if the backup/transaction backup job in the schedule
doesn't complete successfully
--
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O106PHfuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> The database in question uses Full Recovery Model but my maintenance plan
> dictates to only keep 2 days of transaction log files. This has always
> worked great.
> For no obvious reason the backup no longer removes transaction files older
> than 2 days so I am quickly running out of disk space. Again, nothing has
> changed on this server, we keep detailed change management logs. I am
> assuming that if I just blast the old .TRN files I will be fine but am
still
> puzzled why this behavior started and how to correctly stop it.
> Thanks
>|||Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message news:O106PHfuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> The database in question uses Full Recovery Model but my maintenance plan
> dictates to only keep 2 days of transaction log files. This has always
> worked great.
> For no obvious reason the backup no longer removes transaction files older
> than 2 days so I am quickly running out of disk space. Again, nothing has
> changed on this server, we keep detailed change management logs. I am
> assuming that if I just blast the old .TRN files I will be fine but am still
> puzzled why this behavior started and how to correctly stop it.
> Thanks
>

Backup question : Append existing

We are using SQL Server 2005.
I created a maintenance plan to backup the database and the transaction
log.When I click on "modify" on the backup plan, it says something like the
following:
Back up Database task
Backup database on local server
Databases: mydatabase
Type: Full
Append existing -->> what does this mean ?
Destination: disk
What does "Append existing" mean ? I see this on the transaction log backup
plan also.
Thank you.Append existing means that it will append the current backup to the file, if
it exists. So if this job runs 4 times, your backup file will have all 4
backups in it, instead of overwriting it with the latest backup (leaving the
box unchecked will overwrite).
Kyle Hanrahan, MCSE, MCTS (SQL Server)
"fniles" <fniles@.pfmail.com> wrote in message
news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
> We are using SQL Server 2005.
> I created a maintenance plan to backup the database and the transaction
> log.When I click on "modify" on the backup plan, it says something like
> the following:
> Back up Database task
> Backup database on local server
> Databases: mydatabase
> Type: Full
> Append existing -->> what does this mean ?
> Destination: disk
> What does "Append existing" mean ? I see this on the transaction log
> backup plan also.
> Thank you.
>|||You have lots of questions about Transaction Log and BACKUP command however
you insist not reading the document links we give you but you ask here.
I think you better don't be lazy to read them. You'll find most of your
questions' answer in those documens and you'll be learned about these topics
better.
--
Ekrem Önsoy
"fniles" <fniles@.pfmail.com> wrote in message
news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
> We are using SQL Server 2005.
> I created a maintenance plan to backup the database and the transaction
> log.When I click on "modify" on the backup plan, it says something like
> the following:
> Back up Database task
> Backup database on local server
> Databases: mydatabase
> Type: Full
> Append existing -->> what does this mean ?
> Destination: disk
> What does "Append existing" mean ? I see this on the transaction log
> backup plan also.
> Thank you.
>|||"Append existing" does not mean it will not TRUNCATE the transaction log
during backup, right ? (it will still TRUNCATE the log, right) ?
>leaving the box unchecked will overwrite
What box is this ? When I "modify" on the backup plan, the only checkbox I
see is "verify backup integrity" which I have checked.
Thank you.
"Kyle R. Hanrahan" <vbnetguy@.newsgroup.nospam> wrote in message
news:%23LzlzjKSIHA.5136@.TK2MSFTNGP04.phx.gbl...
> Append existing means that it will append the current backup to the file,
> if it exists. So if this job runs 4 times, your backup file will have all
> 4 backups in it, instead of overwriting it with the latest backup (leaving
> the box unchecked will overwrite).
> Kyle Hanrahan, MCSE, MCTS (SQL Server)
> "fniles" <fniles@.pfmail.com> wrote in message
> news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server 2005.
>> I created a maintenance plan to backup the database and the transaction
>> log.When I click on "modify" on the backup plan, it says something like
>> the following:
>> Back up Database task
>> Backup database on local server
>> Databases: mydatabase
>> Type: Full
>> Append existing -->> what does this mean ?
>> Destination: disk
>> What does "Append existing" mean ? I see this on the transaction log
>> backup plan also.
>> Thank you.
>|||This may have been a language translation issue, but your comment seems
excessively rude based on fniles trying to fully understand the T-LOG backup
process. I see others ask similar questions every day.
--
Kevin3NF
"Ekrem Önsoy" <ekrem@.compecta.com> wrote in message
news:A41935D5-64FA-4FD9-8987-068A61026567@.microsoft.com...
> You have lots of questions about Transaction Log and BACKUP command
> however you insist not reading the document links we give you but you ask
> here.
> I think you better don't be lazy to read them. You'll find most of your
> questions' answer in those documens and you'll be learned about these
> topics better.
> --
> Ekrem Önsoy
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server 2005.
>> I created a maintenance plan to backup the database and the transaction
>> log.When I click on "modify" on the backup plan, it says something like
>> the following:
>> Back up Database task
>> Backup database on local server
>> Databases: mydatabase
>> Type: Full
>> Append existing -->> what does this mean ?
>> Destination: disk
>> What does "Append existing" mean ? I see this on the transaction log
>> backup plan also.
>> Thank you.
>|||I didn't mean to be rude for sure and I've answered more than a thousand
times in these newsgroups. I just stressed that he should read the documents
we directed him to understand about the topics he asked.
Besides, I don't think this is your business.
--
Ekrem Önsoy
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23P$kXMLSIHA.1212@.TK2MSFTNGP05.phx.gbl...
> This may have been a language translation issue, but your comment seems
> excessively rude based on fniles trying to fully understand the T-LOG
> backup process. I see others ask similar questions every day.
> --
> Kevin3NF
> "Ekrem Önsoy" <ekrem@.compecta.com> wrote in message
> news:A41935D5-64FA-4FD9-8987-068A61026567@.microsoft.com...
>> You have lots of questions about Transaction Log and BACKUP command
>> however you insist not reading the document links we give you but you ask
>> here.
>> I think you better don't be lazy to read them. You'll find most of your
>> questions' answer in those documens and you'll be learned about these
>> topics better.
>> --
>> Ekrem Önsoy
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server 2005.
>> I created a maintenance plan to backup the database and the transaction
>> log.When I click on "modify" on the backup plan, it says something like
>> the following:
>> Back up Database task
>> Backup database on local server
>> Databases: mydatabase
>> Type: Full
>> Append existing -->> what does this mean ?
>> Destination: disk
>> What does "Append existing" mean ? I see this on the transaction log
>> backup plan also.
>> Thank you.
>>
>|||Thank you very much Kevin for all your help and support. I greatly
appreciate it.
I did read the articles that Ekrem suggested me to read, but I still have
questions after that.
I am not a DBA, I am a programmer, our company does not have a DBA, so I
also need to do the database maintenance stuffs.
Forgive me if I ask too many questions.
"Ekrem Önsoy" <ekrem@.compecta.com> wrote in message
news:58C847C2-2754-40D2-8EC3-011063A6A1F7@.microsoft.com...
>I didn't mean to be rude for sure and I've answered more than a thousand
>times in these newsgroups. I just stressed that he should read the
>documents we directed him to understand about the topics he asked.
> Besides, I don't think this is your business.
> --
> Ekrem Önsoy
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23P$kXMLSIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> This may have been a language translation issue, but your comment seems
>> excessively rude based on fniles trying to fully understand the T-LOG
>> backup process. I see others ask similar questions every day.
>> --
>> Kevin3NF
>> "Ekrem Önsoy" <ekrem@.compecta.com> wrote in message
>> news:A41935D5-64FA-4FD9-8987-068A61026567@.microsoft.com...
>> You have lots of questions about Transaction Log and BACKUP command
>> however you insist not reading the document links we give you but you
>> ask here.
>> I think you better don't be lazy to read them. You'll find most of your
>> questions' answer in those documens and you'll be learned about these
>> topics better.
>> --
>> Ekrem Önsoy
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server 2005.
>> I created a maintenance plan to backup the database and the transaction
>> log.When I click on "modify" on the backup plan, it says something like
>> the following:
>> Back up Database task
>> Backup database on local server
>> Databases: mydatabase
>> Type: Full
>> Append existing -->> what does this mean ?
>> Destination: disk
>> What does "Append existing" mean ? I see this on the transaction log
>> backup plan also.
>> Thank you.
>>
>>
>|||Please do not get me wrong, your questions are always welcome.
That's why I and my other friends are here, we like to answer questions.
However, sometimes people abuse the good intentions and they don't bother
theirselves to read the documents we suggest them to read because they find
it's easier asking. And when I see that you still were asking about the same
topic, I thought you did not even look at the documents I pointed for you.
So I stressed that would be reading them.
You can ask when you need fniles.
Peace! =)
--
Ekrem Önsoy
"fniles" <fniles@.pfmail.com> wrote in message
news:ejHR23MSIHA.4128@.TK2MSFTNGP06.phx.gbl...
> Thank you very much Kevin for all your help and support. I greatly
> appreciate it.
> I did read the articles that Ekrem suggested me to read, but I still have
> questions after that.
> I am not a DBA, I am a programmer, our company does not have a DBA, so I
> also need to do the database maintenance stuffs.
> Forgive me if I ask too many questions.
> "Ekrem Önsoy" <ekrem@.compecta.com> wrote in message
> news:58C847C2-2754-40D2-8EC3-011063A6A1F7@.microsoft.com...
>>I didn't mean to be rude for sure and I've answered more than a thousand
>>times in these newsgroups. I just stressed that he should read the
>>documents we directed him to understand about the topics he asked.
>> Besides, I don't think this is your business.
>> --
>> Ekrem Önsoy
>>
>> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
>> news:%23P$kXMLSIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> This may have been a language translation issue, but your comment seems
>> excessively rude based on fniles trying to fully understand the T-LOG
>> backup process. I see others ask similar questions every day.
>> --
>> Kevin3NF
>> "Ekrem Önsoy" <ekrem@.compecta.com> wrote in message
>> news:A41935D5-64FA-4FD9-8987-068A61026567@.microsoft.com...
>> You have lots of questions about Transaction Log and BACKUP command
>> however you insist not reading the document links we give you but you
>> ask here.
>> I think you better don't be lazy to read them. You'll find most of your
>> questions' answer in those documens and you'll be learned about these
>> topics better.
>> --
>> Ekrem Önsoy
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:ugFXVaKSIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server 2005.
>> I created a maintenance plan to backup the database and the
>> transaction log.When I click on "modify" on the backup plan, it says
>> something like the following:
>> Back up Database task
>> Backup database on local server
>> Databases: mydatabase
>> Type: Full
>> Append existing -->> what does this mean ?
>> Destination: disk
>> What does "Append existing" mean ? I see this on the transaction log
>> backup plan also.
>> Thank you.
>>
>>
>

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:
> I have set up my server to use the simple recovery model. I understood this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:

> I have set up my server to use the simple recovery model. I understood thi
s
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GORob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.co
mcast.com...
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.co
mcast.com...
>|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.co
mcast.com...
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.co
mcast.com...
>

Backup question

Hi All,

I have a database backup that was taken at 10:00 pm and a transaction log backup that was taken at 12:00 pm. If I run a database backup at 2:00 pm, the transactions that are in the 12:00 pm transaction log backup will be committed to the database, and I can safely delete the 12:00 pm transaction log backup. Am I right? Thanks.See scenario below:

Monday 10PM - full db backup
Tuesday 12PM - transaction log backup
Tuesday 2PM - full db backup

The Tuesday 2PM backup contains transactions backed up at 12PM the same day. If the assumption of your explanation is correct, - then the transaction log backup is not needed if you intend to restore the full backup from 2PM.|||Would there be a situation when I want to use Monday 10PM full db backup instead of Tuesday 2PM full db backup for restore? Don't I always want to use the latest db backup?|||The transactions in your backup log are committed anyway.
A database backup is fully recoverable, and does not require prior logs.
You may STILL want to keep your logs, because they enable you to perform "point-in-time" restores, which database backups alone do not allow.|||Would there be a situation when I want to use Monday 10PM full db backup instead of Tuesday 2PM full db backup for restore? Don't I always want to use the latest db backup?
See my above response. You should keep a cycle of backups and logs to allow you to perform point-in-time data recovery. For those times when the accounting department tells you "we just found out today that we goofed up last week".

BackUp Question

When you do a full back-up of a sql server db, does that include the transaction logs or do they still have to be backed up seperately?
Thanks!Depends what your question is - does a full backup include all transactions in T-log? Yes. Does full backup checkpoint the T-log and truncate it? No. Does it shrink the T-log in a full backup? No.

To test this run this command:

dbcc sqlperf('logspace')

this will show you the percentage of your T-log used. Run a full backup then run the command again and you will see that the percentage did not go down. If you are having T-log growth problems, you probably want to back it up periodically.

Another good command to know for the T-log is this one:

select * from ::fn_dblog(null,null)

This will show you the entries in your active T-log.

HTH

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GO
Rob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com. ..
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>
|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com. ..
>
|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com. ..
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>
|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com. ..
>
|||i need to creat a log.ldf file . i am only having the Data.Mdf file with me. I does'nt want to attach the database but i want to only create the log.ldf file how can i creat it.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?
Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?
|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:

> I have set up my server to use the simple recovery model. I understood this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

Backup question

Using sql server 2000...
We are having issues with growing transaction log files...
Is the following a safe practice ?
Assume you create a "full" backup job using the Database Maintenance plan
wizard... This will create Step 1 in an SQL Job.
Would it be safe to add the following code as a step that was executed "on
success" of the first...
Use DatabaseName
BACKUP LOG DatabaseNameWITH NO_LOG
DBCC SHRINKFILE(DatabaseName_Log,10)
GORob
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rob" <robc1@.yahoo.com> wrote in message
news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
> Using sql server 2000...
> We are having issues with growing transaction log files...
> Is the following a safe practice ?
> Assume you create a "full" backup job using the Database Maintenance plan
> wizard... This will create Step 1 in an SQL Job.
> Would it be safe to add the following code as a step that was executed
> "on success" of the first...
> Use DatabaseName
> BACKUP LOG DatabaseNameWITH NO_LOG
> DBCC SHRINKFILE(DatabaseName_Log,10)
> GO
>|||Thanks Uri,
But I still need an interpreter...
Left unchecked, the Log file appears to grow and fill up space on the
server. My assumption, is that IF a good full backup has been successful,
then it should be OK to use SHRINKFILE to lose the transaction file. At
that point, there would be no need to restore a transaction log file...
BTW - I never use shrinkdatabase
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Rob
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance plan
>> wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>|||Rob
If you have database is set up withj FULL RECOVERY mode then in order to
control a physical size of the LOG file , you need to BACKUP LOG ...
command, otherwise set up the database with SIMPLE recovery mode and the SQL
Server will take care for it.
If you implement BACKUP LOG file you probably won't lose the data if the
database get corrupted , please make sure what is your/or your boss
requirements.
"Rob" <robc1@.yahoo.com> wrote in message
news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com...
> Thanks Uri,
> But I still need an interpreter...
> Left unchecked, the Log file appears to grow and fill up space on the
> server. My assumption, is that IF a good full backup has been successful,
> then it should be OK to use SHRINKFILE to lose the transaction file. At
> that point, there would be no need to restore a transaction log file...
> BTW - I never use shrinkdatabase
> Rob
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>> Rob
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance
>> plan wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>>
>|||Hello,
To add on to Uri, perform the transaction log backup in regular intervals
(say 15 minutes), this will make sure that your
log (LDF) file will not grow. Shrinking the LDF life after the log backup is
not a good approch, this will shrino the
LDF file and after each DML operation file will autogrow and this will take
consifderable amount of I/O resources.
One more thing is you can archive all your transaction log backup files
which was taken before the
last full database backup.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMZ9F$5bHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Rob
> If you have database is set up withj FULL RECOVERY mode then in order to
> control a physical size of the LOG file , you need to BACKUP LOG ...
> command, otherwise set up the database with SIMPLE recovery mode and the
> SQL Server will take care for it.
>
> If you implement BACKUP LOG file you probably won't lose the data if the
> database get corrupted , please make sure what is your/or your boss
> requirements.
>
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:bPidnfI0R4aaK5rbnZ2dnUVZ_hmtnZ2d@.comcast.com...
>> Thanks Uri,
>> But I still need an interpreter...
>> Left unchecked, the Log file appears to grow and fill up space on the
>> server. My assumption, is that IF a good full backup has been
>> successful, then it should be OK to use SHRINKFILE to lose the
>> transaction file. At that point, there would be no need to restore a
>> transaction log file...
>> BTW - I never use shrinkdatabase
>> Rob
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uyC4Ho5bHHA.4716@.TK2MSFTNGP02.phx.gbl...
>> Rob
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:ANudneEo7LraMJrbnZ2dnUVZ_segnZ2d@.comcast.com...
>> Using sql server 2000...
>> We are having issues with growing transaction log files...
>> Is the following a safe practice ?
>> Assume you create a "full" backup job using the Database Maintenance
>> plan wizard... This will create Step 1 in an SQL Job.
>> Would it be safe to add the following code as a step that was executed
>> "on success" of the first...
>> Use DatabaseName
>> BACKUP LOG DatabaseNameWITH NO_LOG
>> DBCC SHRINKFILE(DatabaseName_Log,10)
>> GO
>>
>>
>|||i need to creat a log.ldf file . i am only having the Data.Mdf file with me. I does'nt want to attach the database but i want to only create the log.ldf file how can i creat it.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Backup practices ...

Isn't it better to backup databases and transaction logs to a different
drive than the data files? Both for reliability (if the drive dies you're
hurting since the backup is there as well) and effeciency (less contention
on the data drive). If you're backing up everything to the same drive as the
data, can this cause processor spikes? We do backup to tape as well, but
this is not as fast to recover from when there are a zillion users.
As you probably guessed, some pre-existing maintence plans are set up this
way and I'm thinking I should push to get them changed. But this may require
additional hardware so somebody has to write a check. You know the drill.
Thanks,
Bob Castleman
DBA Poseur"Bob Castleman" <nomail@.here> wrote in message
news:e$madNqaFHA.3684@.TK2MSFTNGP12.phx.gbl...
> Isn't it better to backup databases and transaction logs to a different
> drive than the data files? Both for reliability (if the drive dies you're
> hurting since the backup is there as well) and effeciency (less contention
> on the data drive). If you're backing up everything to the same drive as
the
> data, can this cause processor spikes? We do backup to tape as well, but
> this is not as fast to recover from when there are a zillion users.
> As you probably guessed, some pre-existing maintence plans are set up this
> way and I'm thinking I should push to get them changed. But this may
require
> additional hardware so somebody has to write a check. You know the drill.
> Thanks,
> Bob Castleman
> DBA Poseur
>
Actually Bob, you should always do your backups to the same local drives as
the active databases are on. In fact, I'm not sure why you are even
bothering with doing backups at all... <wink>
You are correct, a good backup (and recovery) strategy should be
implemented. Backing up to the same hard disks is dependent on a lot of
different factors.
1. How much activity is going on during the backup process?
2. How big are the backups that we are talking about?
3. How much network traffic do you have already (if you wanted to push your
backups to a UNC name on a different computer).
That said, having your backups in contention with the active databases on
the same physical drives is almost never a good idea (for the reasons you
listed).
Rick Sawtell|||
> Actually Bob, you should always do your backups to the same local drives
> as
> the active databases are on. In fact, I'm not sure why you are even
> bothering with doing backups at all... <wink>
Facetiously sarcastic. Gotta luv it!

> 1. How much activity is going on during the backup process?
Log backup during the production day every four hours spike the processor
pretty hard. Full backups at night are not an issues, yet.

> 2. How big are the backups that we are talking about?
Several hundred databases. Transaction logs every four hours total about a
gig. Full nightly backups are about 100 gig.

> 3. How much network traffic do you have already (if you wanted to push
> your
> backups to a UNC name on a different computer).
>
None right now. Backups are over a fiber chanel to the RAID and so far
network traffic to the database servers is very low.|||"Bob Castleman" <nomail@.here> wrote in message
news:uKsEHrqaFHA.220@.TK2MSFTNGP10.phx.gbl...
>
> Log backup during the production day every four hours spike the processor
> pretty hard. Full backups at night are not an issues, yet.
>
Well, if you can't get the new hardware, how about doing TLog dumps more
often. You still have the pay the piper for the CPU time, but if they were
every hour instead of every 4 hours, your spikes should be shorter-lived.

> None right now. Backups are over a fiber chanel to the RAID and so far
> network traffic to the database servers is very low.
>
Very nice!!!
Rick Sawtell

backup plan

Hello,
I would to do a backup plan and I would like to know if someone can
help me with some advice?
What I have done until now is to do the transaction log (10:50 PM)
before the data backup (11:00) PM.
Do you think there is problem on doing that?
InaIf this plan includes new databases and you are using SQL Server 2000, the
transaction log backup could fail for these new databases.
Ben Nevarez, MCDBA, OCP
Database Administrator
"ina" wrote:

> Hello,
> I would to do a backup plan and I would like to know if someone can
> help me with some advice?
> What I have done until now is to do the transaction log (10:50 PM)
> before the data backup (11:00) PM.
> Do you think there is problem on doing that?
> Ina
>|||ina
I can tell you what I'm doing in my company. I have a FULL database backup
at 2PM and LOG backup every 30 minutes from 4PM to 1:30AM
It is matter of policy at your company how much data can you lose?
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146723393.832760.294060@.e56g2000cwe.googlegroups.com...
> Hello,
> I would to do a backup plan and I would like to know if someone can
> help me with some advice?
> What I have done until now is to do the transaction log (10:50 PM)
> before the data backup (11:00) PM.
> Do you think there is problem on doing that?
> Ina
>|||This plan includes new databases. I am pretty newbie on sql server. Do
you think it is better to postpone the transaction log such as 10 min
earier?
Ina|||Thank you Ben,
yes. it includes new database. I am pretty newbien in SQL Server 2000.
Do you think Do I need to postpone my T log to i.e 10 min earlier
Ina|||Thanks,
Let me understand Do I need the transaction log be backed up often. and
even for a small company? Could you explain me why? and could people
work even if the Transaction log backup is running?
Ina|||ina
It is up to you how often to backup log file or to backup log file at all ,
have you considered to set the database to SIMPLE recovery mode and
performing FULL backup once a day? Some people do the backup log file very
10 minutes because they cannot afford to lose data even in this period
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146725751.358542.287290@.j33g2000cwa.googlegroups.com...
> Thanks,
> Let me understand Do I need the transaction log be backed up often. and
> even for a small company? Could you explain me why? and could people
> work even if the Transaction log backup is running?
> Ina
>|||My database is set to FULL and my full backup once a day at 11:00 AM
and the backup log from 4 PM until 10:30 Pm every hour is it ok?
Is it better to set to SIMPLE if I set it I cannot perform transaction
log backup
Ina|||ina

> Is it better to set to SIMPLE if I set it I cannot perform transaction
> log backup
How important is to BACKUP LOG file for the company
Please read this article
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1146726890.661053.129620@.i40g2000cwc.googlegroups.com...
> My database is set to FULL and my full backup once a day at 11:00 AM
> and the backup log from 4 PM until 10:30 Pm every hour is it ok?
> Is it better to set to SIMPLE if I set it I cannot perform transaction
> log backup
> Ina
>|||it is 11:00 Pm sorry
Ina

Backup or move transaction logs to another server.

Can anyone advice me:
I'm backing up my transaction logs every 2 hours using the SQL Server
database management wizard.
I only keep the logs for two days before the wizard automatically
deletes the ones which are older than two days.
I want to include into the routine a process which will move the
transaction logs to a different server, but will also delete the logs
on the other server if they are older than 2 days. Is this possible?
Or is it possible to set SQL Server to backup the transaction logs to
another server using the wizard or some other means. I can only see
the local driver on the server, even if I've mapped one.
Many thanks in advancethis is from google.com
the default backup folder path is stored in the regisrty at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector
y
for a default SQL Server installation. You can edit the value by specifying
the required valid path.
Becareful while editing the registry.
--
HTH,
Vyas, MVP (SQL Server)
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0401200822.415cb11c@.posting.google.com...

quote:

---

> Can anyone advice me:
> I'm backing up my transaction logs every 2 hours using the SQL Server
> database management wizard.
> I only keep the logs for two days before the wizard automatically
> deletes the ones which are older than two days.
> I want to include into the routine a process which will move the
> transaction logs to a different server, but will also delete the logs
> on the other server if they are older than 2 days. Is this possible?
> Or is it possible to set SQL Server to backup the transaction logs to
> another server using the wizard or some other means. I can only see
> the local driver on the server, even if I've mapped one.
> Many thanks in advance

---

|||I have the same problem.
How does this solution help? Enterprise Manager still refuses to accept the
UNC address (as shown in BOL "sp_addumpdevice") and although sp_addumpdevice
will accept it (it accepts anything), the backup command complains that it
can't open the backup device. Here's the code:
exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local drive
exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
drive
exec sp_addumpdevice 'disk', 'device3', '\\Acqserver\R\Temp\backup.dat' --
same drive, UNC notation
backup database PC_DASC_DB to device1 -- this works
backup database PC_DASC_DB to device2 -- this fails
backup database PC_DASC_DB to device3 -- this fails
TIA,
Wm Schmidt
"ME" <mail@.moon.net> wrote in message
news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...

quote:

---

> this is from google.com
> the default backup folder path is stored in the regisrty at:
>

---

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
> y
> for a default SQL Server installation. You can edit the value by[/QUOTE]
specifying

quote:

---

> the required valid path.
> Becareful while editing the registry.
> --
> HTH,
> Vyas, MVP (SQL Server)
>
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0401200822.415cb11c@.posting.google.com...
>

---

|||if you use the wizard you don't have the choice. SQL wizard backup db and
logs to .BAK and .TRN files
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I have the same problem.
> How does this solution help? Enterprise Manager still refuses to accept

---

the

quote:

---

> UNC address (as shown in BOL "sp_addumpdevice") and although

---

sp_addumpdevice

quote:

---

> will accept it (it accepts anything), the backup command complains that it
> can't open the backup device. Here's the code:
> exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local

---

drive

quote:

---

> exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
> drive
> exec sp_addumpdevice 'disk', 'device3',

---

\\Acqserver\R\Temp\backup.dat' --

quote:

---

> same drive, UNC notation
> backup database PC_DASC_DB to device1 -- this works
> backup database PC_DASC_DB to device2 -- this fails
> backup database PC_DASC_DB to device3 -- this fails
> TIA,
> Wm Schmidt
>
> "ME" <mail@.moon.net> wrote in message
> news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
>

---

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
> specifying
>[/QUOTE]|||Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm running
this test using the Query Analyzer with the commands shown below. I changed
all the file names to backup.bak but it still doesn't work for network
drives.
"ME" <mail@.moon.net> wrote in message
news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> if you use the wizard you don't have the choice. SQL wizard backup db and
> logs to .BAK and .TRN files
>
> "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> the
> sp_addumpdevice
it[QUOTE]
> drive
> \\Acqserver\R\Temp\backup.dat' --
>

---

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
Server

quote:

---

logs[QUOTE]
to[QUOTE]
>

---

|||Statements are valid and probably you don't have valid permission on remote
machine.
Does your SQLServerAgent Service have a permission to write on remote
machine?
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OnHvwg53DHA.1264@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm running
> this test using the Query Analyzer with the commands shown below. I

---

changed

quote:

---

> all the file names to backup.bak but it still doesn't work for network
> drives.
>
> "ME" <mail@.moon.net> wrote in message
> news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
and[QUOTE]
accept[QUOTE]
that[QUOTE]
> it
remote[QUOTE]
>

---

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
> Server
> logs
possible?

quote:

---

> to
see[QUOTE]
>

---

|||Problem solved. It turns out that MSSQLSERVER was logged on as LocalSystem
(the default when a service is installed). By setting the login to
administrator I was able to successfully do a backup to a remote drive.
Apparently it doesn't matter that the remote drive is shared with full
permissions for Everyone. Or that SQLServerAgent was logged on as
administrator.
Thanks for your help in solving the problem.
William
"Ana Mihalj" <amihalj@.hotmail.com> wrote in message
news:%23COs1X$3DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Statements are valid and probably you don't have valid permission on

---

remote

quote:

---

> machine.
> Does your SQLServerAgent Service have a permission to write on remote
> machine?
>
>
>
> "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> news:OnHvwg53DHA.1264@.TK2MSFTNGP11.phx.gbl...
running[QUOTE]
> changed
> and
> accept
> that
local[QUOTE]
> remote
>

---

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\BackupDirector[QUO
TE]
automatically

quote:

---

> possible?
logs[QUOTE]
> see
>

---

Backup or move transaction logs to another server.

Can anyone advice me:
I'm backing up my transaction logs every 2 hours using the SQL Server
database management wizard.
I only keep the logs for two days before the wizard automatically
deletes the ones which are older than two days.
I want to include into the routine a process which will move the
transaction logs to a different server, but will also delete the logs
on the other server if they are older than 2 days. Is this possible?
Or is it possible to set SQL Server to backup the transaction logs to
another server using the wizard or some other means. I can only see
the local driver on the server, even if I've mapped one.
Many thanks in advancethis is from google.com
the default backup folder path is stored in the regisrty at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
y
for a default SQL Server installation. You can edit the value by specifying
the required valid path.
Becareful while editing the registry.
--
HTH,
Vyas, MVP (SQL Server)
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> Can anyone advice me:
> I'm backing up my transaction logs every 2 hours using the SQL Server
> database management wizard.
> I only keep the logs for two days before the wizard automatically
> deletes the ones which are older than two days.
> I want to include into the routine a process which will move the
> transaction logs to a different server, but will also delete the logs
> on the other server if they are older than 2 days. Is this possible?
> Or is it possible to set SQL Server to backup the transaction logs to
> another server using the wizard or some other means. I can only see
> the local driver on the server, even if I've mapped one.
> Many thanks in advance|||I have the same problem.
How does this solution help? Enterprise Manager still refuses to accept the
UNC address (as shown in BOL "sp_addumpdevice") and although sp_addumpdevice
will accept it (it accepts anything), the backup command complains that it
can't open the backup device. Here's the code:
exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local drive
exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
drive
exec sp_addumpdevice 'disk', 'device3', '\\Acqserver\R\Temp\backup.dat' --
same drive, UNC notation
backup database PC_DASC_DB to device1 -- this works
backup database PC_DASC_DB to device2 -- this fails
backup database PC_DASC_DB to device3 -- this fails
TIA,
Wm Schmidt
"ME" <mail@.moon.net> wrote in message
news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> this is from google.com
> the default backup folder path is stored in the regisrty at:
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> y
> for a default SQL Server installation. You can edit the value by
specifying
> the required valid path.
> Becareful while editing the registry.
> --
> HTH,
> Vyas, MVP (SQL Server)
>
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > Can anyone advice me:
> >
> > I'm backing up my transaction logs every 2 hours using the SQL Server
> > database management wizard.
> > I only keep the logs for two days before the wizard automatically
> > deletes the ones which are older than two days.
> >
> > I want to include into the routine a process which will move the
> > transaction logs to a different server, but will also delete the logs
> > on the other server if they are older than 2 days. Is this possible?
> > Or is it possible to set SQL Server to backup the transaction logs to
> > another server using the wizard or some other means. I can only see
> > the local driver on the server, even if I've mapped one.
> >
> > Many thanks in advance
>|||if you use the wizard you don't have the choice. SQL wizard backup db and
logs to .BAK and .TRN files
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> I have the same problem.
> How does this solution help? Enterprise Manager still refuses to accept
the
> UNC address (as shown in BOL "sp_addumpdevice") and although
sp_addumpdevice
> will accept it (it accepts anything), the backup command complains that it
> can't open the backup device. Here's the code:
> exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local
drive
> exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
> drive
> exec sp_addumpdevice 'disk', 'device3',
\\Acqserver\R\Temp\backup.dat' --
> same drive, UNC notation
> backup database PC_DASC_DB to device1 -- this works
> backup database PC_DASC_DB to device2 -- this fails
> backup database PC_DASC_DB to device3 -- this fails
> TIA,
> Wm Schmidt
>
> "ME" <mail@.moon.net> wrote in message
> news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > this is from google.com
> >
> > the default backup folder path is stored in the regisrty at:
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > y
> > for a default SQL Server installation. You can edit the value by
> specifying
> > the required valid path.
> >
> > Becareful while editing the registry.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> >
> >
> > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > Can anyone advice me:
> > >
> > > I'm backing up my transaction logs every 2 hours using the SQL Server
> > > database management wizard.
> > > I only keep the logs for two days before the wizard automatically
> > > deletes the ones which are older than two days.
> > >
> > > I want to include into the routine a process which will move the
> > > transaction logs to a different server, but will also delete the logs
> > > on the other server if they are older than 2 days. Is this possible?
> > > Or is it possible to set SQL Server to backup the transaction logs to
> > > another server using the wizard or some other means. I can only see
> > > the local driver on the server, even if I've mapped one.
> > >
> > > Many thanks in advance
> >
> >
>|||Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm running
this test using the Query Analyzer with the commands shown below. I changed
all the file names to backup.bak but it still doesn't work for network
drives.
"ME" <mail@.moon.net> wrote in message
news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
> if you use the wizard you don't have the choice. SQL wizard backup db and
> logs to .BAK and .TRN files
>
> "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > I have the same problem.
> >
> > How does this solution help? Enterprise Manager still refuses to accept
> the
> > UNC address (as shown in BOL "sp_addumpdevice") and although
> sp_addumpdevice
> > will accept it (it accepts anything), the backup command complains that
it
> > can't open the backup device. Here's the code:
> >
> > exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local
> drive
> > exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' -- remote
> > drive
> > exec sp_addumpdevice 'disk', 'device3',
> \\Acqserver\R\Temp\backup.dat' --
> > same drive, UNC notation
> >
> > backup database PC_DASC_DB to device1 -- this works
> >
> > backup database PC_DASC_DB to device2 -- this fails
> >
> > backup database PC_DASC_DB to device3 -- this fails
> >
> > TIA,
> > Wm Schmidt
> >
> >
> > "ME" <mail@.moon.net> wrote in message
> > news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > > this is from google.com
> > >
> > > the default backup folder path is stored in the regisrty at:
> > >
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > > y
> > > for a default SQL Server installation. You can edit the value by
> > specifying
> > > the required valid path.
> > >
> > > Becareful while editing the registry.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > >
> > >
> > > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > > Can anyone advice me:
> > > >
> > > > I'm backing up my transaction logs every 2 hours using the SQL
Server
> > > > database management wizard.
> > > > I only keep the logs for two days before the wizard automatically
> > > > deletes the ones which are older than two days.
> > > >
> > > > I want to include into the routine a process which will move the
> > > > transaction logs to a different server, but will also delete the
logs
> > > > on the other server if they are older than 2 days. Is this possible?
> > > > Or is it possible to set SQL Server to backup the transaction logs
to
> > > > another server using the wizard or some other means. I can only see
> > > > the local driver on the server, even if I've mapped one.
> > > >
> > > > Many thanks in advance
> > >
> > >
> >
> >
>|||Statements are valid and probably you don't have valid permission on remote
machine.
Does your SQLServerAgent Service have a permission to write on remote
machine?
"Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
news:OnHvwg53DHA.1264@.TK2MSFTNGP11.phx.gbl...
> Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm running
> this test using the Query Analyzer with the commands shown below. I
changed
> all the file names to backup.bak but it still doesn't work for network
> drives.
>
> "ME" <mail@.moon.net> wrote in message
> news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
> > if you use the wizard you don't have the choice. SQL wizard backup db
and
> > logs to .BAK and .TRN files
> >
> >
> > "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> > news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > > I have the same problem.
> > >
> > > How does this solution help? Enterprise Manager still refuses to
accept
> > the
> > > UNC address (as shown in BOL "sp_addumpdevice") and although
> > sp_addumpdevice
> > > will accept it (it accepts anything), the backup command complains
that
> it
> > > can't open the backup device. Here's the code:
> > >
> > > exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' -- local
> > drive
> > > exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' --
remote
> > > drive
> > > exec sp_addumpdevice 'disk', 'device3',
> > \\Acqserver\R\Temp\backup.dat' --
> > > same drive, UNC notation
> > >
> > > backup database PC_DASC_DB to device1 -- this works
> > >
> > > backup database PC_DASC_DB to device2 -- this fails
> > >
> > > backup database PC_DASC_DB to device3 -- this fails
> > >
> > > TIA,
> > > Wm Schmidt
> > >
> > >
> > > "ME" <mail@.moon.net> wrote in message
> > > news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > > > this is from google.com
> > > >
> > > > the default backup folder path is stored in the regisrty at:
> > > >
> > >
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > > > y
> > > > for a default SQL Server installation. You can edit the value by
> > > specifying
> > > > the required valid path.
> > > >
> > > > Becareful while editing the registry.
> > > > --
> > > > HTH,
> > > > Vyas, MVP (SQL Server)
> > > >
> > > >
> > > > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > > > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > > > Can anyone advice me:
> > > > >
> > > > > I'm backing up my transaction logs every 2 hours using the SQL
> Server
> > > > > database management wizard.
> > > > > I only keep the logs for two days before the wizard automatically
> > > > > deletes the ones which are older than two days.
> > > > >
> > > > > I want to include into the routine a process which will move the
> > > > > transaction logs to a different server, but will also delete the
> logs
> > > > > on the other server if they are older than 2 days. Is this
possible?
> > > > > Or is it possible to set SQL Server to backup the transaction logs
> to
> > > > > another server using the wizard or some other means. I can only
see
> > > > > the local driver on the server, even if I've mapped one.
> > > > >
> > > > > Many thanks in advance
> > > >
> > > >
> > >
> > >
> >
> >
>|||Problem solved. It turns out that MSSQLSERVER was logged on as LocalSystem
(the default when a service is installed). By setting the login to
administrator I was able to successfully do a backup to a remote drive.
Apparently it doesn't matter that the remote drive is shared with full
permissions for Everyone. Or that SQLServerAgent was logged on as
administrator.
Thanks for your help in solving the problem.
William
"Ana Mihalj" <amihalj@.hotmail.com> wrote in message
news:%23COs1X$3DHA.632@.TK2MSFTNGP12.phx.gbl...
> Statements are valid and probably you don't have valid permission on
remote
> machine.
> Does your SQLServerAgent Service have a permission to write on remote
> machine?
>
>
>
> "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> news:OnHvwg53DHA.1264@.TK2MSFTNGP11.phx.gbl...
> > Ok, let's forget EI. I have to use T-SQL for my app anyway. I'm
running
> > this test using the Query Analyzer with the commands shown below. I
> changed
> > all the file names to backup.bak but it still doesn't work for network
> > drives.
> >
> >
> > "ME" <mail@.moon.net> wrote in message
> > news:OXJqQS53DHA.1404@.TK2MSFTNGP11.phx.gbl...
> > > if you use the wizard you don't have the choice. SQL wizard backup db
> and
> > > logs to .BAK and .TRN files
> > >
> > >
> > > "Wm" <wschmidt@.egginc_SpamBlocker_.com> wrote in message
> > > news:OKVnQG53DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > > > I have the same problem.
> > > >
> > > > How does this solution help? Enterprise Manager still refuses to
> accept
> > > the
> > > > UNC address (as shown in BOL "sp_addumpdevice") and although
> > > sp_addumpdevice
> > > > will accept it (it accepts anything), the backup command complains
> that
> > it
> > > > can't open the backup device. Here's the code:
> > > >
> > > > exec sp_addumpdevice 'disk', 'device1', 'C:\Temp\backup.dat' --
local
> > > drive
> > > > exec sp_addumpdevice 'disk', 'device2', 'R:\Temp\backup.dat' --
> remote
> > > > drive
> > > > exec sp_addumpdevice 'disk', 'device3',
> > > \\Acqserver\R\Temp\backup.dat' --
> > > > same drive, UNC notation
> > > >
> > > > backup database PC_DASC_DB to device1 -- this works
> > > >
> > > > backup database PC_DASC_DB to device2 -- this fails
> > > >
> > > > backup database PC_DASC_DB to device3 -- this fails
> > > >
> > > > TIA,
> > > > Wm Schmidt
> > > >
> > > >
> > > > "ME" <mail@.moon.net> wrote in message
> > > > news:%23WzsUn33DHA.3216@.TK2MSFTNGP11.phx.gbl...
> > > > > this is from google.com
> > > > >
> > > > > the default backup folder path is stored in the regisrty at:
> > > > >
> > > >
> > >
> >
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirector
> > > > > y
> > > > > for a default SQL Server installation. You can edit the value by
> > > > specifying
> > > > > the required valid path.
> > > > >
> > > > > Becareful while editing the registry.
> > > > > --
> > > > > HTH,
> > > > > Vyas, MVP (SQL Server)
> > > > >
> > > > >
> > > > > "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> > > > > news:a6d765d6.0401200822.415cb11c@.posting.google.com...
> > > > > > Can anyone advice me:
> > > > > >
> > > > > > I'm backing up my transaction logs every 2 hours using the SQL
> > Server
> > > > > > database management wizard.
> > > > > > I only keep the logs for two days before the wizard
automatically
> > > > > > deletes the ones which are older than two days.
> > > > > >
> > > > > > I want to include into the routine a process which will move the
> > > > > > transaction logs to a different server, but will also delete the
> > logs
> > > > > > on the other server if they are older than 2 days. Is this
> possible?
> > > > > > Or is it possible to set SQL Server to backup the transaction
logs
> > to
> > > > > > another server using the wizard or some other means. I can only
> see
> > > > > > the local driver on the server, even if I've mapped one.
> > > > > >
> > > > > > Many thanks in advance
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>