I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance.
So for a while I've been having my SSIS overnight process run a Execute SQL Task to perform the backups - basically running
master.dbo.xp_create_subdir to ensure the destination folders exist, and then
BACKUP DATABASE [DbName] TO DISK = 'G:\Backups\DbName\DbName.bak' WITH INIT.
Whenever that task failed, the rest of the process would abort and I'd get a notification, and come in the next morning to notice the drive for transaction logs was filled to capacity, and so I'd manually truncate them and move on... until the story repeated itself and the transaction logs outgrew the available disk space again.
The "manual truncate" script looks like this:
use Staging; alter database Staging set recovery simple alter database Staging set recovery full dbcc shrinkfile ('Staging_log', 0, truncateonly); go
So I'm growing tired of this, and I decided to try and do things properly instead, and follow the steps here and create an actual maintenance plan:
Thing is, I've never done this before, so I have a few questions:
- Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?
- Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?
- The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?
- Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of
G:\Backups. Does that make sense?
- Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?
Sorry if this is too many questions for one post, if needed I'll edit and ask multiple questions instead - I think they're all tightly related though.