Backup to Network Drive: February 2012

Saturday, February 25, 2012

backups = 100% cpu

i'm having weird problems with a server.
backups to disk are causing the cpu to get pegged at 100% usage.
the backups are much slower than they used to be.
my theory is it's either glitchy network or glitchy SAN.
if sql server is having problems reading or writing to disk (like
happens during backups), would that be the cause of 100% cpu?
i traced the server using profiler, but saw nothing out of the ordinary
as far as cpu usage when the backups were running, but task manager
definitely shows that sqlserver is using the cpu.
on a test db of 200mb, a trasaction log backup takes 7 seconds to create
a 30kb backup file and then another 7 seconds to veryify that 30kb
backup file. i load that same test db on another server and it's 1
second to backup and 1 to verify without the massive cpu usage.
ch wrote:
> i'm having weird problems with a server.
> backups to disk are causing the cpu to get pegged at 100% usage.
> the backups are much slower than they used to be.
> my theory is it's either glitchy network or glitchy SAN.
> if sql server is having problems reading or writing to disk (like
> happens during backups), would that be the cause of 100% cpu?
> i traced the server using profiler, but saw nothing out of the
> ordinary as far as cpu usage when the backups were running, but task
> manager definitely shows that sqlserver is using the cpu.
> on a test db of 200mb, a trasaction log backup takes 7 seconds to
> create a 30kb backup file and then another 7 seconds to veryify that
> 30kb backup file. i load that same test db on another server and
> it's 1 second to backup and 1 to verify without the massive cpu usage.
Where are you writing the backups? Some details about your configuration
would help here.
David Gugick
Imceda Software
www.imceda.com
|||"ch" <ch@.dontemailme.com> wrote in message
news:41E6BA88.602612AB@.dontemailme.com...
> i'm having weird problems with a server.
> backups to disk are causing the cpu to get pegged at 100% usage.
> the backups are much slower than they used to be.
> my theory is it's either glitchy network or glitchy SAN.
> if sql server is having problems reading or writing to disk (like
> happens during backups), would that be the cause of 100% cpu?
> i traced the server using profiler, but saw nothing out of the ordinary
> as far as cpu usage when the backups were running, but task manager
> definitely shows that sqlserver is using the cpu.
How are you attached to the SAN? Is it FC or iSCSI?

> on a test db of 200mb, a trasaction log backup takes 7 seconds to create
> a 30kb backup file and then another 7 seconds to veryify that 30kb
> backup file. i load that same test db on another server and it's 1
> second to backup and 1 to verify without the massive cpu usage.

backups = 100% cpu

i'm having weird problems with a server.
backups to disk are causing the cpu to get pegged at 100% usage.
the backups are much slower than they used to be.
my theory is it's either glitchy network or glitchy SAN.
if sql server is having problems reading or writing to disk (like
happens during backups), would that be the cause of 100% cpu?
i traced the server using profiler, but saw nothing out of the ordinary
as far as cpu usage when the backups were running, but task manager
definitely shows that sqlserver is using the cpu.
on a test db of 200mb, a trasaction log backup takes 7 seconds to create
a 30kb backup file and then another 7 seconds to veryify that 30kb
backup file. i load that same test db on another server and it's 1
second to backup and 1 to verify without the massive cpu usage.ch wrote:
> i'm having weird problems with a server.
> backups to disk are causing the cpu to get pegged at 100% usage.
> the backups are much slower than they used to be.
> my theory is it's either glitchy network or glitchy SAN.
> if sql server is having problems reading or writing to disk (like
> happens during backups), would that be the cause of 100% cpu?
> i traced the server using profiler, but saw nothing out of the
> ordinary as far as cpu usage when the backups were running, but task
> manager definitely shows that sqlserver is using the cpu.
> on a test db of 200mb, a trasaction log backup takes 7 seconds to
> create a 30kb backup file and then another 7 seconds to veryify that
> 30kb backup file. i load that same test db on another server and
> it's 1 second to backup and 1 to verify without the massive cpu usage.
Where are you writing the backups? Some details about your configuration
would help here.
--
David Gugick
Imceda Software
www.imceda.com|||"ch" <ch@.dontemailme.com> wrote in message
news:41E6BA88.602612AB@.dontemailme.com...
> i'm having weird problems with a server.
> backups to disk are causing the cpu to get pegged at 100% usage.
> the backups are much slower than they used to be.
> my theory is it's either glitchy network or glitchy SAN.
> if sql server is having problems reading or writing to disk (like
> happens during backups), would that be the cause of 100% cpu?
> i traced the server using profiler, but saw nothing out of the ordinary
> as far as cpu usage when the backups were running, but task manager
> definitely shows that sqlserver is using the cpu.
How are you attached to the SAN? Is it FC or iSCSI?
> on a test db of 200mb, a trasaction log backup takes 7 seconds to create
> a 30kb backup file and then another 7 seconds to veryify that 30kb
> backup file. i load that same test db on another server and it's 1
> second to backup and 1 to verify without the massive cpu usage.

backups = 100% cpu

