salut, am si eu nevoie de ajutorul vostru cat :(

am procedura care creeaza un backup astfel

CREATE     proc _BackUp
 @param bit Out
as
set @param=0

declare
 @now    nvarchar(100),
 @luna    nvarchar(10),
 @LogicalBackup   nvarchar(300),
 @CaleBackup0   nvarchar(300),
 @CaleBackup   nvarchar(300),
 @strSQL   nvarchar(4000)

declare
 @dataRef datetime,
 @month int,
 @year int

set @dataRef=getdate()
set @month = datepart(mm,@dataRef)
set @year = datepart(yyyy,@dataRef)

declare @i int
select
 @luna=cast(month(GETDATE()) as nvarchar(50)),
 @i=cast(@luna as int)-1

if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

-- Create a logical backup device for the full database backup
set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FolderTEST\TEST_'+@now
set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup='TEST_'+@now

print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
-- Back up the full database
exec('BACKUP DATABASE TEST TO '+@LogicalBackup)

set @param=1

return @param

GO

problema e ca vreau sa includ in procedura si sa-mi creeze folderul in care pun backupul daca el nu exista in cazul meu "FolderTEST" pentru ca imi da eroare daca nu exista folderul in care vrea sa-si puna backupul facut de procedura

Multumesc mult.

de exemplu asta imi creaza un file......cum pot modifica sa-mi creeze un folder?

create proc _NewFolder
 @FolderTEST nvarchar(25)
as

DECLARE
 @cmd  sysname,
 @destinatie nvarchar(50)

set @destinatie='D:\'+@FolderTEST
SET @cmd = 'echo ' + ' > '+@destinatie
EXEC master..xp_cmdshell @cmd

GO

Incearca de exemplu

EXEC xp_cmdshell 'mkdir C:\Backup'

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ntcmds.mspx

 

e ok...multumesc mult......imi creeaza dar daca vreau de exemplu in loc de

EXEC master..xp_cmdshell 'mkdir D:\TEST'

sa am

EXEC master..xp_cmdshell 'mkdir D:\Program Files\MSSQL\BackUp\TEST'

adica folderul test sa mi-l faca in calea aia...

multumesc

 

Incluzi calea între ghilimele:

EXEC master..xp_cmdshell 'mkdir "D:\Program Files\MSSQL\BackUp\TEST"'

 

---

Cătălin D.

multumesc.....

am aflat ca trebuie sa mai includ ceva......

procedura respectiva (de mai sus) trebuie sa faca backup pentru toate bazele de pe server mai putin cele de system

as vrea daca puteti sa ma ajutati sa modific procedura astefl incat sa citeasca ce baze de date exista pe server....sa scoata numele lor....sa creeze cate un folder pt fiecare si sa sa faca backup in folderul respectiv al bazei de date......se poate face cu un cursor?

multumesc

am atasat procedura asa cum am adus-o pana in faza asta:

create    proc _BackUp
 @param bit Out
as
set @param=0

declare
 @now    nvarchar(100),
 @luna    nvarchar(10),
 @LogicalBackup   nvarchar(300),
 @CaleBackup0   nvarchar(300),
 @CaleBackup   nvarchar(300),
 @strSQL   nvarchar(4000)

declare
 @dataRef datetime,
 @month int,
 @year int

set @dataRef=getdate()
set @month = datepart(mm,@dataRef)
set @year = datepart(yyyy,@dataRef)

declare @i int
select
 @luna=cast(month(GETDATE()) as nvarchar(50)),
 @i=cast(@luna as int)-1

if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

--EXEC master..xp_cmdshell 'mkdir D:\TEST'
EXEC master..xp_cmdshell 'mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST"'

-- Create a logical backup device for the full database backup
set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST\TEST_'+@now
set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup='TEST_'+@now

print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
-- Back up the full database
exec('BACKUP DATABASE TEST TO '+@LogicalBackup)

set @param=1

return @param

GO

 

katalyn:

Incluzi calea între ghilimele:

EXEC master..xp_cmdshell 'mkdir "D:\Program Files\MSSQL\BackUp\TEST"'

 

daca vreau sa introduc paramatru @DBName la procedura cum va arata acest mkdir ...ca el sa creeze un folder cu denumirea paramtrului @DBName

asa nu vrea:

EXEC master..xp_cmdshell 'mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" + @DBName '

nu inteleg unde mai trebuies puse ghilimele sau cum trebuie inchis acest mkdir

sa rezolvat am facut asa....:)

