Database backups growing exponentially

Hi All.

I'm currently maintaining 4 servers - 1 for public/customers and 3
for backups, development, etc...
I regularly backup the entire SQL database for our public server and
restore it on each of the other servers. Lately, however, the database
backups have grown (in size) incredibly fast - they've gone from about
200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this at
first since our client traffic has drastically increased as well.) The
weird thing, though, is that (on two of the backup servers) when I
restore the backup then use those servers to create a new complete
backup, the new backup is only about 200-300 MB in size.
My assumption is that there's some kind of setting buried deep inside
the sql configuration allowing it to compress or otherwise alter
backups. Does anyone have any ideas/thoughts as to what may be causing
this issue?
We're using SQL Server 7 on Windows 2000 servers.

Thanks in advance.

Gregg
GArpin@.nospam.plan3D.comHi

You are probably appending multiple backups to the same device. The INIT
keyword indicates the backup will overwrite existing backups the NOINIT
keyword indicates that the backup will be appended. See Books online for
more details.

John

<greggarpin@.hotmail.com> wrote in message
news:1112031032.928818.223430@.z14g2000cwz.googlegr oups.com...
> Hi All.
> I'm currently maintaining 4 servers - 1 for public/customers and 3
> for backups, development, etc...
> I regularly backup the entire SQL database for our public server and
> restore it on each of the other servers. Lately, however, the database
> backups have grown (in size) incredibly fast - they've gone from about
> 200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this at
> first since our client traffic has drastically increased as well.) The
> weird thing, though, is that (on two of the backup servers) when I
> restore the backup then use those servers to create a new complete
> backup, the new backup is only about 200-300 MB in size.
> My assumption is that there's some kind of setting buried deep inside
> the sql configuration allowing it to compress or otherwise alter
> backups. Does anyone have any ideas/thoughts as to what may be causing
> this issue?
> We're using SQL Server 7 on Windows 2000 servers.
> Thanks in advance.
> Gregg
> GArpin@.nospam.plan3D.com|||My thoughts too!

Not sure if you're doing the backup via a batch process or the GUI.

I'm going to describe the GUI interface, since that also includes a
scheduler attribute that you may be using.

At the bottom of the "SQLServer Backup" panel, there's a section
called "Overwrite" -- set it to "Overwrite existing media" and
perform a backup.

Item to think about: it also sounds as though you're doing a complete
backup of the database each time. If you wish to retain the historical
sequence of records being added, changed and deleted; you'll need to
select "differential backup" or "transaction log", depending upon your
requirements.

The decision as to which method of backups to use depends upon the
volatility of the data and how important the historical "log" is
versus snap-shots.

Have you also thought of replication to shift the data between the
servers? You are obviously doing a one-way star arrangement (central
master and remote copies, no changes coming back) and replication is a
perfect solution to your needs.sql

database backups

Hi All
I creatred a service that backups all my databases to a file on the server.
My backup software will backup the files at night. I'm having problems
backing up databases who's names are all numbers (1234, 1211, .. etc). I can
backup the databases manually but my script will always fail. Do I have to
do anything special in T-sql to access a database whos name is all numbers?
Thanks
PhilTry enclosing the database name in brackets:
BACKUP DATABASE [1234]
TO DISK='\\SomeServer\SomeShare\1234.bak'
WITH INIT
BTW, you can make things easier by having database names confirm to the
rules for regular identifiers as described in the Books Online
<acdata.chm::/ac_8_con_03_6e9e.htm>.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Phillip D Edwards" <phil@.benesysinc.com> wrote in message
news:%23FuNKzseDHA.2464@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I creatred a service that backups all my databases to a file on the
server.
> My backup software will backup the files at night. I'm having problems
> backing up databases who's names are all numbers (1234, 1211, .. etc).
I can
> backup the databases manually but my script will always fail. Do I
have to
> do anything special in T-sql to access a database whos name is all
numbers?
>
> Thanks
> Phil
>
>

Database Backups

