Abnormally Large Backup Files

Hi All,
We seem to have an issue with our backup routine for one of the databases.
On a daily basis, we have one of our databases back up to disk. However, I
noticed that our backup file is huge (39GB!). I checked the database, and
it says it is only using 586MB of space. I also checked the transaction
log, and it says it is using 100MB of space.
We have a weekly backup of the database, and it only takes up 921MB, so I
can't see why the daily backup takes up so much room. Does it append to the
existing backup? If so, how can I selectively remove old backups from it?
I looked at the backup routine, and here's the SQL that is run:
BACKUP DATABASE [NAPROD] TO DISK = N'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\NAPROD daily production backup.BAK' WITH NOINIT ,
NOUNLOAD , NAME = N'NAPROD daily production backup ', NOSKIP , STATS = 10, DESCRIPTION = N'Navision production database backup ', NOFORMAT
Looking at it, I can't see any problem. I'd really like to get this file
down to something more manageable, as it is killing our backup program.
Any ideas would be appreciated! :)
Thanks!
Brian.Change the NOINIT to INIT.
--
Andrew J. Kelly
SQL Server MVP
"Brian Piotrowski" <bpiotrowski@.simcoeparts.com> wrote in message
news:%23xbCmNvRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> We seem to have an issue with our backup routine for one of the databases.
> On a daily basis, we have one of our databases back up to disk. However,
I
> noticed that our backup file is huge (39GB!). I checked the database, and
> it says it is only using 586MB of space. I also checked the transaction
> log, and it says it is using 100MB of space.
> We have a weekly backup of the database, and it only takes up 921MB, so I
> can't see why the daily backup takes up so much room. Does it append to
the
> existing backup? If so, how can I selectively remove old backups from it?
> I looked at the backup routine, and here's the SQL that is run:
> BACKUP DATABASE [NAPROD] TO DISK = N'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\NAPROD daily production backup.BAK' WITH NOINIT ,
> NOUNLOAD , NAME = N'NAPROD daily production backup ', NOSKIP , STATS => 10, DESCRIPTION = N'Navision production database backup ', NOFORMAT
> Looking at it, I can't see any problem. I'd really like to get this file
> down to something more manageable, as it is killing our backup program.
> Any ideas would be appreciated! :)
> Thanks!
> Brian.
>

a way to export data from table to flat file

Hi there!
I know bulk insert for importing data into tables and bcp for both
importing, exporting data between tables..from flat files...ok..
But is there any way to export data using a SQL statement and a format
file (need to export in kind of csv) just like bcp ? a kind of *bulk
output*...:)
any idea'
thanks a lot
++
Vincelook at DTS...|||Vince
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\FolderName;','select * from Text1.txt')
"Vince .>" <vincent@.<remove> wrote in message
news:ro7751hjpvc9l0lks58pds2dda1prok8lu@.
4ax.com...
> Hi there!
> I know bulk insert for importing data into tables and bcp for both
> importing, exporting data between tables..from flat files...ok..
> But is there any way to export data using a SQL statement and a format
> file (need to export in kind of csv) just like bcp ? a kind of *bulk
> output*...:)
> any idea'
> thanks a lot
> ++
> Vince
>

a toughy!

Hey all,
I have a bunch of data files that currently come to my shop in an EBCDIC format from IBM midrange machines and I currently use a Borland application to convert them to ASCII. I would like to ditch the Borland piece and do everything with a DTS package and get the data into my SQL2000 server...but how do I convert the EBCDIC to ASCII?

Please help!there are more ways and everything depends on how often you do it, how big files you have, how automated process you want...

I can tell you that the best is Informatica PowerCenter... for $200,000

I'm just kidding :)

you can use command-line conversion tool or ActiveX or your own function.

You can have your own function and use it in FIELD to FIELD mapping in DTS. The problem is, that it will take lot of time and for larger TXT files it is not the best solution.... Better would be to convert text file from EBCDIC to ASCII. There are some free command line programs ActiveX components (text file conversions). Check Google.com

I'd start with small function and you will see if it is really slooooow.

http://www.crystalsoftware.com.au/textpipe.html
http://www.guysoftware.com/parserat.htm

jiri|||Thanks, but I was really hoping to get my hands on some source so I could see how this works. I am not to keen about buying a 3rd party tool from a small developer because it will lead to a support nightmare 3 years down the road.

Has anyone out there tackled this ground up? I would love to see some source, even if its slow...heck I'd even be willing to taske a crack at some Perl.
-d

Originally posted by playernovis
there are more ways and everything depends on how often you do it, how big files you have, how automated process you want...

I can tell you that the best is Informatica PowerCenter... for $200,000

I'm just kidding :)

you can use command-line conversion tool or ActiveX or your own function.