set  @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"'
EXEC master..xp_cmdshell @strSQL

acum am alta problema (....cum pot din cod SQL sa lansez "C:\Program Files\WinRAR\rar.exe" cu parametrii 'a -df -ep -s -d4096 -rr -m5 -ri1' care sa-mi arhiveze respectivul backup pe care l-am creat si dupa arhivare sa-l stearga astfel incat sa ramana doar arhiva in folderul respectiv

interesant...ah :)

atasez procedura asa cum arata pana acum

create proc _BackUp_DBName
 @DBName nvarchar(50),
 @param bit Out

as
set @param=0

declare
 @now    nvarchar(100),
 @luna    nvarchar(10),
 @LogicalBackup   nvarchar(300),
 @CaleBackup0   nvarchar(300),
 @CaleBackup   nvarchar(300),
 @strSQL   nvarchar(4000)

declare
 @dataRef datetime,
 @month int,
 @year int

set @dataRef=getdate()
set @month = datepart(mm,@dataRef)
set @year = datepart(yyyy,@dataRef)

select @luna=cast(month(GETDATE()) as nvarchar(50))

if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

set  @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"'
EXEC master..xp_cmdshell @strSQL

-- Create a logical backup device for the full database backup
set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST\'+@DBName + '_'+ @now
set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup= @DBName + '_'+ @now

print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
-- Back up the full database
exec('BACKUP DATABASE ' + @DBName + ' TO ' + @LogicalBackup)

set @param=1

return @param
GO

Eu zic ca e mai bine sa incerci "manevra" asta dintr-un "batch file", de exemplu copiaza linia de mai jos intr-un "notepad" si salveaza sub numele de "addtoarchive.cmd" (de exemplu)

WinRAR a archive *.bak

 Poti rula addtoarchive.cmd prin "double click" sau prin adaugare la un "scheduled task".

* atentie la directorul unde se afla WinRAR.exe. In exemplul meu (foarte simplu)  WinRAR.exe este situat in directorul curent.

am modificat procedura astfel

CREATE     proc _BackUp_DBName_WinRAR
 @DBName nvarchar(50),
 @param bit Out

as
set @param=0

declare
 @now    nvarchar(100),
 @luna    nvarchar(10),
 @LogicalBackup   nvarchar(300),
 @CaleBackup0   nvarchar(300),
 @CaleBackup   nvarchar(300),
 @strSQL   nvarchar(4000)

declare
 @dataRef datetime,
 @month int,
 @year int

set @dataRef=getdate()
set @month = datepart(mm,@dataRef)
set @year = datepart(yyyy,@dataRef)

select @luna=cast(month(GETDATE()) as nvarchar(50))

if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

--EXEC master..xp_cmdshell 'mkdir D:\TEST'
--EXEC master..xp_cmdshell 'mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" + @DBName" '
set  @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"'
EXEC master..xp_cmdshell @strSQL
-- Create a logical backup device for the full database backup
set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST\'+@DBName + '_'+ @now
set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup= @DBName + '_'+ @now

print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
-- Back up the full database
exec('BACKUP DATABASE ' + @DBName + ' TO ' + @LogicalBackup)

DECLARE  @LocalBackUPPath   nvarchar(255),
  @LocalBackUPDrive  nvarchar(128), 
  @TemplateFileName  nvarchar(255),
  @TemplateCharSeparator  nvarchar(128),
  @tmpSQL    varchar(8000),
  @tmpServer   varchar(8000),
  @PackerFileName   nvarchar(255),
  @PackFileName   nvarchar(255),
  @SetPasswordAutomatic  bit,
  @SetPasswordTo    nvarchar(64),
  @PackOptions   nvarchar(255),
  @FileExtention   nvarchar(32)

