Backup Strategy for MSSQL

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!We back our stuff up nightly, however you have to decide how much info you are willing to lose should your system crash. If weekly backups have been working and you feel confident in your hardware then stick to that schedule.

HTH,
Aric|||

Quote:

Originally Posted by DbAFtW

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!

Hi there,

Backup / recovery plan should be designed based on the importance of your data. As a consultant i am managing 17 database servers, some servers are configured to run backup routine every hour, whereas, some servers are configured to run backup routine every day. Good luck & Take care.|||Thanks folks for the replies.
Well, the vendors that put the web server and the MSSQL DB in place were using a Simple Recovery Model and I felt the nature of the data dictated transaction log backups. The system accepts college applications and I felt any data loss was something I wanted to avoid due to the importance of that data! The intention is that we can experience minimal loss utilizing the strategy to restore from the weekly full, nightly differential and every half hour transaction log backups.|||</bumpitybump>sql

Backup SQL Server Failures

I get the following errors when I use a maintenance plan to backup all of my databases on one of our servers. Note: When I run them manually they work as long as I put them in a different place on the same drive. The Everone group has full access on the NTFS drive that this is writing to so it can't be a rights issue. Also some of my databases are backed up while others fail. The account that I'm using to back these up is an administrator of the machine, I'm using the same account when I automate them through a maintenance plan that I do for doing them manually. The E:\ drive (where these are trying to be backed up) is Raid 5 with 30 gb free and 30gb used. These backups would only take up about 5gb of space (if they would work).

Please help!!!

BACKUP failed to complete the command BACKUP LOG [WebLogs] TO DISK = N'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731. TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Internal I/O request 0x2E0CB728: Op: Write, pBuffer: 0x17D20000, Size: 983040, Position: 328286720, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x13914200, OffsetHigh: 0x0, m_buf: 0x17D20000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.TR N

BackupMedium::ReportIoError: write failure on backup device 'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.T RN'. Operating system error 2(The system cannot find the file specified.).Try to redefine the Maint.plan and also try using BACKUP LOG statement individually for this database.|||Thanks for your reply.

I've redefined my Maintenance Plan several times, as you could imagine, but to no avail.

Also, I've ran the BACKUP LOG statement on all of my databases in the place of the maintenance plan and received the same error.|||What is the SP[service pack] level on the SQL Server & OS?|||Windows 2000 Server SP3

SQL Server 2000 SP3 (Standard Edition not Enterprise)|||Then how about RECOVERY MODEL on these DBs.|||All of the user databases on this server have the Recovery Model option set to FULL.|||Looks like issues with NTFS permissions, check whether the SQL Services have necessary privileges and also under REGISTRY keys for these accounts.|||Since this is an internal machine and not our Internet SQL Server, the SQL Server Services (Main, Agent, etc.) all run under an account that is an administrator of the machine.

Security rights for both logical drives on the machine have the Everyone group having all rights locally. (The network shares only allow administrators in)|||As you'd mentioned the disk is 60GB, whereas 30Gb is free and another 30 is used.

Could you point out exact size of databases and free space available on the disk. It looks like when the Maint.plan is running typically the error refers to Free space on the server.

For a test try to redesing maint.plan for each database and see the results.|||The drive has 21.0 GB used. It has 38.7 GB free.

There are 16 databases to be backed up. The error happens randomly upon the 3 largest databases.

Email database = 263.88 mb (data and log)
ESP database = 316.25 mb (data and log)
WebLogs database = 5,069.76 gb (data and log)

(note 5 datafiles in the WebLogs and one log file... low amount of transactions and the database and the log is shrunk periodically throughout the day. We backup and truncate the log after a main import process is executed each day.)

I've ran the backup by hand using BACKUP DATABASE and it seldom works on these databases. Sometimes it will actually complete, others it doesn't. Also, the WebLogs database never completes and it always seems to fail at 24 percent (stats = 1 on the backup database command).

The backup log doesn't work at all on these databases whether I run it by hand or through the maintenance plan.|||Sorry for wasting your time.

I finally became fed up and went throught the security check points and actually went to the physical machine and looked in the event viewer of windows.

There I found that one of the Raid disks was going bad. That explains a lot.

Thanks for all of your help.

You had a lot of great ideas. If I had the ability to "dial in" to the machine, I would have spotted the problem much earlier.