I’m trying to backup all my database in SQL server 2005, but I keep getting
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
Chris Holden
Chris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> Im trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden
sql

database backups

I am able to do a backup of a database, but can not do a database maintenance
plan of the same database.
Also on doing a database maintenance plan the status says that it is
"Executing Job Step '1" and just stays there. It never finishes.
You need to refresh EM to see the change.
Andrew J. Kelly SQL MVP
"Carlton" <carlton.edwards@.fmb.com> wrote in message
news:0BFCE421-C5F8-4C37-AB65-00C9EF5C3999@.microsoft.com...
>I am able to do a backup of a database, but can not do a database
>maintenance
> plan of the same database.
> Also on doing a database maintenance plan the status says that it is
> "Executing Job Step '1" and just stays there. It never finishes.

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.
Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>
|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/inf..._truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restor
e
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model i
s
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db a
nd
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say a
t
> noon. this will save time restoring the db because you do not have to rest
ore
> all transaction log backups from the last full one, instead you restore th
e
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full
,
> or from diff to diff if there is not a full between them, or between full
to
> full if not diff), as many as you need to avoid it from growing and be sav
e
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
>|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNC
ATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox shou
ld be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the
exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done
of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and als
o
http://www.karaszi.com/SQLServer/in...o_truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If y
ou don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every t
en minutes).
Or if you don't want to do log backups, put the db in simple recovery mode a
nd only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gb
l...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>sql

Database Backups

I’m trying to backup all my database in SQL server 2005, but I keep gettin
g
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
Chris HoldenChris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> Im trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden

database backups

I am able to do a backup of a database, but can not do a database maintenanc
e
plan of the same database.
Also on doing a database maintenance plan the status says that it is
"Executing Job Step '1" and just stays there. It never finishes.You need to refresh EM to see the change.
Andrew J. Kelly SQL MVP
"Carlton" <carlton.edwards@.fmb.com> wrote in message
news:0BFCE421-C5F8-4C37-AB65-00C9EF5C3999@.microsoft.com...
>I am able to do a backup of a database, but can not do a database
>maintenance
> plan of the same database.
> Also on doing a database maintenance plan the status says that it is
> "Executing Job Step '1" and just stays there. It never finishes.

Database backups

HI All
can some one please shed some light on backups please?
im running sql 2000 and i want to know how often i should
backup and restore my MSDB and MASTER databases.
Thanks in advance
ToddI personally do them every night. You really need to back them up though
when something in them changes. Seeing as they are system databases this
should be through the SQL Server interface or stored procs i.e.
New Jobs
New Logins
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:030a01c3a70e$35404110$a101280a@.phx.gbl...
> HI All
> can some one please shed some light on backups please?
> im running sql 2000 and i want to know how often i should
> backup and restore my MSDB and MASTER databases.
> Thanks in advance
> Todd|||MSDB is important especially if you are doing things like
replication, etc. SQL Server uses it to store other
configuration/use. If you are not using any features that
utilize MSDB, then it is less important, but still
critical for SQL Server health. Generally it is fairly
small, so backing it up every night isn't a bad idea.
Master is the brains of the operation. It, too, is small,
but without a good and recent backup, you may be in
trouble if you need to restore it. I suggest nightly, but
at worst case, after updates such as a service pack since
you cannot, for example, restore a SP2 master to a SP3 SQL
Server.
You should never have to restore, but should always test
your backups elsewhere to ensure they are actually good.
However, in a D/R scenario, applying MSDB or MASTER to
another server could potentially have large negative
consequences since they contain configurations specific to
a particular instance of SQL Server.|||Hi,
I will do a daily backup of MSDB and Master database. As well as I have a
scheduled OS command which stops SQL Server and Agent during
sunday and copy the Master.mdf and mastlog.ldf file to a backup location.
This will help you a lot during crash situations.
Thanks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:030a01c3a70e$35404110$a101280a@.phx.gbl...
> HI All
> can some one please shed some light on backups please?
> im running sql 2000 and i want to know how often i should
> backup and restore my MSDB and MASTER databases.
> Thanks in advance
> Todd

