Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...

quote:

---

> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>

---

|||

quote:

---

>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>

---

Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.

quote:

---

>AK
>
>.
>

---

|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK

quote:

---

>--Original Message--
>I wouldn't figure the differential should ever be larger

---

than the full DB

quote:

---

>backup. Is the differential backup writing to the same

---

file each time? If so

quote:

---

>then you're probably adding to the file each time, look

---

up the WITH FORMAT

quote:

---

>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
in[QUOTE]
>
>.
>

---

|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK

quote:

---

>--Original Message--
>
in[QUOTE]
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>.
>

---

|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...[QUOTE]
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
>
> than the full DB
> file each time? If so
> up the WITH FORMAT
> message
> in

different behavior

I have an application (userx is the username) that creates tables on two
different databases. However when it creates it on the first database, it
creates it 'dbo.table1', whereas on the second database it creates the same
table named 'userx.table1'. We captured the script that creates the table
and it is "create table table1 ..."
Is there a setting that determinies what the name qualifier is by default.
We sat with the dba and we could not find any differences in the
configuration of the two databases.
Thanks,
Ericuserx is probably a member of the dbo role in the first database, but not th
e
second.
HTH
Vern Rabe
"Eric Stott" wrote:

> I have an application (userx is the username) that creates tables on two
> different databases. However when it creates it on the first database, it
> creates it 'dbo.table1', whereas on the second database it creates the sam
e
> table named 'userx.table1'. We captured the script that creates the table
> and it is "create table table1 ..."
> Is there a setting that determinies what the name qualifier is by default.
> We sat with the dba and we could not find any differences in the
> configuration of the two databases.
> Thanks,
> Eric
>
>|||If you want all tables to be owned by dbo, then your CREATE statement should
explicitly say so, e.g.,
CREATE TABLE dbo.TableName ...
HTH
Vern Rabe
"Eric Stott" wrote:

> I have an application (userx is the username) that creates tables on two
> different databases. However when it creates it on the first database, it
> creates it 'dbo.table1', whereas on the second database it creates the sam
e
> table named 'userx.table1'. We captured the script that creates the table
> and it is "create table table1 ..."
> Is there a setting that determinies what the name qualifier is by default.
> We sat with the dba and we could not find any differences in the
> configuration of the two databases.
> Thanks,
> Eric
>
>|||I am assuming you are running SQL 2000. Please always state your version in
the future.
Are you sure userx is the user name? Or is it the login name.
A login name gets you into SQL Server, but then when you use a database, you
login name is mapped to a username. Sometimes the names look the same,
sometimes they don't.
SELECT suser_sname() shows you your login name for the server
SELECT user_name() shows you your user name in the current database.
The only way to have a table owned by dbo without specifying the owner name
is to have your user name by dbo. Even if you are in the db_owner role, the
default will be that your real user name will be the owner of the table, and
you would have to ask to create a table dbo.table1.
My guess is that userx is a login name, and is the owner of one database but
not the other, or is aliased to the name dbo.
HTH
Kalen Delaney, SQL Server MVP
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:eyKJO8yjGHA.5108@.TK2MSFTNGP02.phx.gbl...
>I have an application (userx is the username) that creates tables on two
>different databases. However when it creates it on the first database, it
>creates it 'dbo.table1', whereas on the second database it creates the same
>table named 'userx.table1'. We captured the script that creates the table
>and it is "create table table1 ..."
> Is there a setting that determinies what the name qualifier is by default.
> We sat with the dba and we could not find any differences in the
> configuration of the two databases.
> Thanks,
> Eric
>|||Yes it is SQL 2000, and no, we cannot change the script, it is created by an
application that we do not own.
I will look into which is the owner and which one is aliased...
Eric
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e22wt$zjGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I am assuming you are running SQL 2000. Please always state your version in
>the future.
> Are you sure userx is the user name? Or is it the login name.
> A login name gets you into SQL Server, but then when you use a database,
> you login name is mapped to a username. Sometimes the names look the same,
> sometimes they don't.
> SELECT suser_sname() shows you your login name for the server
> SELECT user_name() shows you your user name in the current database.
> The only way to have a table owned by dbo without specifying the owner
> name is to have your user name by dbo. Even if you are in the db_owner
> role, the default will be that your real user name will be the owner of
> the table, and you would have to ask to create a table dbo.table1.
> My guess is that userx is a login name, and is the owner of one database
> but not the other, or is aliased to the name dbo.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eyKJO8yjGHA.5108@.TK2MSFTNGP02.phx.gbl...
>

Different access files DTS to mssql

We use a program that creates a access database for each of our payperiods. Its a pain to get a full history of a person from each one. I want to setup a way I can combine all of the access files into one table in mssql. I have a DTS I created into a .bas file. My plan is to make the vb.net code get a directory of the files, then run a function (the .bas file) on each directory appending the access table to the mssql table. The struckture of the access files are that each file is in its own directory, like below:

\\servername\TA\company\07-25-04\Trans.mdb
\\servername\TA\company\08-08-04\Trans.mdb
\\servername\TA\company\08-22-04\Trans.mdb

The table is called "Transactions" and they are the same in each mdb file in terms of fields. Every two weeks there is a new directory that is added to the company directory. So I can either dump all and append all, or only append new data and only use the dump/append when there is a problem.

I am just not sure how to do that. Any ideas?Since the structure of each of the .MDB files is the same, it sounds to me like a DTS package using a Global Variable would be a good solution. The Global Variable would be used for the name of the Access file to import. Your VB.NET code would loop through the files to process, feeding DTS the name of each file one by one via a Global Variable.

You might consider keep a SQL table of the files you have processed so your VB.NET program can check against that to see if it has already processed and given file.

Terri|||Sounds good....... how do I do that?|||Okay, I have some code in vb.net to get each date from a startdate. I made a DTS and exported it into a VBS file. How doI get that code to run in my VB.net solution? The vbs file has amain() and some other subs. Just not sure how to referenceit. (I can post the vbs script if needed)