i'm having weird problems with a server.
backups to disk are causing the cpu to get pegged at 100% usage.
the backups are much slower than they used to be.
my theory is it's either glitchy network or glitchy SAN.
if sql server is having problems reading or writing to disk (like
happens during backups), would that be the cause of 100% cpu?
i traced the server using profiler, but saw nothing out of the ordinary
as far as cpu usage when the backups were running, but task manager
definitely shows that sqlserver is using the cpu.
on a test db of 200mb, a trasaction log backup takes 7 seconds to create
a 30kb backup file and then another 7 seconds to veryify that 30kb
backup file. i load that same test db on another server and it's 1
second to backup and 1 to verify without the massive cpu usage.ch wrote:
> i'm having weird problems with a server.
> backups to disk are causing the cpu to get pegged at 100% usage.
> the backups are much slower than they used to be.
> my theory is it's either glitchy network or glitchy SAN.
> if sql server is having problems reading or writing to disk (like
> happens during backups), would that be the cause of 100% cpu?
> i traced the server using profiler, but saw nothing out of the
> ordinary as far as cpu usage when the backups were running, but task
> manager definitely shows that sqlserver is using the cpu.
> on a test db of 200mb, a trasaction log backup takes 7 seconds to
> create a 30kb backup file and then another 7 seconds to veryify that
> 30kb backup file. i load that same test db on another server and
> it's 1 second to backup and 1 to verify without the massive cpu usage.
Where are you writing the backups? Some details about your configuration
would help here.
David Gugick
Imceda Software
www.imceda.com|||"ch" <ch@.dontemailme.com> wrote in message
news:41E6BA88.602612AB@.dontemailme.com...
> i'm having weird problems with a server.
> backups to disk are causing the cpu to get pegged at 100% usage.
> the backups are much slower than they used to be.
> my theory is it's either glitchy network or glitchy SAN.
> if sql server is having problems reading or writing to disk (like
> happens during backups), would that be the cause of 100% cpu?
> i traced the server using profiler, but saw nothing out of the ordinary
> as far as cpu usage when the backups were running, but task manager
> definitely shows that sqlserver is using the cpu.
How are you attached to the SAN? Is it FC or iSCSI?

> on a test db of 200mb, a trasaction log backup takes 7 seconds to create
> a 30kb backup file and then another 7 seconds to veryify that 30kb
> backup file. i load that same test db on another server and it's 1
> second to backup and 1 to verify without the massive cpu usage.

backups - complete, differential and transaction log

Hi,
I am new to database administration and I was looking at the backups setup
on a database-
Full backup - every week, overwrite existing media
Differential backup - every night, append to media
Transaction log backup - every hour, append to media
The size of the differential backup file has grown to 40 GB while the actual
database full backup is just 1 GB. This led me to wonder why do we need to
use the setting "Append to media" for the Differential Backup. It should be
"Overwrite existing media" since I am using a disk to write the backups?
Am I correct? What's the correct setting for transaction log backups? Thanks.For DIFFERENTIAL backups: When you have to restore from backups, you will
need to restore the last FULL backup, and then restore the last DIFFERENTIAL
backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
since the last FULL backup. (And then, of course, the remaining TLOG backups
made since the last DIFFERENTIAL.)
Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
during the stressed restore
It is ok to have only the last DIFFERENTIAL.
TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> Hi,
> I am new to database administration and I was looking at the backups setup
> on a database-
> Full backup - every week, overwrite existing media
> Differential backup - every night, append to media
> Transaction log backup - every hour, append to media
> The size of the differential backup file has grown to 40 GB while the
> actual
> database full backup is just 1 GB. This led me to wonder why do we need to
> use the setting "Append to media" for the Differential Backup. It should
> be
> "Overwrite existing media" since I am using a disk to write the backups?
> Am I correct? What's the correct setting for transaction log backups?
> Thanks.|||Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
Am I correct?
"Arnie Rowland" wrote:
> For DIFFERENTIAL backups: When you have to restore from backups, you will
> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backups
> made since the last DIFFERENTIAL.)
> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
> during the stressed restore
> It is ok to have only the last DIFFERENTIAL.
> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> > Hi,
> >
> > I am new to database administration and I was looking at the backups setup
> > on a database-
> > Full backup - every week, overwrite existing media
> > Differential backup - every night, append to media
> > Transaction log backup - every hour, append to media
> >
> > The size of the differential backup file has grown to 40 GB while the
> > actual
> > database full backup is just 1 GB. This led me to wonder why do we need to
> > use the setting "Append to media" for the Differential Backup. It should
> > be
> > "Overwrite existing media" since I am using a disk to write the backups?
> >
> > Am I correct? What's the correct setting for transaction log backups?
> > Thanks.
>
>|||What you need to keep only you can decide. When you do a restore, you use:
Most recent db backup
Most recent diff backup
All subsequent log backups after the diff backup.
But:
Say you had a database corruption introduced between the last db backup and the most recent diff
backup. And say that the corruption is not still present if you restore according to above scheme.
You would not want to restore the most recent db backup, and all subsequent log backup (so the
corruption is not likely to be re-introduced).
Or say that you need a state of the database as it were at tuesday 14:34. If you did db backup sun,
diff mon, diff, tue, diff wed, diff thu, you would now want to restore db, diff tue night, and log
backups until that point in time.
So you do need to carefully consider your possible restore scenarios, before you can say what you
need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:3E7C4E57-2AAB-47C1-A365-99AA537DC2AD@.microsoft.com...
> Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
> 'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
> since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
> Am I correct?
> "Arnie Rowland" wrote:
>> For DIFFERENTIAL backups: When you have to restore from backups, you will
>> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
>> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
>> since the last FULL backup. (And then, of course, the remaining TLOG backups
>> made since the last DIFFERENTIAL.)
>> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
>> during the stressed restore
>> It is ok to have only the last DIFFERENTIAL.
>> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
>> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>> > Hi,
>> >
>> > I am new to database administration and I was looking at the backups setup
>> > on a database-
>> > Full backup - every week, overwrite existing media
>> > Differential backup - every night, append to media
>> > Transaction log backup - every hour, append to media
>> >
>> > The size of the differential backup file has grown to 40 GB while the
>> > actual
>> > database full backup is just 1 GB. This led me to wonder why do we need to
>> > use the setting "Append to media" for the Differential Backup. It should
>> > be
>> > "Overwrite existing media" since I am using a disk to write the backups?
>> >
>> > Am I correct? What's the correct setting for transaction log backups?
>> > Thanks.
>>