Database backups

When I backup one of the SQL server databases, the size of the backup file
varies from 1.2, 3.6 or 9.8MB. All these backups were made w/in 30-min of
each other with only me doing testing, no other users. This makes me feel a
bit uneasy, like I'm not sure if the backups are valid. Is this normal?
How can test a backup to make sure it's complete? I mean w/o deleting the
database and doing a restore.> When I backup one of the SQL server databases, the size of the backup file
> varies from 1.2, 3.6 or 9.8MB. All these backups were made w/in 30-min of
> each other with only me doing testing, no other users. This makes me feel
a
> bit uneasy, like I'm not sure if the backups are valid. Is this normal?
Did you append backups to the same file? Have you done always the same
backups or do you have full database, transaction log and differential
backups?
> How can test a backup to make sure it's complete? I mean w/o deleting the
> database and doing a restore.
You can always restore to a new database, so this is not a problem. Check
the "How to restore a database backup (Enterprise Manager)" topic in Books
OnLine.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
> Tom,
> > How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
> > I have some confusion about backing up databases and transaction logs.
> > When I do a complete database backup it backs up the db and the log, but
> > when I have it set to clear out inactive entries in the log it does not.
> > When I do a log backup and set it to clear out inactive entries it does do
> > that. My question is then if I do complete database backups all the time and
> > the log is not cleared for a long time and I do a restore and then play back
> > the log from a long time ago will I corrupt the database? How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> >
> >
> >|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

database backups

I am able to do a backup of a database, but can not do a database maintenance
plan of the same database.
Also on doing a database maintenance plan the status says that it is
"Executing Job Step '1" and just stays there. It never finishes.You need to refresh EM to see the change.
--
Andrew J. Kelly SQL MVP
"Carlton" <carlton.edwards@.fmb.com> wrote in message
news:0BFCE421-C5F8-4C37-AB65-00C9EF5C3999@.microsoft.com...
>I am able to do a backup of a database, but can not do a database
>maintenance
> plan of the same database.
> Also on doing a database maintenance plan the status says that it is
> "Executing Job Step '1" and just stays there. It never finishes.

Database Backups

Iâ'm trying to backup all my database in SQL server 2005, but I keep getting
the following error message.
Execution failed. See the maintenance plan and SQL Server Agent job history
logs for details.
Job Backup.Subplan_1 was suspended. (SqlManagerUI)
My current operating system structure is this.
OS installed on C: and I have SQL installed on D:
I will appreciate any help on this matter.
--
Chris HoldenChris
We need more info about your backup strategy. What did you create?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4A06B2C6-D95C-4892-A070-B326C12623D6@.microsoft.com...
> I?m trying to backup all my database in SQL server 2005, but I keep
> getting
> the following error message.
> Execution failed. See the maintenance plan and SQL Server Agent job
> history
> logs for details.
> Job Backup.Subplan_1 was suspended. (SqlManagerUI)
> My current operating system structure is this.
> OS installed on C: and I have SQL installed on D:
> I will appreciate any help on this matter.
> --
> Chris Holden

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
Ivan
Hi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>
|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files[vbcol=seagreen]
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
another
> and
>

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> > I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> > device.
> >
> > I have backup the database into a file, and try to restore it into
another
> > SQL Server 2000.
> > The new database has only 1 data device and 1 log.
> > However, when I try to restore it, in Option page, it has 2 data device
> and
> > 2 log device and I have no way to restore
> >
> > how can I restore that database?
> >
> > Ivan
> >
> >
>sql

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
another[vbcol=seagreen]
> and
>

database backup-overwrites old backupfile?

Hi just wondering when you set up a scheluled database backup, does the old
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
Paul G
Software engineer.
The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
more about that GUI and what checkboxes etc you have checked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.
|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.
|||ok thanks did use the gui to set up the schedule.
Paul G
Software engineer.
"Jerry Spivey" wrote:

> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
>
|||looks like the gui has a selection do not keep longer than 4 weeks which will
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option to delete old backup
files. It creates a new file each time you do backup, including a time stamp in the file name. As
for the access denied errors, we need more information. First, understand that the service account
for SQL Server Agent and for the SQL Server service need permissions on whatever you need to do
(like accessing the backup share). I suggest you define a report file for your maint plan, as that
will probably give more details information about exactly what it is that access is denied for etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...[vbcol=seagreen]
> looks like the gui has a selection do not keep longer than 4 weeks which will
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:

database backup-overwrites old backupfile?

Hi just wondering when you set up a scheluled database backup, does the old
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
--
Paul G
Software engineer.The BACKUP command does not overwrite by default. Defile is NOINIT which is
append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the c
ommand, we need to know
more about that GUI and what checkboxes etc you have checked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the ol
d
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks did use the gui to set up the schedule.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:

> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
>|||looks like the gui has a selection do not keep longer than 4 weeks which wil
l
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> The BACKUP command does not overwrite by default. Defile is NOINIT which i
s append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the
command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option
to delete old backup
files. It creates a new file each time you do backup, including a time stamp
in the file name. As
for the access denied errors, we need more information. First, understand th
at the service account
for SQL Server Agent and for the SQL Server service need permissions on what
ever you need to do
(like accessing the backup share). I suggest you define a report file for yo
ur maint plan, as that
will probably give more details information about exactly what it is that ac
cess is denied for etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...[vbcol=seagreen]
> looks like the gui has a selection do not keep longer than 4 weeks which w
ill
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need
to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>

database backup-overwrites old backupfile?

Hi just wondering when you set up a scheluled database backup, does the old
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
--
Paul G
Software engineer.The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
more about that GUI and what checkboxes etc you have checked.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks did use the gui to set up the schedule.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> > Hi just wondering when you set up a scheluled database backup, does the
> > old
> > backup file get written over each time the scheduled backup takes place as
> > this is what I would like it to do?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||looks like the gui has a selection do not keep longer than 4 weeks which will
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> > Hi just wondering when you set up a scheluled database backup, does the old
> > backup file get written over each time the scheduled backup takes place as
> > this is what I would like it to do?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option to delete old backup
files. It creates a new file each time you do backup, including a time stamp in the file name. As
for the access denied errors, we need more information. First, understand that the service account
for SQL Server Agent and for the SQL Server service need permissions on whatever you need to do
(like accessing the backup share). I suggest you define a report file for your maint plan, as that
will probably give more details information about exactly what it is that access is denied for etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...
> looks like the gui has a selection do not keep longer than 4 weeks which will
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
>> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to
>> know
>> more about that GUI and what checkboxes etc you have checked.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>> > Hi just wondering when you set up a scheluled database backup, does the old
>> > backup file get written over each time the scheduled backup takes place as
>> > this is what I would like it to do?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>

Database backup?