You can have your own function and use it in FIELD to FIELD mapping in DTS. The problem is, that it will take lot of time and for larger TXT files it is not the best solution.... Better would be to convert text file from EBCDIC to ASCII. There are some free command line programs ActiveX components (text file conversions). Check Google.com

I'd start with small function and you will see if it is really slooooow.

http://www.crystalsoftware.com.au/textpipe.html
http://www.guysoftware.com/parserat.htm

jiri

a related question - how to convert from sqlexpress to sql server 2005

how does one convert from sqlexpress (mdf files) to sql server 2005 and vise-versa, ie from SQL Server 2005 to SQLExpress mdf files?Attach & detach is the easiest way to go :-)|||

What if you don't have the ldf for the SQLExpress database?

|||Look in Books Online for the usage of [sp_attach_single_file_db]

a related question - how to convert from sqlexpress to sql server 2005

how does one convert from sqlexpress (mdf files) to sql server 2005 and vise-versa, ie from SQL Server 2005 to SQLExpress mdf files?Attach & detach is the easiest way to go :-)|||

What if you don't have the ldf for the SQLExpress database?

|||Look in Books Online for the usage of [sp_attach_single_file_db]

A Matter of Style

When creating SQL scripts, what methods do you use when working with them?

More specifically:
How do you split the SQL across files? For example, do you put things relating to each table into separate files (table creation, indexes for the table, etc.), or do you group similar things into the same file (all indexes in one file, all table creation in another file, etc.)? Maybe you just have ONE BIG file that contains all the code?

Thanks for your input!Hello,

in our projects we split the intitscripts per object into separate file.

f.e. one for sequence
one for tables
and so on

and a upper script that connect to the database with thw correct user and calls the single files.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
and a upper script that connect to the database with thw correct user and calls the single files.


Ahh... this sounds like a good solution! How exactly do you "call" the individual files? Could you post an example?

Thanks!|||Hello,

here is a small example of a batch like we use it. Start the main.sql
in sqlplus. The other script will be called from the main.sql

Hope that help ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hope that help ?


Thanks! I'll have a look... I appreciate you taking the time to post.

A long query problem

Hi all,
I currently build a text processing database for my application.
The database is to store all the plain text files submitted by customers. The plain text files will be processed before it store in the sql server. Thus I have 3 major tables Document, Term and TermDocument.
I encounter a problem when I did a query. For example when I performed the following query

SELECT T.TermName, S.* FROM Weight AS S INNER JOIN Term AS T ON S.TermID = T.TermID WHERE T.TermName IN ('electronic','commerce','consists', ....)

The system told me that "No enough storage is available to complete the operation". The terms in "IN(....)" could have up 3k entries. I know this is huge, but I have no choice but to retrieve all of them in a single query.

Is there anyway to solve the problem or perform similar operation in alternative way.

Maybe it would be easier to use reverse logic?

If possible you could reverse the IN to a NOT IN any maybe the list is shorter then (assumption).

You could also insert the 3000 terms in a temporary table and join with that table instead of using the IN clause.

Anyway, I'm not too fond of queries with 3000 entries in the WHERE clause to be honest.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I agree that its best to go down the table route, either with a temp table or perhaps a table valued UDF.

There are some good examples of the split function referenced in this thread which may suit your needs as it will enable you to convert a string of terms in to a table.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1847071&SiteID=1

Hope that makes sense.

A good SSIS book?

I am new to SQL2005 and have been given the task of writing some SSIS packages to import some CSV files.

I need to cleans the data as it is imported from my CSV files before it reaches my SQL DB.

I am currently Googling the internet to discover how to do this.

Can anyone recommend a good SSIS book?

I am a C# developer, so a book that has lots of SSIS C# examples would be good.

Any help appreciated.

Regards,

Paul.

SSIS(SQL Server Integration Services) is a full ETL(extraction transformation and loading) tool so moving CVS is very easy task I don't think you need a book to move CVS files to SQL Server because the only issue with CVS files is SQL Server sees null values due to the nature of the file. The solution is to move the file to a temp table before destination because if you move it directly to a table with primary key your package will be rejected. I have found you a two part article by Microsoft and the site run by first DTS and now SSIS experts, this is the calculus end of the relational model, the data is cleaned of algebra and moved to read only tables for Dimension modeling and Prediction modeling. I will not recommend a book because most things on the calculus end of SQL Server is new so you need to browse some books at your local book store and choose the one that meets your needs I found three books on the service. Post again if you still have questions. Hope this helps.

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

http://www.sqlis.com/

|||

I have ordered a SSIS book from Amazon - Looks like I'm a steep learning curve.

Thanks for the help.

Regards,

Paul.

|||

wadep:

I have ordered a SSIS book from Amazon - Looks like I'm a steep learning curve.

Thanks for the help.

Regards,

Paul.

I am glad I could help, just remember to start with the wizard and use code as needed.