Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Backing up multiple databases and then compressing with WinRAR

Posted on 2011-04-22
8
Medium Priority
?
580 Views
Last Modified: 2013-11-26
I am trying to create a script that i would schedule for backing up multiple databases.

The backup side of things seems working fine but the compression with WinRAR is not, yet no error is returned when i execute the script. Could somebody help me?

The script is as below:

DECLARE @dbname NVARCHAR(1024)
DECLARE @backupPath VARCHAR(300)
DECLARE @rarPathandFile VARCHAR(300)
DECLARE @bakname VARCHAR(300)
DECLARE @filename VARCHAR(300)
DECLARE @cmd VARCHAR(300)
DECLARE @backupSetId as int
SET @backupPath = '\\Tally\bACKUPS\SQLBackup\op\'
EXECUTE xp_create_subdir @backupPath

EXEC sp_configure 'show advanced options', 1 RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE
DECLARE db_cursor CURSOR FOR
Select name From master.dbo.sysdatabases
Where name IN ('ABTS', 'BCV')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @bakname = @dbname + '_Fbackup_' + REPLACE(CONVERT(VARCHAR(20),
GETDATE(), 112) + CONVERT(VARCHAR(20), GETDATE(), 108),':','')
SET @filename = @backupPath + @bakname+'.bak'
BACKUP DATABASE @dbname TO DISK = @filename WITH NOFORMAT,
NOINIT, NAME = @bakname, SKIP, REWIND, NOUNLOAD, STATS = 10
select @backupSetId = position from msdb..backupset where database_name=@dbname
and backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name=@dbname )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database
@dbname not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = @filename WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND
SET @rarPathandFile = @backupPath + @bakname+'.rar'
SET @cmd = '"\\Tally\Program Files\WinRAR\rar.exe" a -m5' +@rarPathandFile + ' ' + @filename
EXEC master.dbo.xp_cmdshell @cmd
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE
EXEC sp_configure 'show advanced options', 0 RECONFIGURE
0
Comment
Question by:mamlaide
8 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35450570
Try run the winrar from command prompt and check whether it zips the backup files.
0
 

Author Comment

by:mamlaide
ID: 35450878
Hi racaesar,

I just run winrar from the command prompt and it zips the files
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35451392
The only way you are going to get that to work is if you resort to installing winrar on the server.  Most DBAs would frown upon that.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mamlaide
ID: 35451462
Winrar is installed on the Server. Am I missing something?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35451475
Is xp_cmdshell enabled?
0
 

Author Comment

by:mamlaide
ID: 35451596
I enable xp_cmdshell in my script:
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35451769
use this command to archive file
winrar a -afzip -df c:\test.zip c:\test.csv
0
 
LVL 15

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35452925
Your script have one little bug ;) There is a missing space in following line after -m5 parameter:
 
SET @cmd = '"\\Tally\Program Files\WinRAR\rar.exe" a -m5' +@rarPathandFile + ' ' + @filename

Open in new window

Try this code:
 
DECLARE @dbname NVARCHAR(1024)
DECLARE @backupPath VARCHAR(300)
DECLARE @rarPathandFile VARCHAR(300)
DECLARE @bakname VARCHAR(300)
DECLARE @filename VARCHAR(300)
DECLARE @cmd VARCHAR(300)
DECLARE @backupSetId as int
SET @backupPath = '\\Tally\bACKUPS\SQLBackup\op\'
EXECUTE xp_create_subdir @backupPath

EXEC sp_configure 'show advanced options', 1 RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE
DECLARE db_cursor CURSOR FOR
Select name From master.dbo.sysdatabases
Where name IN ('ABTS', 'BCV')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @bakname = @dbname + '_Fbackup_' + REPLACE(CONVERT(VARCHAR(20),
GETDATE(), 112) + CONVERT(VARCHAR(20), GETDATE(), 108),':','')
SET @filename = @backupPath + @bakname+'.bak'
BACKUP DATABASE @dbname TO DISK = @filename WITH NOFORMAT,
NOINIT, NAME = @bakname, SKIP, REWIND, NOUNLOAD, STATS = 10
select @backupSetId = position from msdb..backupset where database_name=@dbname
and backup_set_id=(select max(backup_set_id) from msdb..backupset where
database_name=@dbname )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database
@dbname not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = @filename WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND
SET @rarPathandFile = @backupPath + @bakname+'.rar'
SET @cmd = '"\\Tally\Program Files\WinRAR\rar.exe" a -m5 ' +@rarPathandFile + ' ' + @filename
EXEC master.dbo.xp_cmdshell @cmd
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE
EXEC sp_configure 'show advanced options', 0 RECONFIGURE

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question