Backups - 2000 -> 2005

If I create a backup in SQL Server 2000 can I restore that database to SQL
Server 2005?
If not, is there some tool to convert a 2K DB to 2005?
Thanks."Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
Yes.
Note it's one way. You can't restore a 2005 backup to 2000.
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to a 2005 server. You can also
detach from SQL Server 2000 and attach to 2005. However, you can not do the
same from SQL Server 2005 to 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
>|||Thanks. That saved me an hour or two looking through BOL.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>

Backups - 2000 -> 2005

If I create a backup in SQL Server 2000 can I restore that database to SQL
Server 2005?
If not, is there some tool to convert a 2K DB to 2005?
Thanks."Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
Yes.
Note it's one way. You can't restore a 2005 backup to 2000.

> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to a 2005 server. You can also
detach from SQL Server 2000 and attach to 2005. However, you can not do the
same from SQL Server 2005 to 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:

> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
>|||Thanks. That saved me an hour or two looking through BOL.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>

Backups

Hi,

I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?

Any suggestions?

Thanks,

Dale

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

Thanks for that. In answer to your question, the easiest way I know to backup the site (not just the database) is to copy the entire site using FTP, included in the site is the database file database.mdf under App_Data which is auto-mounted.

Given that ASP.NET 2.0 comes with this database xcopy feature and the App_Data directory it seemed such a nice solution that I didn't consider the backup implications until now. Now I am wondering if anyone has used the App_Data directory for a live site and if so how they manage backups.

Thanks,

Caddre:

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||

Thanks for the comprehensive reply - I do agree with you although in this case its nothing as important as transaction data. BTW - using the App_Data directory you still get a mdf & ldf file, Its a normal SQL Server 2005 Express database, its just that its physically located in the website and its automatically mounted when access is required. In all other respects its the same.

I was assuming (probably a bad move) that because Microsoft has developed this architecture it was therefore a *good* way to do things. So far it has proved to be *good* but now maybe with the exception of taking a backup. Therefore I was also assuming (another bad move) that there was a way to backup such an architecture.

Caddre:

http://forums.asp.net/thread/1454694.aspx

|||Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.|||

Thanks again, so just to sumamrise you're recommending that I convert my 'user instance' database into a 'real' database? Can you clarify, are you also suggesting that I move the physical location of the database from the website to the SQL data directory?

You've been very helpful, I appreciate it.

Caddre:
Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.

|||

No I am not recommending you change the location just receate it through restore or reattach it. User instance is a database that is independent of the SQL Server engine with known issues that is covered by the SQL Server team in the blog entry below. Your database located in the App_Data directory is a User instance. What I am suggesting is Backup the User instance and Restore at the same folder location but this time it is not a user instance because when you choose the Restore location SQL Server engine maps it as the physical location of the database. Microsoft have also provided a guide to use Attach to change the location you are use the method that suits you purpose.

http://msdn2.microsoft.com/en-us/library/ms179316.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

|||Thank you! Much appreciated!|||

CoolKiwiBloke:
Thank you! Much appreciated!

I am glad I could help.

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
..
..
..
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!
I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>
|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

backups

We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather than
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
Dan D.
You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else posted.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished on
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:

> We use Veritas for backups on SS2000. Right now we do backups using the sql
> server agent. Would there be any problem doing just a file backup rather than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware related
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and get
> the data backed up. The MDF and LDF files are in use, as someone else posted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, then
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the market
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:

backups

I am unable to modify/delete a backup job for master database.
Error 14274: Cannot add, update or delete a job that originated from MSX
server.
??
Help!
Did you recently migrate the msdb database from another server? Are you the
DBA responsible for managing these?
You get that error for one of the two reasons above usually. The second one
happens when someone is using a single server to administer the jobs on
several servers.
If you look at the sysjobs table in the msdb database, you will see a column
called originating_server. That will be a different name then the instance
this msdb and master database reside on. If you update that column, you will
no longer get the error.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"docsql" wrote:

> I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> ??
> Help!
>
>
|||Hi,
You may also want to check if you encounter the following known issue:
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming
http://support.microsoft.com/?id=281642
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: backups
| thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
| X-WBNR-Posting-Host: 65.69.68.118
| From: "=?Utf-8?B?TWVhbk9sZERCQQ==?="
<MeanOldDBA@.discussions.microsoft.com>
| References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: RE: backups
| Date: Thu, 6 Oct 2005 19:18:02 -0700
| Lines: 33
| Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Did you recently migrate the msdb database from another server? Are you
the
| DBA responsible for managing these?
|
| You get that error for one of the two reasons above usually. The second
one
| happens when someone is using a single server to administer the jobs on
| several servers.
|
| If you look at the sysjobs table in the msdb database, you will see a
column
| called originating_server. That will be a different name then the
instance
| this msdb and master database reside on. If you update that column, you
will
| no longer get the error.
|
| --
| MeanOldDBA
| derrickleggett@.hotmail.com
| http://weblogs.sqlteam.com/derrickl
|
| When life gives you a lemon, fire the DBA.
|
|
| "docsql" wrote:
|
| > I am unable to modify/delete a backup job for master database.
| >
| > Error 14274: Cannot add, update or delete a job that originated from
MSX
| > server.
| >
| > ??
| >
| > Help!
| >
| >
| >
|
|||... and this http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pjczSIwyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> You may also want to check if you encounter the following known issue:
> 281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
> Renaming
> http://support.microsoft.com/?id=281642
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: backups
> | thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
> | X-WBNR-Posting-Host: 65.69.68.118
> | From: "=?Utf-8?B?TWVhbk9sZERCQQ==?="
> <MeanOldDBA@.discussions.microsoft.com>
> | References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
> | Subject: RE: backups
> | Date: Thu, 6 Oct 2005 19:18:02 -0700
> | Lines: 33
> | Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Did you recently migrate the msdb database from another server? Are you
> the
> | DBA responsible for managing these?
> |
> | You get that error for one of the two reasons above usually. The second
> one
> | happens when someone is using a single server to administer the jobs on
> | several servers.
> |
> | If you look at the sysjobs table in the msdb database, you will see a
> column
> | called originating_server. That will be a different name then the
> instance
> | this msdb and master database reside on. If you update that column, you
> will
> | no longer get the error.
> |
> | --
> | MeanOldDBA
> | derrickleggett@.hotmail.com
> | http://weblogs.sqlteam.com/derrickl
> |
> | When life gives you a lemon, fire the DBA.
> |
> |
> | "docsql" wrote:
> |
> | > I am unable to modify/delete a backup job for master database.
> | >
> | > Error 14274: Cannot add, update or delete a job that originated from
> MSX
> | > server.
> | >
> | > ??
> | >
> | > Help!
> | >
> | >
> | >
> |
>
|||open sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished
|||open msdb..sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished
|||"docsql" <docsql@.noemail.nospam> wrote in message
news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> ??
> Help!
>
|||http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"docsql" <docsql@.noemail.nospam> wrote in message news:eVx37BpzFHA.908@.tk2msftngp13.phx.gbl...
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

Backups

I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?
Are you asking a question or responding to the question already asked in this thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?

Backups

I am using the maintenance plan wizard for Full backups and tlog backups. How do I include differential backups? Apparently I can't as part of the wizard, can I combine the current plan (full backups + tlog backups) thru the wizard and add a diff backup
seperately?
How to schedule them so that recovery would make sense?
Thanks
Yes you would simply create another job that does the diff backup... ( You
can acutally do this with the backup job Dialog box in SQL Enterprise
manager, then choose schedule.)
If you backup the whole db daily you might diff every 4 hours.. but most
folks will use diffs when they back the database up weekly... They'll do a
diff at the end of the business day, and perhaps another after nightly
batches...
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
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:EAF59FCE-E8ED-4C23-ACC8-4A089CD1815E@.microsoft.com...
> I am using the maintenance plan wizard for Full backups and tlog backups.
How do I include differential backups? Apparently I can't as part of the
wizard, can I combine the current plan (full backups + tlog backups) thru
the wizard and add a diff backup seperately?
> How to schedule them so that recovery would make sense?
> Thanks
>

backups

I am in search of a simple .bat file or .vbs script that I can use to
stop my SQL database server. I am looking to schedule a script of sorts
to initiate at a certain time every day. This script needs to be able to
stop my server and/or initiate a backup. The script should be able to be
ran remotely from the lan. can anyone help me?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"jeff wilmoth" <jeff_wilmoth@.yahoo.com> wrote in message
news:40185174$0$70300$75868355@.news.frii.net...
> I am in search of a simple .bat file or .vbs script that I can use to
> stop my SQL database server. I am looking to schedule a script of sorts
> to initiate at a certain time every day. This script needs to be able to
> stop my server and/or initiate a backup. The script should be able to be
> ran remotely from the lan. can anyone help me?

First question, why not use the online backup capabilities of SQL Server?

In any case, a simple

NET STOP MSSQLSERVER

and

NET START MSSQLSERVER

will stop and start SQL Server

If you have SQL Server Agent running, you can either do:

NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER

NET STOP MSSQLSERVER /Y

And then to start either

NET START MSSQLSERVER
NET START SQLSERVERAGENT

or simply

NET START SQLSERVERAGENT

which should start SQL Server first.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Backups

I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in thi
s thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx
.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?

backups

I am unable to modify/delete a backup job for master database.
Error 14274: Cannot add, update or delete a job that originated from MSX
server.
'?
Help!Did you recently migrate the msdb database from another server? Are you the
DBA responsible for managing these?
You get that error for one of the two reasons above usually. The second one
happens when someone is using a single server to administer the jobs on
several servers.
If you look at the sysjobs table in the msdb database, you will see a column
called originating_server. That will be a different name then the instance
this msdb and master database reside on. If you update that column, you wil
l
no longer get the error.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"docsql" wrote:

