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 and log backups not deleting

We have had an issue for a long time. We have the
maintenance plan set to backup databases and log files
and to delete the old ones after 2 days. The backups run
fine and restores have been tested. We have never been
able to get the maintenance plan to automatically delete
the backups after the 2 days as it is supposed to do. We
are running Windows 2000 Advanced Server SP3 with SQL
2000 SP3. It continues to be a problem as we
consistently run out of disk space due to too many
backups stored on our local disk.We had the same problems.
Make sure you do not have RETAINDAYS or EXPIRES in the backup.
One thing I found was that a backup does not run for the same duration each
time,
so a backup that needs to delete older than 2 days sees the "2 day old
backup"
scenario as EXACTLY 24 hours or greater. The 2 days is based on the end
time.
So, if the 2 day old backup ran 15 minutes - 2:00am to 2:15am and
the new backup runs less time 2:00am to 2:13am then it is not 24 hours.
it is 47 hours 58 minutes. Voila NO DELETE. I worked around this by
DELETING Backups more than 36 HOURS old.
Hope this helps.
--
FrankM
You know you are in trouble when
the learning curve turns into a death spiral
"chuck" <ccrofford@.adelphia.net> wrote in message
news:082901c35dad$1a92a790$a501280a@.phx.gbl...
> We have had an issue for a long time. We have the
> maintenance plan set to backup databases and log files
> and to delete the old ones after 2 days. The backups run
> fine and restores have been tested. We have never been
> able to get the maintenance plan to automatically delete
> the backups after the 2 days as it is supposed to do. We
> are running Windows 2000 Advanced Server SP3 with SQL
> 2000 SP3. It continues to be a problem as we
> consistently run out of disk space due to too many
> backups stored on our local disk.|||In addition to Franks wonderful suggestions you may want to read this:
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly
SQL Server MVP
"chuck" <ccrofford@.adelphia.net> wrote in message
news:082901c35dad$1a92a790$a501280a@.phx.gbl...
> We have had an issue for a long time. We have the
> maintenance plan set to backup databases and log files
> and to delete the old ones after 2 days. The backups run
> fine and restores have been tested. We have never been
> able to get the maintenance plan to automatically delete
> the backups after the 2 days as it is supposed to do. We
> are running Windows 2000 Advanced Server SP3 with SQL
> 2000 SP3. It continues to be a problem as we
> consistently run out of disk space due to too many
> backups stored on our local disk.

data warehousing

We have some old data that we are not using, it's taking so much space for
no reason, we want to keep them seperately from the new data so we 're
planning to insert them to a seperate table and still make them accessible
by the customers whenever they want to access the arcieved data. Now I
really need your advice on this. What is the best way to do this? There 's 8
million data on this table, how do I implement this arcieving system?
Thank you.I would put it in a separate database... that way it could be backed up and
restored separately... It would also make it easier to move to another
server if (later) you decided to doso.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:eU3qGrAwDHA.3744@.TK2MSFTNGP11.phx.gbl...
> We have some old data that we are not using, it's taking so much space for
> no reason, we want to keep them seperately from the new data so we 're
> planning to insert them to a seperate table and still make them accessible
> by the customers whenever they want to access the arcieved data. Now I
> really need your advice on this. What is the best way to do this? There 's
8
> million data on this table, how do I implement this arcieving system?
> Thank you.
>

Data types Differences

I've recently installed a new server for an aplication we used at work. In the old server we had SQL Server 2000; for the new one we bought SQL Server 2005. Everything is ok till now, the aplication has worked ok; all ok except one thing.

I have a report in excel, all the format of the report is saved on one table, that table has fields bit, smallint, tinyint, varchar. Now if I try to make the report all the information is ok but the format it's messed. I think that maybe the application might be interpreting the information wrong. I don't have the sources of the application, so cannot debug it.

If I make the report but on the old server, the report appears ok.

Did something on these types (bit, tinyint,varchar,smallint) change from 2000 to 2005?

What can be happening?

Nothing about these types have changed. You might try running profiler against both servers, comparing the statements sent to the server, and then executing the queries from query analzyer/managment studio. Perhaps the results of the queries used to build the report are different in some respect.

|||

I'm not sure if you're saying that the data is in Excel and you're accessing it from SQL Server or the data is in SQL Server and you're accessing it in Excel.

If the data is in Excel and you're accessing it from SQL Server, you may have an issue based on the way you're doing it. If the data is in SQL Server and you're accessing it from Excel, check the compatibility flag on the database. Was this database upgraded from 2K to 2K5 or is it native 2K5?

Buck

|||

No the data is in SQL Server, the aplication access it and create a report on excel. I installed the SQL Server from zero in the new server but the database came from a SQL Server 2K.

Thank you both for reply.

|||

Then I recommend changing the compatibility setting on the database. Also - ensure that the system with the Excel report has the latest MDAC pack on it.

As an aside, I reccomend that you use a different client than Excel to access SQL Server. Microsoft Office applications can tend to escalate locks unacceptably.

Buck

|||

Mr. Halmans, I've run the profiler in both servers and seems to be the same, there are no differences in the statements or the results.

What about the binary type? Any changes? 'cause the report seems to select and update some binary field on one table.

Mr Woody, I'm not using Excel to extract information from the SQL, one application does the extraction and just use excel to present it, but all the information about the format of the excel cells (size, type, formula, etc) are saved in the data base. Right now the report appears with the right information but with no format at all.

Data type mismatch in criteria expression.

Hi,

I am santosh sending this mail regards database errorin vc++ 6.0 application . actually I am trying to convert old MS SQL server database to MS Access so what are the changes should I do in vc++ source code so it can work fine with MS Access. I am trying but it giving error on this statement as follows :-

strSQLString.MakeUpper();

ReturnCode = SQLExecDirect(m_hstmt,(UCHAR*)((LPCTSTR)strSQLString),SQL_NTS);

if (ReturnCode)

{

CDBException* pDBEx = new CDBException();

pDBEx->BuildErrorString(m_pDatabase,m_hstmt);

throw ( pDBEx);

return 0;

}

And this is sql statement witch is storing data in data type ofCStringstrSQLString;and the statement is as follows:-

strSQLString.Format("SELECT TOP %d MESSAGEID, MOBILENUMBER,MESSAGETEXT, SENDERID, RECDDATETIME, SENDDATETIME, SUBSCUSERID, SUBSCID, MESSAGESTATUS, RETURNCODE , PRIORITYLEVEL, VALIDITYDATETIME, MCL,CONFIRMREQD,DATAENCODING FROM SMSMESSAGES WHERE MESSAGESTATUS = 0", nRowsToFetch );

so I getting error likeby this in Debug mode throw (pDBEx);

Data type mismatch in criteria expression.

State:22005,Native:-3030,Origin:[Microsoft][ODBC Microsoft Access Driver]

And I am connecting throw database by DSN .

Please tell me the way for this is possible or not I am waiting for response .

Best regards,

S. Santosh Naidu

Is SMSMESSAGES.MESSAGESTATUS a varchar? If so, you'd want to rewrite your where clause to WHERE MESSAGESTATUS = '0' or similar. Send along your DDL for the table if this isn't the problem.

Hope this helps,
Josh Lindenmuth