SQLite Forum

Size of back using a sqlcmd script

Size of back using a sqlcmd script

(1) By Boby (bobyell) on 2021-02-12 21:56:37 [link] [source]

Hi , I have inherited maintaining a few stand alone hosts running 2014 SQLite instance. They are using a this script and the backup files have been doubling in size each week . They are now up into the 90GB range.

sqlcmd -E -S G037E1076serverxxxxx -Q "BACKUP DATABASE Cedex TO DISK='E:Cedex_WorkfolderDaily-Backup-G037E1076.BAK'"

The actual data is really much, much less than this. i have tried the "with init" switch.

Oddly if I go into the SSMS utility and create a full backup its only like 30GB.

I am severely under the gun to get this issue rectified ASAP as the storage NAS I archive the files too is running out of space. Any help would be greatly appreciated. If you need anymore information dont hesitate to ask.


(2) By Larry Brasfield (larrybr) on 2021-02-12 22:07:56 in reply to 1 [link] [source]

I find it difficult to see how your post relates to SQLite. [a] I could infer that the "script" you mention runs "sqlcmd", but how that relates to SQLite I cannot fathom.

[a. I see your assertion "hosts running 2014 SQLite instance." It seems to be a non-sequitur except for leading the unrelated text. ]

From the geometric size progression, (which obviously should be halted soon), I would say that your DB prior to backup must be including the previous backup. That's what you need to remedy rather than some script issue. Of course, I cannot guess as to why that DB state comes to be, since you have not shown anything related to that.

(3) By Keith Medcalf (kmedcalf) on 2021-02-12 22:11:55 in reply to 1 [link] [source]

That would be Microsoft SQL Server. Get out the American Express card and call Microsoft. Don't hold your breath waiting for a resolution though.

(4) By anonymous on 2021-02-13 06:56:20 in reply to 1 [link] [source]

sqlcmd -E -S G037E1076serverxxxxx -Q "BACKUP DATABASE Cedex TO DISK='E:\Cedex_Workfolder\Daily-Backup-G037E1076.BAK'"

That is SQL Server, one version or another. Looks like you might be using the Express version; if not; consider using SQL Agent to schedule the backup rather than using SQLCMD (the job history will give you an audit trail).

You might like to research DBCC SHRINK DATABASE/SHRINKFILE; starting here or here.

(5) By Boby (bobyell) on 2021-02-13 16:33:57 in reply to 4 [link] [source]

Yes it is SQL express 2014.

Like I said , I inherited this from a tech that just threw this together with no real research behind what he was doing . He just created that batch file and used task scheduler to execute at 3am everyday.

Then it was just dumped in my lap to take over archiving the .bak files to a secure location.

My "job" is to find out how to fix this ASAP as the files have out grown the allocated NAS storage space given for this system. The backup process is actually sneaker net. The backups are created on a USB thumb drive, every week I go and copy the files off to the nas manually to my laptop, then copy to the NAS.(another story LOL)

But Thank You very much for your input, greatly appreciated !

I will try the shrink database.

(6) By Larry Brasfield (larrybr) on 2021-02-13 17:26:24 in reply to 5 [link] [source]

Trying to shrink the DB is not going to work for more than maybe a week.

As you described it, the problem you face is a variant of Wheat and chessboard problem, which comes from an Indian legend.

Even if you could substitute grains of millet for grains of wheat on that chess board, you would never come near to meeting that geometrically grown demand.

If you were untruthful about the weekly doubling, you really need to take your problem to some SQL Server expert(s). This is not the place for shrinking tips on that product's DBs.

If the weekly doubling in fact occurs over many weeks, you need to fix that rather than figure out how to more efficiently store the ridiculously redundant data.

(7) By Boby (bobyell) on 2021-02-13 18:44:21 in reply to 6 [link] [source]

What I am hoping to do , is perform a monthly full back up and clear the database.(say on the last day of month), Then after that do full backups everyday until the last day again . Or do a full, then diffs everyday . Then clear the database end of month and repeat. Is this possible to script?

I'm sorry I'm not that knowledgeable about SQL, but I am doing as much research as I can before posting :-)

I guess what im not understanding is why the batch file creates a 60GB bak file and when I use the SSMS backup task , its only 30GB.

(8) By Larry Brasfield (larrybr) on 2021-02-13 19:34:10 in reply to 7 [link] [source]

As you have framed the problem, it is truly off-topic in this forum.

There probably are some SQL server users participating here, even some experts. But they are too polite (or busy) to clutter this forum with posts unrelated to its focus.

Given your inability or unwillingness to explore why (or if) you have periodic DB size doubling, trouble-shooting your problem is going to be a matter of looking at the schema, what "back up" specifically means, and whatever scripts are making that happen. It is all off-topic here.

(9) By anonymous on 2021-02-14 07:38:27 in reply to 4 [source]

You should consider moving i.e. re-submitting your question to stackoverflow

  1. When you are able to describe your issues more succinctly & following their guidelines for posting
  2. And are able to summarise what you have tried so far without ambiguity.

This will ensure enhance the likelihood of a more sympathetic and relevant response (this is an SQLite forum not a SQL Server or generic programming forum).