I'm betting you agree that a bad disk could cause all of the quirky backup errors?|||Dont' be sorry.... Glad to hear your resolution and efforts, keep it up.
No second thought for these failures, but anytime if you've them again then follow the listings above.

Good luck.

Backup SQL 2005 Plan

Hello everybody,
I'm using SQL 2005 for few weeks, and I'd like to ask you something about
the Backup and Restore Plan. I need to create a complete DR solution for my
client.
So, what I did was: (using Management Studio-Maintenance Plan)
- All the user database are on Full Recovery Mode.
- Run a Full User Database backup every day at 1:00am and save it on day's
folder, it means the backup for Monday will go to Monday's backup folder and
so on.
- At 6:00am (normally the time they start to work), I start to do a Log
Backup every 15 minutes until 11:00pm aprox.
My question is:
- Every time that I ran the log backup, I'm truncating the LOG file? or I
have to run other task to truncate the log?
- Using that Plan, I'll be able to recover the data for the last 15 minutes
in case the database crashed?
- How often I have to shrink the database file and logs?
The Primary data file is : 570Gb
The Secondary data file is : 130Gb
The Log file is : 850Gb
I'm not worry about the space because the server and the backup unit have a
lot of space.
Any ideas or opinions'
Thank you!
JoseHola Jose,
Some comments here:
> The Log file is : 850Gb
Is this a typo? This is too big for a transaction log file. You should
manually shrink it, but probably only once. Do not schedule a job to shrink
it periodically.
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
After the transaction log is shrunk manually doing a backup every 15 minutes
should keep it in a acceptable size.
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
Yes, if you still have access to the backup folder. Are these folders on the
same computer? Also, if you are lucky and have access to the tail of the log
you can recover your database to the point of failure, losing no data at all.
> - How often I have to shrink the database file and logs?
Do not shrink any file on a job or maintenance plan. Do it only manually
when you really need it.
Hope this helps,
Ben Nevarez
"Jose" wrote:
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Jose
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
You can run BACKUP LOG with or without WITH INIT option (for more details
please see BOL)
If you choose using WITH INIT option that means SQL Server creates one file
per media set and you need to give the file separate name
Like log_20080101_17:00.log
log_20080101_17:30.log
On the other hand if you use WITH NOINIT you can create one file and SQL
Server adds one file to the media set and you will have to refer thopse
file when you restore database
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1,
norecovery --full database
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
You will be able to restore even at point of time
> - How often I have to shrink the database file and logs?
Do not shrink them at all
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:A3D3CD10-2D80-422A-AD68-09F9E510CDCB@.microsoft.com...
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for
> my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder
> and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have
> a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Hi Ben and Uri.
Thanks for your comments!.
And I made a mistake, is not "GB" is "MB"..sorry.
I've asked if every time that I run the backup for the LOG files I'm
truncating the log, because my client is using NAVISION from Microsoft, and
the company who did the installation, gave me some instructions for the
backup, and to be honest I dont know too much about SQL commands, that is way
I did using the Management Studio. And they told me to create :
- 1 full backup every day
- 1 log backup every 15 minutes
- at the end of the day truncate the log to keep the log small...
But if backing up the log and I'm truncating it at the same time, I'll need
to create other job to truncate the log at the end of the day? I guess not.
Right now, when I run the log backup, SQL creates a directory for each
database and it creates every log file for each database every 15 minutes and
the size of the log is 500Kb.
All the files backup are on the server and I have a task who move files and
logs to other (external) unit backup, so, at least I have 2 places to find if
something happens to the datasbase and 1 place if something happens to the
server and to be more protected, those database are been replicated online to
1 external server outside of the company.
When I will really need shrink a database and/or Log File?
Thank you so much for all your comments!
Have a nice day!
Jose
"Ben Nevarez" wrote:
> Hola Jose,
> Some comments here:
> > The Log file is : 850Gb
> Is this a typo? This is too big for a transaction log file. You should
> manually shrink it, but probably only once. Do not schedule a job to shrink
> it periodically.
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> After the transaction log is shrunk manually doing a backup every 15 minutes
> should keep it in a acceptable size.
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> Yes, if you still have access to the backup folder. Are these folders on the
> same computer? Also, if you are lucky and have access to the tail of the log
> you can recover your database to the point of failure, losing no data at all.
> > - How often I have to shrink the database file and logs?
> Do not shrink any file on a job or maintenance plan. Do it only manually
> when you really need it.
> Hope this helps,
> Ben Nevarez
>
>
> "Jose" wrote:
> > Hello everybody,
> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> > the Backup and Restore Plan. I need to create a complete DR solution for my
> > client.
> > So, what I did was: (using Management Studio-Maintenance Plan)
> > - All the user database are on Full Recovery Mode.
> > - Run a Full User Database backup every day at 1:00am and save it on day's
> > folder, it means the backup for Monday will go to Monday's backup folder and
> > so on.
> > - At 6:00am (normally the time they start to work), I start to do a Log
> > Backup every 15 minutes until 11:00pm aprox.
> >
> > My question is:
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> > - How often I have to shrink the database file and logs?
> >
> > The Primary data file is : 570Gb
> > The Secondary data file is : 130Gb
> > The Log file is : 850Gb
> >
> > I'm not worry about the space because the server and the backup unit have a
> > lot of space.
> >
> > Any ideas or opinions'
> > Thank you!
> >
> > Jose|||> When I will really need shrink a database and/or Log File?
If, and only if, it becomes exceptionally large. Larger that it would have to be for your normal
operation. And only if you actually gain something by doing the shrink (i.e., you really need the
disk space). See below:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:7D430938-4FC7-4D15-A4EF-6ED05EF6E386@.microsoft.com...
> Hi Ben and Uri.
> Thanks for your comments!.
> And I made a mistake, is not "GB" is "MB"..sorry.
> I've asked if every time that I run the backup for the LOG files I'm
> truncating the log, because my client is using NAVISION from Microsoft, and
> the company who did the installation, gave me some instructions for the
> backup, and to be honest I dont know too much about SQL commands, that is way
> I did using the Management Studio. And they told me to create :
> - 1 full backup every day
> - 1 log backup every 15 minutes
> - at the end of the day truncate the log to keep the log small...
> But if backing up the log and I'm truncating it at the same time, I'll need
> to create other job to truncate the log at the end of the day? I guess not.
> Right now, when I run the log backup, SQL creates a directory for each
> database and it creates every log file for each database every 15 minutes and
> the size of the log is 500Kb.
> All the files backup are on the server and I have a task who move files and
> logs to other (external) unit backup, so, at least I have 2 places to find if
> something happens to the datasbase and 1 place if something happens to the
> server and to be more protected, those database are been replicated online to
> 1 external server outside of the company.
> When I will really need shrink a database and/or Log File?
> Thank you so much for all your comments!
> Have a nice day!
> Jose
> "Ben Nevarez" wrote:
>> Hola Jose,
>> Some comments here:
>> > The Log file is : 850Gb
>> Is this a typo? This is too big for a transaction log file. You should
>> manually shrink it, but probably only once. Do not schedule a job to shrink
>> it periodically.
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> After the transaction log is shrunk manually doing a backup every 15 minutes
>> should keep it in a acceptable size.
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> Yes, if you still have access to the backup folder. Are these folders on the
>> same computer? Also, if you are lucky and have access to the tail of the log
>> you can recover your database to the point of failure, losing no data at all.
>> > - How often I have to shrink the database file and logs?
>> Do not shrink any file on a job or maintenance plan. Do it only manually
>> when you really need it.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Jose" wrote:
>> > Hello everybody,
>> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
>> > the Backup and Restore Plan. I need to create a complete DR solution for my
>> > client.
>> > So, what I did was: (using Management Studio-Maintenance Plan)
>> > - All the user database are on Full Recovery Mode.
>> > - Run a Full User Database backup every day at 1:00am and save it on day's
>> > folder, it means the backup for Monday will go to Monday's backup folder and
>> > so on.
>> > - At 6:00am (normally the time they start to work), I start to do a Log
>> > Backup every 15 minutes until 11:00pm aprox.
>> >
>> > My question is:
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> > - How often I have to shrink the database file and logs?
>> >
>> > The Primary data file is : 570Gb
>> > The Secondary data file is : 130Gb
>> > The Log file is : 850Gb
>> >
>> > I'm not worry about the space because the server and the backup unit have a
>> > lot of space.
>> >
>> > Any ideas or opinions'
>> > Thank you!
>> >
>> > Jose

