SQL Server Performance Forum – Threads Archive
MSDE wo DataBase maintenance plan, how to backup
Dear all<br /><br />My computer equipped serveral SQL server but one is installed the 5 client’s MSDE Sql2000 server (FREE). I found the different as captioned and I can schedule using the Backup function BUT [V][V][?][?]it can only accept constant name for the output BAK file. <br /><br />Please help on how to create a dynamic file name output like ABCDB_ddmmyyhhmmss.bak<br /><br />thanks in advance<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
Sorry, should be my COMPANY rather than my computer installed serveral SQL servers…
Use master<br />go<br />Create procedure SP_DBbackup @dbname varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@path varchar(500) as<br />declare @backupname varchar(500)<br />declare @x varchar(500)<br />declare @dateandtime varchar(12)<br />declare @date datetime<br />declare @backup varchar(200)<br />set @date =getdate()<br />set @dateandtime = convert(varchar(20),@date,112) +case when <br />datepart(hh,@date) <10 then ‘0’+convert(varchar(1),datepart(hh,@date)) else<br />convert(varchar(2),datepart(hh,@date)) <br />end<br />+case when datepart(mi,@date) <10 then<br />’0’+convert(varchar(1),datepart(mi,@date)) else<br />convert(varchar(2),datepart(mi,@date)) <br />end<br />set @backup = "Backup"+" " + @dbname+ " to disk = ‘"+<br />@[email protected]+"_"[email protected]+".bak’ "<br />exec ( @backup)<br /><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS†with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]Thanks Satya SKJ[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] but when I copy&paste and run from the SQL Analyzer, it display the following error [?]<br /><br />go<br />Create procedure SP_DBbackup @dbname varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@path varchar(500) as<br />declare @backupname varchar(500)<br />declare @x varchar(500)<br />declare @dateandtime varchar(12)<br />declare @date datetime<br />declare @backup varchar(200)<br />set @date =getdate()<br />set @dateandtime = convert(varchar(20),@date,112) +case when <br />datepart(hh,@date) <10 then ‘0’+convert(varchar(1),datepart(hh,@date)) else<br />convert(varchar(2),datepart(hh,@date)) <br />end<br />+case when datepart(mi,@date) <10 then<br />’0’+convert(varchar(1),datepart(mi,@date)) else<br />convert(varchar(2),datepart(mi,@date)) <br />end<br />set @backup = "Backup"+" " + @dbname+ " to disk = ‘"+ @[email protected]+"_"[email protected]+".bak’ "<br />exec ( @backup)<br /><br />[xx(][xx(][xx(]<br />Server: Msg 207, Level 16, State 3, Procedure SP_DBbackup, Line 16<br />Invalid column name ‘Backup’.<br />Server: Msg 207, Level 16, State 1, Procedure SP_DBbackup, Line 16<br />Invalid column name ‘ ‘.<br />Server: Msg 207, Level 16, State 1, Procedure SP_DBbackup, Line 16<br />Invalid column name ‘ to disk = ”.<br />Server: Msg 207, Level 16, State 1, Procedure SP_DBbackup, Line 16<br />Invalid column name ‘_’.<br />Server: Msg 207, Level 16, State 1, Procedure SP_DBbackup, Line 16<br />Invalid column name ‘.bak’ ‘.<br /><br /><br />Thanks in advance[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
Use
set @backupname=’backup ‘[email protected]+’ to disk=’[email protected]+’_’ + convert(char(10),getdate(),112)
instead of
set @backup = "Backup"+" " + @dbname+ " to disk = ‘"+ @[email protected]+"_"[email protected]+".bak’ " Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Many thanks, the command ran successfully<br /><br />Use master<br />go<br />Create procedure SP_DBbackup @dbname varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@path varchar(500) as<br />declare @backupname varchar(500)<br />declare @x varchar(500)<br />declare @dateandtime varchar(12)<br />declare @date datetime<br />declare @backup varchar(200)<br />set @date =getdate()<br />set @dateandtime = convert(varchar(20),@date,112) +case when <br />datepart(hh,@date) <10 then ‘0’+convert(varchar(1),datepart(hh,@date)) else<br />convert(varchar(2),datepart(hh,@date)) <br />end<br />+case when datepart(mi,@date) <10 then<br />’0’+convert(varchar(1),datepart(mi,@date)) else<br />convert(varchar(2),datepart(mi,@date)) <br />end<br />set @backupname=’backup ‘[email protected]+’ to disk=’[email protected]+’_’ + convert(char(10),getdate(),112) + ‘.bak'<br />exec ( @backupname)<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]
Sorry, when I try to run<br />exec sp_dbbackup ‘tvdb’ ,’d:ackup'<br /><br />it display the below error<br /><br />Server: Msg 156, Level 15, State 1, Line 1<br />Incorrect syntax near the keyword ‘to’.<br /><br />====================================================================<br />Create procedure SP_DBbackup @dbname varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@path varchar(500) as<br />declare @backupname varchar(500)<br />declare @x varchar(500)<br />declare @dateandtime varchar(12)<br />declare @date datetime<br />declare @backup varchar(200)<br />set @date =getdate()<br />set @dateandtime = convert(varchar(20),@date,112) +case when <br />datepart(hh,@date) <10 then ‘0’+convert(varchar(1),datepart(hh,@date)) else<br />convert(varchar(2),datepart(hh,@date)) <br />end<br />+case when datepart(mi,@date) <10 then<br />’0’+convert(varchar(1),datepart(mi,@date)) else<br />convert(varchar(2),datepart(mi,@date)) <br />end<br />set @backupname=’backup ‘[email protected]+’ to disk=’[email protected][email protected]+’_’ + convert(char(10),getdate(),112) + ‘.bak'<br />exec ( @backupname)<br />GO<br />[?][?][?][?]
run PRINT @backupname brfore executiing to see if the statement is generated properly. Running the same in ISQLW will tell if the command is correct Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>