|
cum creez un folder in T-SQL (SQLServer 2000)
Last post 06-21-2007, 11:34 PM by ggciubuc. 26 replies.
-
06-06-2007, 10:34 AM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
cum creez un folder in T-SQL (SQLServer 2000)
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.
|
|
-
06-06-2007, 11:09 AM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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
|
|
-
06-06-2007, 11:18 AM |
-
Diana
-
-
-
Joined on 03-21-2006
-
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
Incearca de exemplu EXEC xp_cmdshell 'mkdir C:\Backup' http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ntcmds.mspx
|
|
-
06-06-2007, 11:32 AM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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
|
|
-
06-06-2007, 11:55 AM |
-
katalyn
-
-
-
Joined on 09-20-2006
-
Bucureşti
-
sysadmin
-
-
|
cum creez un folder in T-SQL (SQLServer 2000)
Incluzi calea între ghilimele:
EXEC master..xp_cmdshell 'mkdir "D:\Program Files\MSSQL\BackUp\TEST"'
Cătălin D.
|
|
-
06-06-2007, 1:03 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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
|
|
-
06-06-2007, 2:18 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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
|
|
-
06-06-2007, 2:35 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
sa rezolvat am facut asa....:)
set @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"' EXEC master..xp_cmdshell @strSQL
|
|
-
06-06-2007, 3:13 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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
|
|
-
06-06-2007, 3:59 PM |
-
Diana
-
-
-
Joined on 03-21-2006
-
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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.
|
|
-
06-06-2007, 4:26 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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 ;)
|
|
-
06-06-2007, 5:51 PM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
se pare ca nu imi lanseaza "C:\Program Files\WinRAR\WinRAR.exe" ....nu il recunoaste.....dar nu-mi dau seama de ce :(
|
|
-
06-06-2007, 6:37 PM |
-
MrSmersh
-
-
-
Joined on 03-31-2006
-
Timisoara
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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.
|
|
-
06-07-2007, 9:37 AM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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'
|
|
-
06-07-2007, 11:05 AM |
-
Alex
-
-
-
Joined on 10-25-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: cum creez un folder in T-SQL (SQLServer 2000)
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
|
|
Page 1 of 2 (27 items)
1
|
|
|