> I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> '?
> Help!
>
>|||Hi,
You may also want to check if you encounter the following known issue:
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming
http://support.microsoft.com/?id=281642
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: backups
| thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
| X-WBNR-Posting-Host: 65.69.68.118
| From: "examnotes"
<MeanOldDBA@.discussions.microsoft.com>
| References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: RE: backups
| Date: Thu, 6 Oct 2005 19:18:02 -0700
| Lines: 33
| Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Did you recently migrate the msdb database from another server? Are you
the
| DBA responsible for managing these?
|
| You get that error for one of the two reasons above usually. The second
one
| happens when someone is using a single server to administer the jobs on
| several servers.
|
| If you look at the sysjobs table in the msdb database, you will see a
column
| called originating_server. That will be a different name then the
instance
| this msdb and master database reside on. If you update that column, you
will
| no longer get the error.
|
| --
| MeanOldDBA
| derrickleggett@.hotmail.com
| http://weblogs.sqlteam.com/derrickl
|
| When life gives you a lemon, fire the DBA.
|
|
| "docsql" wrote:
|
| > I am unable to modify/delete a backup job for master database.
| >
| > Error 14274: Cannot add, update or delete a job that originated from
MSX
| > server.
| >
| > '?
| >
| > Help!
| >
| >
| >
||||... and this http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pjczSIwyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> You may also want to check if you encounter the following known issue:
> 281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
> Renaming
> http://support.microsoft.com/?id=281642
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> | Thread-Topic: backups
> | thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
> | X-WBNR-Posting-Host: 65.69.68.118
> | From: "examnotes"
> <MeanOldDBA@.discussions.microsoft.com>
> | References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
> | Subject: RE: backups
> | Date: Thu, 6 Oct 2005 19:18:02 -0700
> | Lines: 33
> | Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Did you recently migrate the msdb database from another server? Are you
> the
> | DBA responsible for managing these?
> |
> | You get that error for one of the two reasons above usually. The second
> one
> | happens when someone is using a single server to administer the jobs on
> | several servers.
> |
> | If you look at the sysjobs table in the msdb database, you will see a
> column
> | called originating_server. That will be a different name then the
> instance
> | this msdb and master database reside on. If you update that column, you
> will
> | no longer get the error.
> |
> | --
> | MeanOldDBA
> | derrickleggett@.hotmail.com
> | http://weblogs.sqlteam.com/derrickl
> |
> | When life gives you a lemon, fire the DBA.
> |
> |
> | "docsql" wrote:
> |
> | > I am unable to modify/delete a backup job for master database.
> | >
> | > Error 14274: Cannot add, update or delete a job that originated from
> MSX
> | > server.
> | >
> | > '?
> | >
> | > Help!
> | >
> | >
> | >
> |
>|||open sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished|||open msdb..sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished|||"docsql" <docsql@.noemail.nospam> wrote in message
news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> '?
> Help!
>|||http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"docsql" <docsql@.noemail.nospam> wrote in message news:eVx37BpzFHA.908@.tk2msftngp13.phx.gbl.
.
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

Backups

I have created a backup schedule for all databases on a
SQL server. I have Created a Backup device for each db.
and a single stored procedure that is called with database
name, device & retain days passed as parameters. All
Devices are network locations. I didn't want these backups
to swallow the entire disk space so i set the retain days
to 14. After 2 weeks i hoped that each backup would have
been overwriten. Well that was my logic. 2 weeks are up
and the files created are still growing. After some
investigation it is apparent that the expiry dates have
been passed but the backups sets have not been
overwritten. So much for my plan. I have now re-read BOL
and realised that i have got my wires crossed, the entire
media is overwriten when the expiry dates of all backups
within have been reached. To me this is topsy turvy, why
would i wish to overwrite an entire backup file, maybe if
i had taken a back up of the backup then i would wish to
delete it. Have i yet again misunderstood BOL. What i want
to do is maintain a dynamic history of backups. I back up
my database to a device, this backup lasts for 2 weeks and
then is overwriten. so the actual physical file is never
deleted, only the contents within when the expiry date is
reached.
Help!!!!!!!Expiredays and retaindays are only there to not allow you to overwrite using
the INIT before a certain day. If you aren't using INIT or if you are using
NOINIT, it will always be append. And, it is all or nothing.
If you want generation handling, either use the Maint Wizard, a 3:rd party
like www.dbmaint.com or some TSQL programming to handle this (using more
than one backup device).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
> I have created a backup schedule for all databases on a
> SQL server. I have Created a Backup device for each db.
> and a single stored procedure that is called with database
> name, device & retain days passed as parameters. All
> Devices are network locations. I didn't want these backups
> to swallow the entire disk space so i set the retain days
> to 14. After 2 weeks i hoped that each backup would have
> been overwriten. Well that was my logic. 2 weeks are up
> and the files created are still growing. After some
> investigation it is apparent that the expiry dates have
> been passed but the backups sets have not been
> overwritten. So much for my plan. I have now re-read BOL
> and realised that i have got my wires crossed, the entire
> media is overwriten when the expiry dates of all backups
> within have been reached. To me this is topsy turvy, why
> would i wish to overwrite an entire backup file, maybe if
> i had taken a back up of the backup then i would wish to
> delete it. Have i yet again misunderstood BOL. What i want
> to do is maintain a dynamic history of backups. I back up
> my database to a device, this backup lasts for 2 weeks and
> then is overwriten. so the actual physical file is never
> deleted, only the contents within when the expiry date is
> reached.
> Help!!!!!!!
>|||Thanks Tibor
Are there any sys SP's or XP's that can be used to edit
backup files? Maybe i could remove expired files..
I have created a stored procedure that looks at a backup
device and tells me which full, DIff and TL backups need
to be applied to restore to a specified point in time. It
looks as if this will nor work if i have to create new
devices..rats...
I pull my hair out some times with the illogical-ness of
SQL server

