0

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?

5
  • 1
    How are you confirming that bottom script does not includes log file.
    – Shanky
    Feb 26, 2019 at 7: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. Feb 26, 2019 at 8: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 Feb 26, 2019 at 8: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, 2019 at 9: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, 2019 at 15:18

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.