Tag Archives: backup

DTExec: The package execution returned DTSER_FAILURE (1)

What to do when a SQL backup job created with SQL server Maintenance Plans fails with this error?

DTExec: The package execution returned DTSER_FAILURE (1).

Investigate! No useful information in the logs.
Previously that day I dropped a few unneeded databases so maybe the Maintenance Plan was still referencing them. Everything looked good when the Maintenance Plan was opened in SSMS (it was configured to backup “SELECTED” databases), I could not find any reference to the deleted databases. Under the hood, the Maintenance Plan was still trying to backup those deleted databases because when I clicked OK and then hit Save, that Maintenance Plan was refreshed and everything worked just fine.

So always try to find the root cause.

T-SQL to generate backup and restore commands

Hi guys,

Since my day to day job is a DBA, I figured I will share stuff that others might find useful.
For the first time I will share a small script which will help you generate T-SQL commands in order to script the backup/restore process of your SQL Server.

This was tested on Microsoft SQL Server 2012 (SP1) – Express Edition (64-bit). Feel free to send your comments.

/*
 
This script will list all the databases on the server and generate backup and restore commands for each database, based on the recovery model.
The script will also determine if your SQL Server version supports compressed backups and will generate the commands accordingly.
 
(C) viorel.ciucu@gmail.com, 28 Aug 2014
 
*/
 
DECLARE @backup_location VARCHAR(254) = 'C:\TEMP\' -- full, UNC path
DECLARE @compresion VARCHAR(13) = ''
DECLARE @a nvarchar(max) = ''
 
DECLARE @CR AS CHAR(1)    -- Carriage Return (CR)
DECLARE @LF AS CHAR(1)    -- Line Feed (LF)
DECLARE @CrLf AS CHAR(2)  -- Carriage Return / Line Feed
 
SET @CR = CHAR(10)
SET @LF = CHAR(13)
SET @CrLf = @CR + @LF
 
IF (SERVERPROPERTY('EngineEdition') = 3)
	BEGIN
		PRINT 'Enterprise Edition detected, backup compression IS SUPPORTED!';
		SET @compresion = ' ,COMPRESSION';
	END
ELSE
	BEGIN
		PRINT 'Enterprise Edition NOT detected, backup compression IS NOT SUPPORTED!';
		SET @compresion = '';
	END
 
 
SELECT @@SERVERNAME as ServerName, database_id, name, --recovery_model, recovery_model_desc,
--CONCAT('ALTER DATABASE  [', name, '] SET READ_ONLY') AS read_only_command,
CONCAT('BACKUP DATABASE [', name, '] TO DISK = N''', @backup_location+name, '.bak''', ' WITH NOFORMAT, NOINIT', @compresion, ', STATS = 10;') +  @CR +
CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED
THEN
	CONCAT('BACKUP LOG  [', name, '] TO DISK = N''', @backup_location+name, '.trn'' WITH NOFORMAT, NOINIT, STATS = 10') + @LF
ELSE
	''
END AS backup_command, 
--CONCAT('ALTER DATABASE  [', name, '] SET  READ_WRITE WITH NO_WAIT') AS read_write_command,
CONCAT('RESTORE DATABASE [', name, '] FROM DISK = N''', @backup_location+name, '.bak''', ' WITH REPLACE, NORECOVERY;') +  @CR +
CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED
THEN
	CONCAT('RESTORE LOG [', name, '] FROM DISK = N''', @backup_location+name, '.trn''', ' WITH NORECOVERY;') + @LF +
	CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF
ELSE
	CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF
END AS restore_command
FROM sys.databases WITH (NOLOCK) WHERE database_id > 4
ORDER BY name

Bash script to backup my configs (dotfiles)

I needed some script to take care of my config files and put them in a single directory. So here it is.

#!/bin/bash
 
# Display All Hidden Dot Files In a Directory
# ls -a | egrep "^\." > backup_dotfiles.sh
 
DESTINATION="/storage/dotfiles"  # do not use a trailing slash (/)
 
# Colors
blue="\e[0;34m"
green="\e[1;32m"
red="\e[0;31m"
bold="\e[1;30m"
reset="\e[0m"
 
# file list (use trailing slash for directories)
FILES="
.bash_aliases
.bash_logout
.bash_profile
.bashrc
.colours/
.config/openbox/
.config/terminator/
.config/tint2/
.config/zathura/
.devilspie/
.fehbg
.fonts.conf
.gtk-bookmarks
.gtkrc-2.0
.gtkrc-2.0.mine
.inputrc
.mplayer/
.nanorc
.rtorrent.rc
.screenrc
.synergy.conf
.vim/colors/
.vimrc
.xbindkeysrc
.Xdefaults
.xinitrc
.xmod
.Xmodmap
.xsession
.xxkb/
.xxkbrc
"
for file in $FILES
do
   if [ -d $file ]; then
      mkdir -p $DESTINATION/$file
      cp -f $HOME/$file* $DESTINATION/$file
   elif [ -f $file ]; then
      cp -f $HOME/$file $DESTINATION
   else
      echo -e "$red:: $file is not a file/directory! $reset"
   fi     
done
 
echo -e "$green:: Done! $reset"
exit 0

Backup your MySQL databases automatically with AutoMySQLBackup

If you are a MySQL user and you want to have a disaster recovery solution, or even as a simple backup, you can write small shell scripts based on mysqldump tool.

For this, you need AutoMySQLBackup. It doesn’t have any real requirements (mysqldump of course is needed – in any mysql client package – and gzip or bzip2 to compress the resulting file) and has all the features I was looking for in such a script.

AutoMySQLBackup has all the features I needed: it can backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.). It has also some other features (check the project homepage for full details), that I am not using myself (like email logs for example), but other peoples might find interesting.

All you have to do is to modify a few things:

# Username to access the MySQL server e.g. dbuser
USERNAME=dbuser
# Username to access the MySQL server e.g. password
PASSWORD=password
# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"
# Backup directory location e.g /backups
BACKUPDIR="/var/backup/mysql"
# Mail setup
MAILCONTENT="quiet"

You can run the script manually but you will want to enable it in cron
and run daily.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close