Backup sizes?

I have a maintenance plan set up so that we do a full backup every Sunday
night. We then do a differential backup every night at 11:00. Additionally we
do transaction log backups every hour from 5:00 in the morning until 9:00 at
night. I recently noticed that my differential backup was about 7 times the
size of my full backup. Does this make sense to anyone else? What could be
causing this? Thanks. MattHi
Are you appending the backup into a single file?
It is best to give every backup a new file, every time it is run.
Regards
Mike
"Matt" wrote:
> I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally we
> do transaction log backups every hour from 5:00 in the morning until 9:00 at
> night. I recently noticed that my differential backup was about 7 times the
> size of my full backup. Does this make sense to anyone else? What could be
> causing this? Thanks. Matt|||You need to specify the WITH INIT option otherwise it appends each backup to
the prior one. Why not simply do a FULL backup once a night?
--
Andrew J. Kelly SQL MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally
> we
> do transaction log backups every hour from 5:00 in the morning until 9:00
> at
> night. I recently noticed that my differential backup was about 7 times
> the
> size of my full backup. Does this make sense to anyone else? What could
> be
> causing this? Thanks. Matt|||Would it not be faster to restore with my current setup? If not, then I
probably will just go to a Full backup every night. Thanks.
"Andrew J. Kelly" wrote:
> You need to specify the WITH INIT option otherwise it appends each backup to
> the prior one. Why not simply do a FULL backup once a night?
> --
> Andrew J. Kelly SQL MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
> >I have a maintenance plan set up so that we do a full backup every Sunday
> > night. We then do a differential backup every night at 11:00. Additionally
> > we
> > do transaction log backups every hour from 5:00 in the morning until 9:00
> > at
> > night. I recently noticed that my differential backup was about 7 times
> > the
> > size of my full backup. Does this make sense to anyone else? What could
> > be
> > causing this? Thanks. Matt
>
>|||> Would it not be faster to restore with my current setup?
Au contraire. With your current setup, you have to first restore the latest database backup, then
the latest diff backup, then all subsequent log backups.
With Andrew's suggestion you just restore the latest database backup and then all subsequent log
backup (same number of log backups as above scenario).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
> Would it not be faster to restore with my current setup? If not, then I
> probably will just go to a Full backup every night. Thanks.
> "Andrew J. Kelly" wrote:
>> You need to specify the WITH INIT option otherwise it appends each backup to
>> the prior one. Why not simply do a FULL backup once a night?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>> >I have a maintenance plan set up so that we do a full backup every Sunday
>> > night. We then do a differential backup every night at 11:00. Additionally
>> > we
>> > do transaction log backups every hour from 5:00 in the morning until 9:00
>> > at
>> > night. I recently noticed that my differential backup was about 7 times
>> > the
>> > size of my full backup. Does this make sense to anyone else? What could
>> > be
>> > causing this? Thanks. Matt
>>|||Thanks to everyone for your help. I assume the only reason to use
differential backups is if you don't have a very big window to do a full
backup? Again, thanks. Matt
"Tibor Karaszi" wrote:
> > Would it not be faster to restore with my current setup?
> Au contraire. With your current setup, you have to first restore the latest database backup, then
> the latest diff backup, then all subsequent log backups.
> With Andrew's suggestion you just restore the latest database backup and then all subsequent log
> backup (same number of log backups as above scenario).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
> > Would it not be faster to restore with my current setup? If not, then I
> > probably will just go to a Full backup every night. Thanks.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> You need to specify the WITH INIT option otherwise it appends each backup to
> >> the prior one. Why not simply do a FULL backup once a night?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
> >> >I have a maintenance plan set up so that we do a full backup every Sunday
> >> > night. We then do a differential backup every night at 11:00. Additionally
> >> > we
> >> > do transaction log backups every hour from 5:00 in the morning until 9:00
> >> > at
> >> > night. I recently noticed that my differential backup was about 7 times
> >> > the
> >> > size of my full backup. Does this make sense to anyone else? What could
> >> > be
> >> > causing this? Thanks. Matt
> >>
> >>
> >>
>
>|||> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup?
Yes, something like that. Or disk space prohibits you from doing db backups with that frequency...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:B64881EC-6D15-4AFE-ABBF-47BFF7CBCA79@.microsoft.com...
> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup? Again, thanks. Matt
> "Tibor Karaszi" wrote:
>> > Would it not be faster to restore with my current setup?
>> Au contraire. With your current setup, you have to first restore the latest database backup, then
>> the latest diff backup, then all subsequent log backups.
>> With Andrew's suggestion you just restore the latest database backup and then all subsequent log
>> backup (same number of log backups as above scenario).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
>> > Would it not be faster to restore with my current setup? If not, then I
>> > probably will just go to a Full backup every night. Thanks.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> You need to specify the WITH INIT option otherwise it appends each backup to
>> >> the prior one. Why not simply do a FULL backup once a night?
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> >> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>> >> >I have a maintenance plan set up so that we do a full backup every Sunday
>> >> > night. We then do a differential backup every night at 11:00. Additionally
>> >> > we
>> >> > do transaction log backups every hour from 5:00 in the morning until 9:00
>> >> > at
>> >> > night. I recently noticed that my differential backup was about 7 times
>> >> > the
>> >> > size of my full backup. Does this make sense to anyone else? What could
>> >> > be
>> >> > causing this? Thanks. Matt
>> >>
>> >>
>> >>
>>

