Back up Full Recovery Model Without Log Backups

Question

I have a database that I need to backup to URL, but without the log file.

 BACKUP DATABASE [MyDB] TO URL = @destination
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = '', stats=1

The above script includes the log file.

When I back up to disk (not what I need) with the following command, it does not however back up the Log file - which is what I want

BACKUP DATABASE MyDB
TO DISK = 'F:\SQLServer\MyDB.bak'
WITH COPY_ONLY;

Why would the top script include the log file, and the bottom script exclude the log file?

How are you confirming that bottom script does not includes log file. — Shanky, Feb 26 '19 at 07:58
I am only looking at file size, e.g. MDF 1GB, LDF 10GB. Top Script the bak file is 11GB, bottom script it is 1GB. — Peter PitLock, Feb 26 '19 at 08:49
The requirement is to reduce the bak file size because of BLOB costs involved - lead me to this conclusion that the bottom script backups = mdf size, and top script backup= mdf+ldf size — Peter PitLock, Feb 26 '19 at 08:51
For bottom script did you checked if backup compression is enabled ?. Go ahead and check sp_configure or your query if you are using compression when backup — Shanky, Feb 26 '19 at 09:25
If your log file is 10GB in size but barely any data in there it's not going to create a bak file to equal file size. When you restore it will create the same sized log file — Alen, Feb 26 '19 at 15:18

score 1 · Answer 1 · edited Feb 26 '19 at 10:52

When you take full database backup it will not take all the log file, and it seems you have some confusion about copyonly option.

If the issue you are facing is free desk space then you can refer to Is it possible to restore sql-server bak and shrink the log at the same time? if your transaction log is growing too fast then you can start troubleshooting and check this URLtoo

if the database is used for development environment then you can change it to simple mode this will truncate transaction log for you, but if you are on production site then do not do any changes to transaction log

see what will happen if you truncate logs

Back up Full Recovery Model Without Log Backups

1 Answers1