>--Original Message--
>Expiredays and retaindays are only there to not allow you
to overwrite using
>the INIT before a certain day. If you aren't using INIT
or if you are using
>NOINIT, it will always be append. And, it is all or
nothing.
>If you want generation handling, either use the Maint
Wizard, a 3:rd party
>like www.dbmaint.com or some TSQL programming to handle
this (using more
>than one backup device).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
database
backups
days
entire
why
if
want
up
and
is
>
>.
>|||There are no tool with which you can remove selective backups inside a
backup file, I'm afraid.
One alternative is to do append, say over one day (assume db backup once per
day and log backup once per hour). Then after one day, you rename the Active
to give it a timestamp (so you have generations) and then do INIT to the
active one. This is how we did it in Db Maint up until the current version,
where we decided to not do append anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
> Thanks Tibor
> Are there any sys SP's or XP's that can be used to edit
> backup files? Maybe i could remove expired files..
> I have created a stored procedure that looks at a backup
> device and tells me which full, DIff and TL backups need
> to be applied to restore to a specified point in time. It
> looks as if this will nor work if i have to create new
> devices..rats...
> I pull my hair out some times with the illogical-ness of
> SQL server
>
> to overwrite using
> or if you are using
> nothing.
> Wizard, a 3:rd party
> this (using more
> message
> database
> backups
> days
> entire
> why
> if
> want
> up
> and
> is|||Thanks tibor, thats great. Part of my backup script now
contains code to create a backup device every time it is
run that is named depending on a variable passed.
Alternating every week the physical file names change and
the retain days are set to 7. So as you advised i create a
file and add my backups. After a week i swich to a second
file and use this for a week. After another 7 days i
switch back to the original file that is now ready tbe
overwriten.
My SP that advises me of what backup files to aply now
works in pretty much the same way. It creates a device
based on the parameters and returns the backup history. it
then re-creates the device with the second file name and
apends this to the first run.
Thanks so much, you have been a great help..

>--Original Message--
>There are no tool with which you can remove selective
backups inside a
>backup file, I'm afraid.
>One alternative is to do append, say over one day (assume
db backup once per
>day and log backup once per hour). Then after one day,
you rename the Active
>to give it a timestamp (so you have generations) and then
do INIT to the
>active one. This is how we did it in Db Maint up until
the current version,
>where we decided to not do append anymore.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
It
you
on a
db.
have
are up
have
BOL
backups
maybe
wish to
back
weeks
never
date
>
>.
>|||I'm glad I could help, mat. Just one thing: As far as I can see, retains
days doesn't really buy you anything. I only mention this so you don't read
anything into this parameter which isn't there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d55d01c40aad$fb535920$a501280a@.phx.gbl...
> Thanks tibor, thats great. Part of my backup script now
> contains code to create a backup device every time it is
> run that is named depending on a variable passed.
> Alternating every week the physical file names change and
> the retain days are set to 7. So as you advised i create a
> file and add my backups. After a week i swich to a second
> file and use this for a week. After another 7 days i
> switch back to the original file that is now ready tbe
> overwriten.
> My SP that advises me of what backup files to aply now
> works in pretty much the same way. It creates a device
> based on the parameters and returns the backup history. it
> then re-creates the device with the second file name and
> apends this to the first run.
> Thanks so much, you have been a great help..
>
> backups inside a
> db backup once per
> you rename the Active
> do INIT to the
> the current version,
> message
> It
> you
> on a
> db.
> have
> are up
> have
> BOL
> backups
> maybe
> wish to
> back
> weeks
> never
> date

backups

database backups and differential backups

i took database back up on 01-jan-2005 and from 2nd jan onwards im taking differential backups. if i want to restore the database to a new system, is it sufficient that i restore the back up which i took on 1st jan , and the most recent differential backup ? will the entire data till the last back up date be restored?From BOL:
The sequence for restoring differential database backups is:

1.Restore the most recent database backup.

2.Restore the last differential database backup.

3.Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.

yes if u restore to the last differential database backup the database will be recoverd to that point of time.|||I'm 99% sure that you'll need to restore the last full backup, plus each differential backup in sequence in order to get back to the state of the last differential dump you've got. In other words, if you are missing the backup from day 13 out of 30, you can only get to the point of the backup from day 12.

You only explicitly restore the last file, but all of the files need to be present in order for the restore to be successful.

-PatP|||that means if i have 10 differential back ups named 1,2,..10

i'll have to restore the last full back up (on jan 1st) and then restore each of 1,2,3...10 in order. am i correct?

one more question.

BOL (under Differential Database Backups ) says that "A differential database backup records only the data that has changed since the last database backup".

so is it necessary that i restore full backup & differential backups from 1 to 10

i think restoring full back up + differential backup no 10 will give me all the data .. i donno whether im correct..

pl discuss|||You have to restore only the last diff. backup made, not the whole sequence.
See 'Tracking Modified Extents' topic in Books Online for details. mojza

backups

I have some backups scheduled on my sqlserver.most of them are differentail backups.But among them some have options 'Append to media' and some of them are 'Overwrite existing media '. But i dont know which of them falls into which category.How can i find that and can i cahnge 'Append to media' to 'Overwrite existing media '?If yes how can i do that?Does it effect my db if i change it?
INfact which one is the best option ('Append to media','Overwrite existing media ') among these two?
Thanks.you need to identify your app requirements, retention resources availabilities, disaster recovery processes (if exist, else - develop them based on business needs), and then tailor your backup strategy to the above. looks like you inherited the box(-s) from someone else and nothing is documented.|||yep, nothing is documented abt the existing backups,
How do i know the info?

Backups

Hi Guys,

I'm pretty new to SQL Server. I have a ? regarding backups.

I have a database which I'm backing up everyday using BACKUP DATABASE db TO db_bak WITH INIT. This job runs everyday successfully, but I'm having an evergrowing transaction log file. I presumed a full database backup will apply transaction logs and will truncate the log files. Isnt it so, or am i missing some concepts? This database can have a data loss of 1 day which is covered by my full database backup job. I dont need a transaction log backup. But to reduce log file size should I do a log backup?

-Sunil.Depends...(love that answer)

What's your recovery model? Simple would do what you're thinking...

I have a feeling it's FULL...in which case you need to dump the tranny too...

Look at the maint plan wizard to help you out

Also you need to determine what type of strategy you need to employ...

Basically, how much time/data can you afford to lose...|||Thanks Brett,

My recovery model is FULL and i prefer to keep it that way. So, i reckon I should set transaction log backups aswell. I have set database backup to run every 6PM, if i create another job to backup transaction logs at 7PM, will it do? Also, since my database is big (It is MSProject database) database backup is done everyday in overwrite mode to avoid appending 200MB everyday to the backup file. Could transaction log backup be done in overwrite mode aswell or is it necessary to be appended every day? If there is a recovery scenario, will it all add well?

I'm still wondering why isnt transaction log truncated after a successful full backup. Why do we need transaction logs if the database itself is fully backed up.

-Sunil.|||Originally posted by sunilthomas98
I'm still wondering why isnt transaction log truncated after a successful full backup. Why do we need transaction logs if the database itself is fully backed up.
-Sunil.

Because it is a point in time backup...logs are usually dumped periodically to recover data throughout the daya (I do mine every 10 minutes)

Why not first go through the db maint wizard...it'll show you all the things you can do, without having to code them in T-SQL...it'll even schedule the jobs...

Then by a book...one of my favorites is SQL Server 911 by Brian Knight..good book

Here's a real story...

HR "dba" (the quotes are on purpose) had the type of scenario you mention..

It was time to enter in all the work for the bonuses...which got done...

Now scrub boy messed something up...we think he had a glitch a decided to recover the db from last nights dump...effectivley wiping out all the work the clerks had enetered...soooooo no bonuses...right?

Wrong...the vp called ALL of the clerks in plus IT staff to re-enter the data...so at about 4:00 am it was done...and the file went out the next day...

He's not with us anymore...|||Thanks Brett, I'll do that not to miss my next bonus (if it comes)

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

Backups

Here is the question, what does SQL Server do with databases that are in use but I am performing a backup. Our company has some kind of software that cannot have a database in use during a backup. I suggested that SQL Server perform it's own backup and
then the can let the tape drives backup my back up. What does SQL Server do in this case?
You are right, SQL Server can do online backups. When you do a database
backup, it backups data files, and after that the portinon of the
transaction log that holds changes made during the data files backup.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in
use but I am performing a backup. Our company has some kind of software
that cannot have a database in use during a backup. I suggested that SQL
Server perform it's own backup and then the can let the tape drives backup
my back up. What does SQL Server do in this case?
|||SQL Server basically doesn't disturb the users at all while it does a backup. It does a checkpoint, backs up
all data pages and then all log records that were created during the time it took to backup the data pages.
What the users will see is possibly some decreased performance as SQL Server generated I/O while the backup
was running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:FB7C977B-45A5-4DAD-9B9A-A87F6A229DA2@.microsoft.com...
> Here is the question, what does SQL Server do with databases that are in use but I am performing a backup.
Our company has some kind of software that cannot have a database in use during a backup. I suggested that
SQL Server perform it's own backup and then the can let the tape drives backup my back up. What does SQL
Server do in this case?

Backups

I need to restore a backup dbs from one SQL Server to another on a nightly
basis. We do our backups using a Maintenance Plan. The name of the backup
file is different every night as the backups of each db run at different
times. Is there a way to fix the name of the backup files so they don't
have a time element in the name? Is there a way to discover the name that
was used last?
Barry
Set up a job to do the backup with T-SQL instead of using the maint plan.
<barryfz@.home.com> wrote in message
news:%23neDZcOnEHA.4004@.TK2MSFTNGP10.phx.gbl...
> I need to restore a backup dbs from one SQL Server to another on a nightly
> basis. We do our backups using a Maintenance Plan. The name of the backup
> file is different every night as the backups of each db run at different
> times. Is there a way to fix the name of the backup files so they don't
> have a time element in the name? Is there a way to discover the name that
> was used last?
> Barry
>
|||I had to do this for a file that was being sent through a firewall every
night via FTP. So I dynamically created the ftp script every night.
In your situation then in DOS do something like this
Create a batch file
SETLOCAL
SET File_Name=
FOR /F "usebackq" %%x IN (`dir *.txt /b /od`) DO SET FILE_NAME=%%x
IF {%File_Name%}={} exit 1
COPY %File_Name% Z:\SomeFolderNamedWhatEveryYouLike
ENDLOCAL
The for loop is the interesting bit, it simply lists every txt file in
date order and assigns the name to a variable File_Name. The only issue
is for every txt file the variable is changed, so if there are 100 files
the loop goes around 100 times before it finds the correct name
Adrian
barryfz@.home.com wrote:

> I need to restore a backup dbs from one SQL Server to another on a nightly
> basis. We do our backups using a Maintenance Plan. The name of the backup
> file is different every night as the backups of each db run at different
> times. Is there a way to fix the name of the backup files so they don't
> have a time element in the name? Is there a way to discover the name that
> was used last?
> Barry
>
|||Hi Barry,
As community members had provided sufficient information about the way of
backup, I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Backups

Hi All,
We have setup Transactional Replication with queued updating subscribers option. Our Servers are in Failover cluster with windows 2000 AS. We are doing Full and Transactional log backups as usual. Can any one suggest what are the recovery scenarios, in ca
se of Publisher (if it is completely down, I mean all clusters are down) and User interactions to Subscriber immediately, so that data is more than of Publisher db.
Please provide us valuable suggestions.
Thanks,
John.
I'm not exactly sure what your question is. You loose me with this statement
:
"so that data is more than of Publisher db. "
With queued Updating, your Subscriber is essentially a mirror of your
Publisher's data. If your Publisher goes offline permanently you will lose
transactions that are on your publisher and haven't made their way back to
the Subscriber.
So, its not too hard to clone your Subscriber to rebuild your Publisher. To
do this you would have to drop your subscriber, copy the subscriber schema
and data to the publisher, re run your publication scripts on your new
publisher, and do a no-sync subscription.
If your Publisher goes offline for an extended period your chance of
bringing it back on line without conflicts increases. With Queued Updating
the conflict view only lets you view conflicts, not resolve them.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:1D2A989A-5347-4E74-AAD6-707E9EC9CE70@.microsoft.com...
> Hi All,
> We have setup Transactional Replication with queued updating subscribers
option. Our Servers are in Failover cluster with windows 2000 AS. We are
doing Full and Transactional log backups as usual. Can any one suggest what
are the recovery scenarios, in case of Publisher (if it is completely down,
I mean all clusters are down) and User interactions to Subscriber
immediately, so that data is more than of Publisher db.
> Please provide us valuable suggestions.
> Thanks,
> John.
>
|||The best suggestion I have is to look in BOL for the article about the
"synch with backup" option that can be used. This will not only walk you
through these scenarios, but explain how to create coherent, multi-server
backups.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Backups

How do you backup a SQL database so the logs are commited
to the database file? Will a backup using Veritas Backup
Exec do it for us or do I need to use SQL to backup the
product?
Thanks,
ScottAll inactive (not-currently running) transactions will be included in your
db backup, a db backup does not back up the actual log though. Your log will
still have the same footprint size and used space size.
Ray Higdon MCSE, MCDBA, CCNA
--
"spt" <anonymous@.discussions.microsoft.com> wrote in message
news:bd8b01c40c17$295146b0$a601280a@.phx.gbl...
> How do you backup a SQL database so the logs are commited
> to the database file? Will a backup using Veritas Backup
> Exec do it for us or do I need to use SQL to backup the
> product?
> Thanks,
> Scott|||I would like to get some more information before I can address your
concerns.
Any third party product, will use the SQL APIs for Backup. So the first
thing would be to do a GAP analysis of what SQL Server Backup can provide
and what your specific requirements are. For this, I would recommend that
you go through the BooksOnline Backup section that provides all the
information that you would ever need about SQL Server Backup. I would also
suggest that a Backup Plan is of now use without a Disaster Recovery plan.
For that, plese visit the following links :
http://support.microsoft.com/defaul...kb;en-us;169039
http://support.microsoft.com/defaul...kb;en-us;307775
Once you are done with it, you will get a fair idea of what you can achieve
with SQL Backup and what are the GAP areas for which you might want to use
a third party product.
Please let me know if I can be of any further assistance.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

backups

We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather tha
n
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
--
Dan D.You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else poste
d.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished o
n
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:

> We use Veritas for backups on SS2000. Right now we do backups using the sq
l
> server agent. Would there be any problem doing just a file backup rather t
han
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the fil
e
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware relate
d
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
--
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and g
et
> the data backed up. The MDF and LDF files are in use, as someone else pos
ted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, the
n
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the mark
et
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished
on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
>

Saturday, February 25, 2012

backups = 100% cpu

backups = 100% cpu

backups = 100% cpu

backups - complete, differential and transaction log

backups - complete, differential and transaction log

backups - complete, differential and transaction log

Backups - 2000 -> 2005

Backups - 2000 -> 2005

Backups

Backups

backups

backups

Backups

Backups

backups

Backups

backups

Backups

backups

backups

Backups

backups

Backups

Backups

Backups

Backups

backups

Backup to Network Drive

Blog Archive

About Me