Backup sizes?

I have a maintenance plan set up so that we do a full backup every Sunday
night. We then do a differential backup every night at 11:00. Additionally w
e
do transaction log backups every hour from 5:00 in the morning until 9:00 at
night. I recently noticed that my differential backup was about 7 times the
size of my full backup. Does this make sense to anyone else? What could be
causing this? Thanks. MattHi
Are you appending the backup into a single file?
It is best to give every backup a new file, every time it is run.
Regards
Mike
"Matt" wrote:

> I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally
we
> do transaction log backups every hour from 5:00 in the morning until 9:00
at
> night. I recently noticed that my differential backup was about 7 times th
e
> size of my full backup. Does this make sense to anyone else? What could b
e
> causing this? Thanks. Matt|||You need to specify the WITH INIT option otherwise it appends each backup to
the prior one. Why not simply do a FULL backup once a night?
Andrew J. Kelly SQL MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally
> we
> do transaction log backups every hour from 5:00 in the morning until 9:00
> at
> night. I recently noticed that my differential backup was about 7 times
> the
> size of my full backup. Does this make sense to anyone else? What could
> be
> causing this? Thanks. Matt|||Would it not be faster to restore with my current setup? If not, then I
probably will just go to a Full backup every night. Thanks.
"Andrew J. Kelly" wrote:

