Tag Archives: backup

DTExec: The package execution returned DTSER_FAILURE (1)

Posted by cviorel on September 11, 2014 No comments | This post has 1,083 views.

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).

1	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

Posted by cviorel on August 29, 2014 No comments | This post has 645 views.

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

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.

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)

Posted by cviorel on June 17, 2011 1 comment | This post has 1,008 views.

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

#!/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

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"

done

echo -e "$green:: Done! $reset"

exit 0

Backup your MySQL databases automatically with AutoMySQLBackup

Posted by cviorel on May 6, 2008 No comments | This post has 755 views.

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.

cviorel.com

Tag Archives: backup

DTExec: The package execution returned DTSER_FAILURE (1)

T-SQL to generate backup and restore commands

Bash script to backup my configs (dotfiles)

Backup your MySQL databases automatically with AutoMySQLBackup

Categories

cviorel.com

Tag Archives: backup

DTExec: The package execution returned DTSER_FAILURE (1)

T-SQL to generate backup and restore commands

Bash script to backup my configs (dotfiles)

Backup your MySQL databases automatically with AutoMySQLBackup

Social

Categories

Tags