SET @PackerFileName = '"C:\Program Files\WinRAR\rar.exe"'
SET @PackOptions = 'a -df -ep -s -d4096 -rr -m5 -ri1'
SET @LocalBackupDrive = 'D:'
SET @LocalBackUPPath = 'Program Files\Microsoft SQL Server\MSSQL\BACKUP'
SET @PackFileName=@LogicalBackup + '.BAK' --+ @FileExtention

SET @tmpServer='master.dbo.xp_cmdshell'
--SET @tmpSQL='"C:\Program Files\WinRAR\rar.exe"' + ' ' + 'a -df -ep -s -d4096 -rr -m5 -ri1'  + ' ' + 'D:' + '\' + 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP' + '\' + @PackFileName + ' ' + @LocalBackupDrive + '\' + @LocalBackUPPath + '\' + @BAKFileName
SET @tmpSQL=@PackerFileName + ' ' + @PackOptions + ' ' + @LocalBackupDrive + '\' + @LocalBackUPPath + '\' + @PackFileName -- + ' ' + @LocalBackupDrive + '\' + @LocalBackUPPath + '\' + @BAKFileName
EXEC @tmpServer @tmpSQL

 

set @param=1

return @param

 

GO

si primesc cu debug in sql urmatoarea eroare....nu-mi dau seama de la ce poate fi....ce e gresit

 

NULL
RAR 3.00    Copyright (c) 1993-2002 Eugene Roshal    14 May 2002
Shareware version         Type RAR -? for help
NULL
Evaluation copy. Please register.
NULL
Cannot open Files\Microsoft
Cannot open SQL
Cannot open Server\MSSQL\BACKUP\TEST_6062007_162215.BAK
Updating solid archive D:\Program.rar
NULL
Repacking archived files:     1    2
WARNING: No files
NULL

 

aveti vreo idee ?

o sa ma gandesc si la varianta ta Diana....dar as vrea sa rezolv asa totusi ...daca nu reusesc ;)

se pare ca nu imi lanseaza "C:\Program Files\WinRAR\WinRAR.exe" ....nu il recunoaste.....dar nu-mi dau seama de ce :(

Il recunoaste pe rar dar ii dai un parametru la winrar care nu e bun ... 
Incearca-le inainte intr-un bat sau ceva, sau apeleaza direct bat cu parametru calea...
A atentie ca bat sa se inchida ca daca nu ramine in memorie pina la reset, cred ca echo off si @cls la sfirsit daca mergi pe calea bat caut si iti zic sigur.

 

da...intradevar chiar  nu sunt sigur de parametrii pe care-i dau....dar nici nu inteleg cum as putea sa-i verific.....parametrii pe care-i dau acum i-am luat si eu dintr-o alta procedura care facea ceva asemanator......nu am inteles principiul deci nici nu stiu care sunt cei corecti :(....o iau si eu pe incercate....daca are cineva vreo idee sunt mai mult decat fericit sa o primesc.....

multumesc

 P.S. daca vrea cineva sa incerce sa-i dea de cap...mai jos am pus procedura asa cum arata ea acum......

create  proc _BackUp_DBName_WinRAR
 @DBName nvarchar(50),
 @param bit Out

as
set @param=0

declare
 @now    nvarchar(100),
 @luna    nvarchar(10),
 @LogicalBackup   nvarchar(300),
 @CaleBackup0   nvarchar(300),
 @CaleBackup   nvarchar(300),
 @strSQL   nvarchar(4000)

declare
 @dataRef datetime,
 @month int,
 @year int

set @dataRef=getdate()
set @month = datepart(mm,@dataRef)
set @year = datepart(yyyy,@dataRef)

select @luna=cast(month(GETDATE()) as nvarchar(50))

if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))
set  @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"'
EXEC master..xp_cmdshell @strSQL
-- Create a logical backup device for the full database backup
set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST\'+@DBName + '_'+ @now
set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup= @DBName + '_'+ @now

print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
-- Back up the full database
exec('BACKUP DATABASE ' + @DBName + ' TO ' + @LogicalBackup)