> You need to specify the WITH INIT option otherwise it appends each backup
to
> the prior one. Why not simply do a FULL backup once a night?
> --
> Andrew J. Kelly SQL MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>
>|||> Would it not be faster to restore with my current setup?
Au contraire. With your current setup, you have to first restore the latest
database backup, then
the latest diff backup, then all subsequent log backups.
With Andrew's suggestion you just restore the latest database backup and the
n all subsequent log
backup (same number of log backups as above scenario).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...[vbcol=seagreen]
> Would it not be faster to restore with my current setup? If not, then I
> probably will just go to a Full backup every night. Thanks.
> "Andrew J. Kelly" wrote:
>|||Thanks to everyone for your help. I assume the only reason to use
differential backups is if you don't have a very big window to do a full
backup? Again, thanks. Matt
"Tibor Karaszi" wrote:

> Au contraire. With your current setup, you have to first restore the lates
t database backup, then
> the latest diff backup, then all subsequent log backups.
> With Andrew's suggestion you just restore the latest database backup and t
hen all subsequent log
> backup (same number of log backups as above scenario).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
>
>|||> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup?
Yes, something like that. Or disk space prohibits you from doing db backups
with that frequency...
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:B64881EC-6D15-4AFE-ABBF-47BFF7CBCA79@.microsoft.com...[vbcol=seagreen]
> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup? Again, thanks. Matt
> "Tibor Karaszi" wrote:
>sql

Backup sizes?

I have a maintenance plan set up so that we do a full backup every Sunday
night. We then do a differential backup every night at 11:00. Additionally we
do transaction log backups every hour from 5:00 in the morning until 9:00 at
night. I recently noticed that my differential backup was about 7 times the
size of my full backup. Does this make sense to anyone else? What could be
causing this? Thanks. Matt
Hi
Are you appending the backup into a single file?
It is best to give every backup a new file, every time it is run.
Regards
Mike
"Matt" wrote:

