Backups of Logs - Growing way, way out of control & Need Assistanc

Hello All
I should start by saying, I am not a SQL guru at all!
We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
NetBackup 5.1 MP5 using an online SQL Agent.
I will start with the good news, in that all FULL Backups work fine! I have
discovered that the Transaction logs are NOT included in this FULL Backup.
In NetBackup there is an option to "Backup and Truncate the logs" - so I
attempted to do this, and it claims it worked fine (the Application log also
shows event id 18265 and the description gives an indication "log was backed
up Database ect and ending in "No user action is required".
Great! but here is the problem. The .ldf files are growing WAY out of
control. For example, they can grow to over 40GB in a week !
I know there is a query or a SHRINK command that can be used to help, but I
am being told that NetBackup should be "truncating" the log down. I guess
this means shrinking.
Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
2005 Administrator has to manually shrink the logs or run a query to do this?
Or could something be setup wrong in SQL2005.
Any help is warmly appreciated.
Thank you
SimonConsider a log file a bucket. As modifications are performed, the bucket is filled. It is only
emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
by SQL Server if it becomes full and modifications are performed (autogrow).
So, one reason for large log files is that you never did backup log. You now did it, and that log
backup was probably pretty big and you should now have a lot of empty space in the log file. This
can be a valid situation for shrinking the log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> Hello All
> I should start by saying, I am not a SQL guru at all!
> We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> NetBackup 5.1 MP5 using an online SQL Agent.
> I will start with the good news, in that all FULL Backups work fine! I have
> discovered that the Transaction logs are NOT included in this FULL Backup.
> In NetBackup there is an option to "Backup and Truncate the logs" - so I
> attempted to do this, and it claims it worked fine (the Application log also
> shows event id 18265 and the description gives an indication "log was backed
> up Database ect and ending in "No user action is required".
> Great! but here is the problem. The .ldf files are growing WAY out of
> control. For example, they can grow to over 40GB in a week !
> I know there is a query or a SHRINK command that can be used to help, but I
> am being told that NetBackup should be "truncating" the log down. I guess
> this means shrinking.
> Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> 2005 Administrator has to manually shrink the logs or run a query to do this?
> Or could something be setup wrong in SQL2005.
> Any help is warmly appreciated.
> Thank you
> Simon|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||> Is there a way of telling what free space is in the log file.
Sure.:
DBCC SQLPERF(LOGSPACE)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
Correct thinking. Use above command.
So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
shrinking the file size. Here's an elaboration on the "bucket" analogy:
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
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> Tibor thanks
> Is there a way of telling what free space is in the log file. I sort of
> understand the "bucket" route now :-)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
> Will check the link out as well. I apprecaite that a 3rd party tool is doing
> the backup, but it sounds like I may be on the right track - the only concern
> I had is the log file does not shrink. but if the query I showed below is
> run, then the file is reduced down in size.
> Simon
> "Tibor Karaszi" wrote:
>> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
>> size
>> by SQL Server if it becomes full and modifications are performed (autogrow).
>> So, one reason for large log files is that you never did backup log. You now did it, and that log
>> backup was probably pretty big and you should now have a lot of empty space in the log file. This
>> can be a valid situation for shrinking the log file. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
>> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> > Hello All
>> > I should start by saying, I am not a SQL guru at all!
>> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >
>> > I will start with the good news, in that all FULL Backups work fine! I have
>> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >
>> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> > attempted to do this, and it claims it worked fine (the Application log also
>> > shows event id 18265 and the description gives an indication "log was backed
>> > up Database ect and ending in "No user action is required".
>> >
>> > Great! but here is the problem. The .ldf files are growing WAY out of
>> > control. For example, they can grow to over 40GB in a week !
>> >
>> > I know there is a query or a SHRINK command that can be used to help, but I
>> > am being told that NetBackup should be "truncating" the log down. I guess
>> > this means shrinking.
>> >
>> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >
>> > Or could something be setup wrong in SQL2005.
>> >
>> > Any help is warmly appreciated.
>> > Thank you
>> > Simon
>>|||Thank you! I think I understand a bit better now!
Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
another recommendation?
Thanks
"Tibor Karaszi" wrote:
> > Is there a way of telling what free space is in the log file.
> Sure.:
> DBCC SQLPERF(LOGSPACE)
>
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> Correct thinking. Use above command.
> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
> shrinking the file size. Here's an elaboration on the "bucket" analogy:
> 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
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> > Tibor thanks
> > Is there a way of telling what free space is in the log file. I sort of
> > understand the "bucket" route now :-)
> >
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> >
> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
> > the backup, but it sounds like I may be on the right track - the only concern
> > I had is the log file does not shrink. but if the query I showed below is
> > run, then the file is reduced down in size.
> >
> > Simon
> >
> > "Tibor Karaszi" wrote:
> >
> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
> >> size
> >> by SQL Server if it becomes full and modifications are performed (autogrow).
> >>
> >> So, one reason for large log files is that you never did backup log. You now did it, and that log
> >> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> >> can be a valid situation for shrinking the log file. See
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> >>
> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> >> > Hello All
> >> > I should start by saying, I am not a SQL guru at all!
> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> >> > NetBackup 5.1 MP5 using an online SQL Agent.
> >> >
> >> > I will start with the good news, in that all FULL Backups work fine! I have
> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >> >
> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> >> > attempted to do this, and it claims it worked fine (the Application log also
> >> > shows event id 18265 and the description gives an indication "log was backed
> >> > up Database ect and ending in "No user action is required".
> >> >
> >> > Great! but here is the problem. The .ldf files are growing WAY out of
> >> > control. For example, they can grow to over 40GB in a week !
> >> >
> >> > I know there is a query or a SHRINK command that can be used to help, but I
> >> > am being told that NetBackup should be "truncating" the log down. I guess
> >> > this means shrinking.
> >> >
> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >> >
> >> > Or could something be setup wrong in SQL2005.
> >> >
> >> > Any help is warmly appreciated.
> >> > Thank you
> >> > Simon
> >>
> >>
>|||There are so many books out there. First think about what area you want (admin, architecture, some
specific component, programming etc), then browse at various book sites. I've always liked the
"Inside SQL Server" series from MS Press.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26E40685-B062-4B78-BAB5-FCCCCCB5D5E8@.microsoft.com...
> Thank you! I think I understand a bit better now!
> Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
> another recommendation?
> Thanks
> "Tibor Karaszi" wrote:
>> > Is there a way of telling what free space is in the log file.
>> Sure.:
>> DBCC SQLPERF(LOGSPACE)
>>
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> Correct thinking. Use above command.
>> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
>> shrinking the file size. Here's an elaboration on the "bucket" analogy:
>> 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
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
>> > Tibor thanks
>> > Is there a way of telling what free space is in the log file. I sort of
>> > understand the "bucket" route now :-)
>> >
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> >
>> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
>> > the backup, but it sounds like I may be on the right track - the only concern
>> > I had is the log file does not shrink. but if the query I showed below is
>> > run, then the file is reduced down in size.
>> >
>> > Simon
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow
>> >> in
>> >> size
>> >> by SQL Server if it becomes full and modifications are performed (autogrow).
>> >>
>> >> So, one reason for large log files is that you never did backup log. You now did it, and that
>> >> log
>> >> backup was probably pretty big and you should now have a lot of empty space in the log file.
>> >> This
>> >> can be a valid situation for shrinking the log file. See
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> >>
>> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like
>> >> BACKUP
>> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that
>> >> app.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> >> > Hello All
>> >> > I should start by saying, I am not a SQL guru at all!
>> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> >> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >> >
>> >> > I will start with the good news, in that all FULL Backups work fine! I have
>> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >> >
>> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> >> > attempted to do this, and it claims it worked fine (the Application log also
>> >> > shows event id 18265 and the description gives an indication "log was backed
>> >> > up Database ect and ending in "No user action is required".
>> >> >
>> >> > Great! but here is the problem. The .ldf files are growing WAY out of
>> >> > control. For example, they can grow to over 40GB in a week !
>> >> >
>> >> > I know there is a query or a SHRINK command that can be used to help, but I
>> >> > am being told that NetBackup should be "truncating" the log down. I guess
>> >> > this means shrinking.
>> >> >
>> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >> >
>> >> > Or could something be setup wrong in SQL2005.
>> >> >
>> >> > Any help is warmly appreciated.
>> >> > Thank you
>> >> > Simon
>> >>
>> >>

backups explained

Beginner's query.
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?

For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?

Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file

bak is the database backup
trn is the transaction log file backup

It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.

The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers

backups

Dear all,
I would like to know in what table the backups are stored.
I mean, I see in the SQL Server log registry these lines:
Database backed up: Database: DATA1, creation date(time):
2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN:
2724:197:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
..
..
That's fine but from what table is retrieving SQL that information?
Best regards,Enric,
The info is scatted across a few tables in the msdb database.
Mainly backupset, but have a look at the others whose name starts with backu
p :)
Regards
AJ
"Enric" <Enric@.discussions.microsoft.com> wrote in message news:8AA0C8CB-9368-4C70-8883-444
2B4CAA10E@.microsoft.com...
> Dear all,
> I would like to know in what table the backups are stored.
> I mean, I see in the SQL Server log registry these lines:
> Database backed up: Database: DATA1, creation date(time):
> 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN
:
> 2724:197:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
> ..
> ..
> That's fine but from what table is retrieving SQL that information?
> Best regards,
>
>|||Below four tables in the msdb database:
dbo.backupfile
dbo.backupmediafamily
dbo.backupmediaset
dbo.backupset
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:8AA0C8CB-9368-4C70-8883-4442B4CAA10E@.microsoft.com...
> Dear all,
> I would like to know in what table the backups are stored.
> I mean, I see in the SQL Server log registry these lines:
> Database backed up: Database: DATA1, creation date(time):
> 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN
:
> 2724:197:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
> ..
> ..
> That's fine but from what table is retrieving SQL that information?
> Best regards,
>
>|||Thanks a lot to both,
"Tibor Karaszi" wrote:

> Below four tables in the msdb database:
> dbo.backupfile
> dbo.backupmediafamily
> dbo.backupmediaset
> dbo.backupset
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:8AA0C8CB-9368-4C70-8883-4442B4CAA10E@.microsoft.com...
>

backup views?

Hi,
I have in one database about 300 views. I have just backed up this database
and restored it with a different name. I have to drop all views from the
first database and the plan is to use the backed up database to restore the
views. How do I do this? If I use DTS, will data be ovewritten from the 2nd
database to the first one?
thanks,
aolxp
Hi,
You can not restore only the view from the database backup.
View will not hold any data until or unless u are using Indexed view. So you
can very well overwrite the view. Even if it is table in DTS
you have option to Overwrite or Append to existing table.
Thanks
Hari
SQL Server MVP
"aolxp" <sa@.anonymous.com> wrote in message
news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> I have in one database about 300 views. I have just backed up this
> database and restored it with a different name. I have to drop all views
> from the first database and the plan is to use the backed up database to
> restore the views. How do I do this? If I use DTS, will data be ovewritten
> from the 2nd database to the first one?
> thanks,
> aolxp
>
|||Thanks Hari. I will actually delete all the views from the 1st database and
then use the second db to DTS them. No indexed views.
will it be allright?
aolxp
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O9K4fiJRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not restore only the view from the database backup.
> View will not hold any data until or unless u are using Indexed view. So
> you can very well overwrite the view. Even if it is table in DTS
> you have option to Overwrite or Append to existing table.
> Thanks
> Hari
> SQL Server MVP
> "aolxp" <sa@.anonymous.com> wrote in message
> news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
>
|||Hi,
Yes, You can do that.
But best approach is script the views using Enterprise manager from 2nd
database and open query analyzer and execute in first database.
Thanks
Hari
SQL Server MVP
"aolxp" <sa@.anonymous.com> wrote in message
news:uS6kBlJRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari. I will actually delete all the views from the 1st database
> and then use the second db to DTS them. No indexed views.
> will it be allright?
> aolxp
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O9K4fiJRFHA.2744@.TK2MSFTNGP10.phx.gbl...
>

backup views?

Hi,
I have in one database about 300 views. I have just backed up this database
and restored it with a different name. I have to drop all views from the
first database and the plan is to use the backed up database to restore the
views. How do I do this? If I use DTS, will data be ovewritten from the 2nd
database to the first one?
thanks,
aolxpHi,
You can not restore only the view from the database backup.
View will not hold any data until or unless u are using Indexed view. So you
can very well overwrite the view. Even if it is table in DTS
you have option to Overwrite or Append to existing table.
Thanks
Hari
SQL Server MVP
"aolxp" <sa@.anonymous.com> wrote in message
news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> I have in one database about 300 views. I have just backed up this
> database and restored it with a different name. I have to drop all views
> from the first database and the plan is to use the backed up database to
> restore the views. How do I do this? If I use DTS, will data be ovewritten
> from the 2nd database to the first one?
> thanks,
> aolxp
>|||Thanks Hari. I will actually delete all the views from the 1st database and
then use the second db to DTS them. No indexed views.
will it be allright?
aolxp
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O9K4fiJRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not restore only the view from the database backup.
> View will not hold any data until or unless u are using Indexed view. So
> you can very well overwrite the view. Even if it is table in DTS
> you have option to Overwrite or Append to existing table.
> Thanks
> Hari
> SQL Server MVP
> "aolxp" <sa@.anonymous.com> wrote in message
> news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have in one database about 300 views. I have just backed up this
>> database and restored it with a different name. I have to drop all views
>> from the first database and the plan is to use the backed up database to
>> restore the views. How do I do this? If I use DTS, will data be
>> ovewritten from the 2nd database to the first one?
>> thanks,
>> aolxp
>|||Hi,
Yes, You can do that.
But best approach is script the views using Enterprise manager from 2nd
database and open query analyzer and execute in first database.
Thanks
Hari
SQL Server MVP
"aolxp" <sa@.anonymous.com> wrote in message
news:uS6kBlJRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari. I will actually delete all the views from the 1st database
> and then use the second db to DTS them. No indexed views.
> will it be allright?
> aolxp
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O9K4fiJRFHA.2744@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> You can not restore only the view from the database backup.
>> View will not hold any data until or unless u are using Indexed view. So
>> you can very well overwrite the view. Even if it is table in DTS
>> you have option to Overwrite or Append to existing table.
>> Thanks
>> Hari
>> SQL Server MVP
>> "aolxp" <sa@.anonymous.com> wrote in message
>> news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
>> Hi,
>> I have in one database about 300 views. I have just backed up this
>> database and restored it with a different name. I have to drop all views
>> from the first database and the plan is to use the backed up database to
>> restore the views. How do I do this? If I use DTS, will data be
>> ovewritten from the 2nd database to the first one?
>> thanks,
>> aolxp
>>
>

backup views?

Hi,
I have in one database about 300 views. I have just backed up this database
and restored it with a different name. I have to drop all views from the
first database and the plan is to use the backed up database to restore the
views. How do I do this? If I use DTS, will data be ovewritten from the 2nd
database to the first one?
thanks,
aolxpHi,
You can not restore only the view from the database backup.
View will not hold any data until or unless u are using Indexed view. So you
can very well overwrite the view. Even if it is table in DTS
you have option to Overwrite or Append to existing table.
Thanks
Hari
SQL Server MVP
"aolxp" <sa@.anonymous.com> wrote in message
news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> I have in one database about 300 views. I have just backed up this
> database and restored it with a different name. I have to drop all views
> from the first database and the plan is to use the backed up database to
> restore the views. How do I do this? If I use DTS, will data be ovewritten
> from the 2nd database to the first one?
> thanks,
> aolxp
>|||Thanks Hari. I will actually delete all the views from the 1st database and
then use the second db to DTS them. No indexed views.
will it be allright?
aolxp
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O9K4fiJRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not restore only the view from the database backup.
> View will not hold any data until or unless u are using Indexed view. So
> you can very well overwrite the view. Even if it is table in DTS
> you have option to Overwrite or Append to existing table.
> Thanks
> Hari
> SQL Server MVP
> "aolxp" <sa@.anonymous.com> wrote in message
> news:%23RBtmeJRFHA.3076@.tk2msftngp13.phx.gbl...
>|||Hi,
Yes, You can do that.
But best approach is script the views using Enterprise manager from 2nd
database and open query analyzer and execute in first database.
Thanks
Hari
SQL Server MVP
"aolxp" <sa@.anonymous.com> wrote in message
news:uS6kBlJRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari. I will actually delete all the views from the 1st database
> and then use the second db to DTS them. No indexed views.
> will it be allright?
> aolxp
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O9K4fiJRFHA.2744@.TK2MSFTNGP10.phx.gbl...
>

Backup to Unix/Linux

Hi, I have data sitting on an external MSSQL 2000 host that needs to be
backed up. Currently I'm using DTS on a Win2k desktop, but this isn't really
practical as this machine is used as a workstation.
Has anyone heard of any methods to backup to a Unix/Linux server?
Any help would be appreciated.
TIA JoIf the server can see a volume on the network then it should be possible to
write to it. If Windows can't see it then SQL Server won't either.
--
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> If the server can see a volume on the network then it should be possible to
> write to it. If Windows can't see it then SQL Server won't either.
> --
> David Portas
> SQL Server MVP
> --
>
Thanks for the reply David. I can create a Samba share, but do you know of
any scripts or tools that can copy pull the data down? I only have DTS access
to my database on the hosting provider.
I realise most people have access to a Windows server, just wondering if
anyone had done/tried/heard of it.
Jo|||You can create a DTS package with one connection and a T-sql task... The
T-sql task would be a backup command ie.
backup database prod to disk = '\\myserver\mysharename\mybackup.bak' with
init
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jo" <Jo@.discussions.microsoft.com> wrote in message
news:E1948BEF-BBF2-45A0-8695-14D766726225@.microsoft.com...
>
> "David Portas" wrote:
> > If the server can see a volume on the network then it should be possible
to
> > write to it. If Windows can't see it then SQL Server won't either.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> Thanks for the reply David. I can create a Samba share, but do you know of
> any scripts or tools that can copy pull the data down? I only have DTS
access
> to my database on the hosting provider.
> I realise most people have access to a Windows server, just wondering if
> anyone had done/tried/heard of it.
> Jo

Backup to UNC path

I'm having a problem with a backup job.

15 or so databases are being backed up overnight to a remote server using UNC paths. All bar one database is being copied fine. The one that isn't is the largest at 8gb. The error in the log is OS error 64 (the network name could not be found).

Now I've got the customer looking into any reasons why their network may be interrupted overnight, but while googling around I saw someone mentioned that backing up to UNC with databases over 2gb can cause problems? Is there any truth in this?

I am inclined to reccomend the user backs up the database to a local drive and then sets up a scheduled task to copy it across, but I'm interested to see if this is a known issue.

Thanks.My own Opinion (MOO)

You should backup to the local drive, then copy the dump.

It'll be faster and safer...

MOO|||Once upon a time, in a galaxy far, far away ... oops, wrong place

I had that situation once. You could download a free copy of gzip and use that to compress the backup on the local machine before copying it across the network. I tried winzip, but it became unreliable after the database grew to over 4 GB. I **never** had a problem unzipping the database using gzip, and before I left that company, it had grown to over 17 GB.

I backed up the db to a backup device, zipped the file into a new file, and then copied the zipped file off server, where it was then picked up by the tape backup daily. Triple redundacy!!|||As I thought :)

btw does anyone know if sql 2005 will support native compression of backups?

Sage databases have a *lot* of padding. I've got a 1gb database that compressed down to 25mb with winrar, would be nice to have backups compressed right off the bat!|||backup to disk
use network backup to backup the backup file
saves you money so you dont have to buy the sql plugin for your backup software. and you get permenent backup devices which make restoring a whole lot easier.

backup database db1 to db1full
backup database db1 to db1diff with differential
backup log db1 to db1log

i know where all of my fulls, diffs and logs are without searching through hundreds of files in a dir.

convenient yes
does it compress well? not really.