DECLARE  @LocalBackUPPath   nvarchar(255),
  @LocalBackUPDrive  nvarchar(128), 
  @TemplateFileName  nvarchar(255),
  @TemplateCharSeparator  nvarchar(128),
  @tmpSQL    varchar(8000),
  @tmpServer   varchar(8000),
  @PackerFileName   nvarchar(255),
  @PackFileName   nvarchar(255),
  @SetPasswordAutomatic  bit,
  @SetPasswordTo    nvarchar(64),
  @PackOptions   nvarchar(255),
  @FileExtention   nvarchar(32)

SET @PackerFileName = '"C:\Program Files\WinRAR\WinRAR.exe"'
SET @PackOptions = 'a -s' -- -df -ep -d4096 -rr -ri1 -m5
SET @LocalBackupDrive = 'D:'
SET @LocalBackUPPath = 'Program Files\Microsoft SQL Server\MSSQL\BACKUP'
SET @PackFileName=@LogicalBackup + '.BAK' --+ @FileExtention

SET @tmpServer='master.dbo.xp_cmdshell'
SET @tmpSQL=@PackerFileName + ' ' + @PackOptions + ' ' + @LocalBackupDrive + '\' + @LocalBackUPPath + '\' + @PackFileName + ' ' + @LocalBackupDrive + '\' + @LocalBackUPPath + '\' + @LogicalBackup
EXEC master..xp_cmdshell @tmpSQL

set @param=1

return @param

GO

 

asa arata acel @tmpSQL care se formeaza si care se pare ca nu e ok.....

'"C:\Program Files\WinRAR\WinRAR.exe" a -s D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST_7062007_94649.BAK D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST_7062007_94649'

am reusit sa arhivez fisierul....singura problema e ca nu-mi pune extensia .RAR.....il arhiveaza fara extensie si nu pot sa-l dezarhivez....dar oricum am inaintat ceva :)....asta e procedura corecta...cat de cat :)

create      proc _BackUp_DBName_WinRAR_Test
 @DBName nvarchar(50),
 @param bit Out

as
set @param=0

declare
 @now    nvarchar(100),
 @luna    nvarchar(10),
 @LogicalBackup   nvarchar(300),
 @CaleBackup0   nvarchar(300),
 @CaleBackup   nvarchar(300),
 @strSQL   nvarchar(4000)

declare
 @dataRef datetime,
 @month int,
 @year int

set @dataRef=getdate()
set @month = datepart(mm,@dataRef)
set @year = datepart(yyyy,@dataRef)

select @luna=cast(month(GETDATE()) as nvarchar(50))

if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

set  @strSQL='mkdir "D:\' + @DBName + '"'

EXEC master..xp_cmdshell @strSQL
-- Create a logical backup device for the full database backup
set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))

set @CaleBackup0='D:\'+ @DBName + '\' + @DBName + '_'+ @now

set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup= @DBName + '_'+ @now

print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
-- Back up the full database
exec('BACKUP DATABASE ' + @DBName + ' TO ' + @LogicalBackup)

DECLARE  @LocalBackUPPath   nvarchar(255),
  @LocalBackUPDrive  nvarchar(128), 
  @TemplateFileName  nvarchar(255),
  @TemplateCharSeparator  nvarchar(128),
  @tmpSQL    varchar(8000),
  @tmpServer   varchar(8000),
  @PackerFileName   nvarchar(255),
  @PackFileName   nvarchar(255),
  @SetPasswordAutomatic  bit,
  @SetPasswordTo    nvarchar(64),
  @PackOptions   nvarchar(255),
  @FileExtention   nvarchar(32)

SET @PackerFileName = '"C:\Program Files\WinRAR\WinRAR.exe"'
SET @PackOptions = 'a -df -ep -s -d4096 -rr -m5 -ri1'
SET @LocalBackupDrive = 'D:'

SET @LocalBackUPPath = 'D:\' + @DBName

SET @PackFileName=@LogicalBackup + '.RAR' --+ @FileExtention

SET @tmpServer='master.dbo.xp_cmdshell'

SET @tmpSQL=@PackerFileName + ' ' + @PackOptions + ' ' +  @LocalBackUPPath + '\' + @PackFileName + ' ' + @LocalBackUPPath + '\' + @LogicalBackup + '.BAK'
EXEC master..xp_cmdshell @tmpSQL

set @param=1

return @param

GO