> I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally we
> do transaction log backups every hour from 5:00 in the morning until 9:00 at
> night. I recently noticed that my differential backup was about 7 times the
> size of my full backup. Does this make sense to anyone else? What could be
> causing this? Thanks. Matt
|||You need to specify the WITH INIT option otherwise it appends each backup to
the prior one. Why not simply do a FULL backup once a night?
Andrew J. Kelly SQL MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>I have a maintenance plan set up so that we do a full backup every Sunday
> night. We then do a differential backup every night at 11:00. Additionally
> we
> do transaction log backups every hour from 5:00 in the morning until 9:00
> at
> night. I recently noticed that my differential backup was about 7 times
> the
> size of my full backup. Does this make sense to anyone else? What could
> be
> causing this? Thanks. Matt
|||Would it not be faster to restore with my current setup? If not, then I
probably will just go to a Full backup every night. Thanks.
"Andrew J. Kelly" wrote:

> You need to specify the WITH INIT option otherwise it appends each backup to
> the prior one. Why not simply do a FULL backup once a night?
> --
> Andrew J. Kelly SQL MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:443B1E93-6FFE-4A06-8D32-4DF540B03125@.microsoft.com...
>
>
|||> Would it not be faster to restore with my current setup?
Au contraire. With your current setup, you have to first restore the latest database backup, then
the latest diff backup, then all subsequent log backups.
With Andrew's suggestion you just restore the latest database backup and then all subsequent log
backup (same number of log backups as above scenario).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...[vbcol=seagreen]
> Would it not be faster to restore with my current setup? If not, then I
> probably will just go to a Full backup every night. Thanks.
> "Andrew J. Kelly" wrote:
|||Thanks to everyone for your help. I assume the only reason to use
differential backups is if you don't have a very big window to do a full
backup? Again, thanks. Matt
"Tibor Karaszi" wrote:

> Au contraire. With your current setup, you have to first restore the latest database backup, then
> the latest diff backup, then all subsequent log backups.
> With Andrew's suggestion you just restore the latest database backup and then all subsequent log
> backup (same number of log backups as above scenario).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:ABCDCE9D-67A5-4D70-AFCA-20B51E3ABF79@.microsoft.com...
>
>
|||> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup?
Yes, something like that. Or disk space prohibits you from doing db backups with that frequency...
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:B64881EC-6D15-4AFE-ABBF-47BFF7CBCA79@.microsoft.com...[vbcol=seagreen]
> Thanks to everyone for your help. I assume the only reason to use
> differential backups is if you don't have a very big window to do a full
> backup? Again, thanks. Matt
> "Tibor Karaszi" wrote:

Backup Setup

I am trying to setup the following and was hoping if someone could tell me a more efficient way to set this up. I am not using the maintenance plan or wizard cause Ive had nothing but issues with them:

1. Create a backup device for each day of the week (Sunday - Saturday)

2. Create a job using T-SQL to run maintenance (Rebuild Indexes) and then at the conclusion of the maintenance run a full backup every night at midnight. I would need once job for each day of the week and store them on the proper backup device.

BACKUP DATABASE [DBS1] TO [Sunday Backup] WITH NOFORMAT, NOINIT, NAME = N'DBS1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

GO

declare @.backupSetId as int

select @.backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )

if @.backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND

GO

3. Create a job using T-SQL for transaction log backups to run every hour and store them in the proper backup device. I would need one job for each day of the week.

BACKUP LOG [DBS1 [Sunday Backup] WITH NOFORMAT, NOINIT,

NAME = N'DBS1-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

GO

declare @.backupSetId as int

select @.backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )

if @.backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1"' not found.', 16, 1) end

RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND

GO

This method would give me 7 backups devices and 14 jobs but at least things would be organized nice and neat. Im sure there is a better way to do this so I am looking for some help. Any help would be much appreciated.

Thanks

One simplification would be to have the script build the name of the backup device based on the current day of the week. This way you have one backup job and one log backup job that each run every day.

The following would be an example of building the backup device name:

DECLARE @.BackupDev AS NVARCHAR(30)

select @.BackupDev = DATENAME(dw,GETDATE()) + N'_BACKUP_DEV'

backup schedule

SQL server 2005 is installed but can not schedule a backup job.
In the maintenance plan when selecting a new maintenance plan or maintenance plan wizard an error is produced which is:
'Agent XPs' component is turned off as part of the security configuration for this server.
I can see that the SQL Server Agent (Agent XPs is disabled). When trying to start this by right clicking on it in object explorer, the error message is 'unable to start service SQLAgent$SQL2005 on server serverName'. Service Logon failure

How can I scedule a back up job on this server?