Hi,
Two questions, in the query analyzer I typed this:
backup database hcn to disk 'D:\Microsoft SQL
Server\MSSQL\BACKUP\mybackup01232006.bak'
And I got a syntax error: Line 1: Incorrect syntax near 'D:\Microsoft SQL
Server\MSSQL\BACKUP\hcnprod01232006.bak'.
I also tried it without the 'disk' parameter as it looked in the online help
but got the same message. What did I do wrong?
Secondly, how can I direct this backup to another server? Using a share on
a different server with similar results.
I am able to perform backups using the enterprise manager to the location in
my first question but not to a network share using the UNC:
\\server\share\directory\subdirectory\fi
le.bck
Any help is greatly appreciated,
LinnYou're missing an =
backup database hcn to disk = 'D:\Microsoft SQL
Server\MSSQL\BACKUP\mybackup01232006.bak'
HTH. Ryan
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:ePm17cCIGHA.2928@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Two questions, in the query analyzer I typed this:
> backup database hcn to disk 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\mybackup01232006.bak'
> And I got a syntax error: Line 1: Incorrect syntax near 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\hcnprod01232006.bak'.
> I also tried it without the 'disk' parameter as it looked in the online
> help but got the same message. What did I do wrong?
> Secondly, how can I direct this backup to another server? Using a share
> on a different server with similar results.
> I am able to perform backups using the enterprise manager to the location
> in my first question but not to a network share using the UNC:
> \\server\share\directory\subdirectory\fi
le.bck
> Any help is greatly appreciated,
> Linn
>|||Ah, I see, thanks Ryan. It's always simple once you see it. How about the
other question, any idea what I'm doing wrong there? Isn't it possible to
backup to a remote server?
Thanks,
Linn
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uPeAbgCIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> You're missing an =
>
> backup database hcn to disk = 'D:\Microsoft SQL
> Server\MSSQL\BACKUP\mybackup01232006.bak'
> --
> HTH. Ryan
> "Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
> news:ePm17cCIGHA.2928@.TK2MSFTNGP10.phx.gbl...
>|||What is the account being used for running the services for SQL Server? Is i
t
a LocalSystem account? If so, it probably doesn't have access to the remote
location you have specified. Changing to a domain account that has access to
that location might solve the problem, but there could be security concerns
that you should read up on.
HTH
"Linn Kubler" wrote:

> Ah, I see, thanks Ryan. It's always simple once you see it. How about th
e
> other question, any idea what I'm doing wrong there? Isn't it possible to
> backup to a remote server?
> Thanks,
> Linn
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uPeAbgCIGHA.2064@.TK2MSFTNGP09.phx.gbl...
>
>|||I'm using the sa account and I see what you mean. How does the sa account
have rights to save files to the local directories? I can't locate it
anywhere outside of SQL. How does that work?
Thanks,
Linn
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:354CE2D0-6B8F-4AC3-A747-93B0A800DBD9@.microsoft.com...
> What is the account being used for running the services for SQL Server? Is
> it
> a LocalSystem account? If so, it probably doesn't have access to the
> remote
> location you have specified. Changing to a domain account that has access
> to
> that location might solve the problem, but there could be security
> concerns
> that you should read up on.
> HTH
> "Linn Kubler" wrote:
>|||Open up the Services (Start-ControlPanel-AdminTools-Services) and look for
the Sql server services. Check what account they are running under (Log on
as). If they are logging on as LocalSystem, that might be causing the
problem. Note that you must NOT change the service account from the Services
directly. Go to Enterprise manager (assuming you are using Sql 2k), select
the server and then, the properties. You will find a way to change it there
(I don't have access to a server right now, so cannot give you the exact tab
to look at, but it should be fairly easy to find. Change it there (again,
make sure you read up about the security concerns before you do that)
"Linn Kubler" wrote:

> I'm using the sa account and I see what you mean. How does the sa account
> have rights to save files to the local directories? I can't locate it
> anywhere outside of SQL. How does that work?
> Thanks,
> Linn
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:354CE2D0-6B8F-4AC3-A747-93B0A800DBD9@.microsoft.com...
>
>|||On the server properties, select the "Security" Tab and at the bottom of the
tab, you will notice "Startup service account" - change it from "system
account" to "This account" and put in your domain ID/pwd that has the
necessary access. If I'm not mistaken, it will require you to restart the sq
l
server service.
"Bob" wrote:

> Open up the Services (Start-ControlPanel-AdminTools-Services) and look for
> the Sql server services. Check what account they are running under (Log on
> as). If they are logging on as LocalSystem, that might be causing the
> problem. Note that you must NOT change the service account from the Servic
es
> directly. Go to Enterprise manager (assuming you are using Sql 2k), select
> the server and then, the properties. You will find a way to change it ther
e
> (I don't have access to a server right now, so cannot give you the exact t
ab
> to look at, but it should be fairly easy to find. Change it there (again,
> make sure you read up about the security concerns before you do that)
>