Any thoughts please?

"Service Logon Failure" message means that user name and pwd provided for the service are incorrect. You can fix them in services snap-in (type services.msc in commnd line or try My Computer -> Manage -> Services And Applications -> Services -> double click on SQLAgent$SQL2005 service -> Log On tab )|||

Hi,

Went to the Logon Tab.

There is already a log on setup as admin

What next?

Thanks

|||

There is already a log on setup as admin

Then provide a correct password for him.

Backup retention problem

Hi,
In my full backup maintenance Plan, I configured 1 day in a box 'Remove
files older than : "
The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
older than 1 day.
Why the maintenance Plan does'nt remove those files ?
What can cause this ?
How can I be alert by SQL if the maintenance plan is not able to delete the
..BAK files older than 1 day ?
Thanks
http://support.microsoft.com/default...b;en-us;303292
-- Log files don't delete --
http://support.microsoft.com/default...;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Psa555" <Psa555@.discussions.microsoft.com> wrote in message
news:34A86E28-127A-4A50-B857-EF729A401790@.microsoft.com...
> Hi,
> In my full backup maintenance Plan, I configured 1 day in a box 'Remove
> files older than : "
> The problem is ... sometimes I have 3 or 4 files .BAK in my backup's
> folder
> older than 1 day.
> Why the maintenance Plan does'nt remove those files ?
> What can cause this ?
> How can I be alert by SQL if the maintenance plan is not able to delete
> the
> .BAK files older than 1 day ?
> Thanks
|||Hi,
If you have a mapi e-mail you can have a notification when the job fails.
Probably your status is that the job failed. You can install outlook 2000 or
outlook 2003 and create a new account for sending mails as f.e Database Admin.
Additionally, you can go to the maintenance plan properties and in the
Reporting tab you can tick the check box Write report a text file in
directory or any other option.
Hope that helped
Andreas
"Psa555" wrote:

> Hi,
> In my full backup maintenance Plan, I configured 1 day in a box 'Remove
> files older than : "
> The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
> older than 1 day.
> Why the maintenance Plan does'nt remove those files ?
> What can cause this ?
> How can I be alert by SQL if the maintenance plan is not able to delete the
> .BAK files older than 1 day ?
> Thanks
|||Because 1 day = 24 hours, exactly. If you job is off schedule or takes a
little longer to run than the prior day, even if by 1 minute, the time
stamps are only 23 hours, 59 minutes, etc., which is NOT 1 day.
I usually code mine to delete anything older than 3 hours. When it runs, it
will run the backup or reorg operation first, then it will check for files
to delete.
Sincerely,
Anthony Thomas

"Psa555" <Psa555@.discussions.microsoft.com> wrote in message
news:34A86E28-127A-4A50-B857-EF729A401790@.microsoft.com...
Hi,
In my full backup maintenance Plan, I configured 1 day in a box 'Remove
files older than : "
The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
older than 1 day.
Why the maintenance Plan does'nt remove those files ?
What can cause this ?
How can I be alert by SQL if the maintenance plan is not able to delete the
..BAK files older than 1 day ?
Thanks
sql

Backup retention problem

Hi,
In my full backup maintenance Plan, I configured 1 day in a box 'Remove
files older than : "
The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
older than 1 day.
Why the maintenance Plan does'nt remove those files ?
What can cause this ?
How can I be alert by SQL if the maintenance plan is not able to delete the
.BAK files older than 1 day ?
Thankshttp://support.microsoft.com/defaul...kb;en-us;303292
-- Log files don't delete --
http://support.microsoft.com/defaul...b;en-us;Q303292
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
Andrew J. Kelly SQL MVP
"Psa555" <Psa555@.discussions.microsoft.com> wrote in message
news:34A86E28-127A-4A50-B857-EF729A401790@.microsoft.com...
> Hi,
> In my full backup maintenance Plan, I configured 1 day in a box 'Remove
> files older than : "
> The problem is ... sometimes I have 3 or 4 files .BAK in my backup's
> folder
> older than 1 day.
> Why the maintenance Plan does'nt remove those files ?
> What can cause this ?
> How can I be alert by SQL if the maintenance plan is not able to delete
> the
> .BAK files older than 1 day ?
> Thanks|||Hi,
If you have a mapi e-mail you can have a notification when the job fails.
Probably your status is that the job failed. You can install outlook 2000 or
outlook 2003 and create a new account for sending mails as f.e Database Admi
n.
Additionally, you can go to the maintenance plan properties and in the
Reporting tab you can tick the check box Write report a text file in
directory or any other option.
Hope that helped
Andreas
"Psa555" wrote:

> Hi,
> In my full backup maintenance Plan, I configured 1 day in a box 'Remove
> files older than : "
> The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folde
r
> older than 1 day.
> Why the maintenance Plan does'nt remove those files ?
> What can cause this ?
> How can I be alert by SQL if the maintenance plan is not able to delete th
e
> .BAK files older than 1 day ?
> Thanks|||Because 1 day = 24 hours, exactly. If you job is off schedule or takes a
little longer to run than the prior day, even if by 1 minute, the time
stamps are only 23 hours, 59 minutes, etc., which is NOT 1 day.
I usually code mine to delete anything older than 3 hours. When it runs, it
will run the backup or reorg operation first, then it will check for files
to delete.
Sincerely,
Anthony Thomas
"Psa555" <Psa555@.discussions.microsoft.com> wrote in message
news:34A86E28-127A-4A50-B857-EF729A401790@.microsoft.com...
Hi,
In my full backup maintenance Plan, I configured 1 day in a box 'Remove
files older than : "
The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
older than 1 day.
Why the maintenance Plan does'nt remove those files ?
What can cause this ?
How can I be alert by SQL if the maintenance plan is not able to delete the
.BAK files older than 1 day ?
Thanks

Backup retention problem

Hi,
In my full backup maintenance Plan, I configured 1 day in a box 'Remove
files older than : "
The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
older than 1 day.
Why the maintenance Plan does'nt remove those files ?
What can cause this ?
How can I be alert by SQL if the maintenance plan is not able to delete the
.BAK files older than 1 day ?
Thankshttp://support.microsoft.com/default.aspx?scid=kb;en-us;303292
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
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
--
Andrew J. Kelly SQL MVP
"Psa555" <Psa555@.discussions.microsoft.com> wrote in message
news:34A86E28-127A-4A50-B857-EF729A401790@.microsoft.com...
> Hi,
> In my full backup maintenance Plan, I configured 1 day in a box 'Remove
> files older than : "
> The problem is ... sometimes I have 3 or 4 files .BAK in my backup's
> folder
> older than 1 day.
> Why the maintenance Plan does'nt remove those files ?
> What can cause this ?
> How can I be alert by SQL if the maintenance plan is not able to delete
> the
> .BAK files older than 1 day ?
> Thanks|||Hi,
If you have a mapi e-mail you can have a notification when the job fails.
Probably your status is that the job failed. You can install outlook 2000 or
outlook 2003 and create a new account for sending mails as f.e Database Admin.
Additionally, you can go to the maintenance plan properties and in the
Reporting tab you can tick the check box Write report a text file in
directory or any other option.
Hope that helped
Andreas
"Psa555" wrote:
> Hi,
> In my full backup maintenance Plan, I configured 1 day in a box 'Remove
> files older than : "
> The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
> older than 1 day.
> Why the maintenance Plan does'nt remove those files ?
> What can cause this ?
> How can I be alert by SQL if the maintenance plan is not able to delete the
> .BAK files older than 1 day ?
> Thanks|||Because 1 day = 24 hours, exactly. If you job is off schedule or takes a
little longer to run than the prior day, even if by 1 minute, the time
stamps are only 23 hours, 59 minutes, etc., which is NOT 1 day.
I usually code mine to delete anything older than 3 hours. When it runs, it
will run the backup or reorg operation first, then it will check for files
to delete.
Sincerely,
Anthony Thomas
"Psa555" <Psa555@.discussions.microsoft.com> wrote in message
news:34A86E28-127A-4A50-B857-EF729A401790@.microsoft.com...
Hi,
In my full backup maintenance Plan, I configured 1 day in a box 'Remove
files older than : "
The problem is ... sometimes I have 3 or 4 files .BAK in my backup's folder
older than 1 day.
Why the maintenance Plan does'nt remove those files ?
What can cause this ?
How can I be alert by SQL if the maintenance plan is not able to delete the
.BAK files older than 1 day ?
Thanks

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...
>|||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...
>|||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...
>|||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...
>|||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...
>

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...
>
|||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...
>
|||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...
>
|||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...
>
|||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...
>
sql

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.
>>
>>
>