?
Solved

Backup SQL DB with Vbscript

Posted on 2015-02-21
17
Medium Priority
?
256 Views
Last Modified: 2015-02-25
Hello,

I am looking for Vb Script which export a full backup of my DB (.bak extension).

back file should be composed as follow "dbname_now_date(yyy,month,day,hours,minutes,second)" and should be place at the following path (C:\backups-folder. In the script I should be able to put the db user, password
0
Comment
Question by:LD16
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40625428
using oSql it is easy enough,
http://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
    -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT"

hard part if formatting dates in VBS..
ill dig up an old script.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40625448
try this...  runs but doesnt login correctly to my test server for some reason. can look again tomorrow if needed.

run from the C:\Program Files\Microsoft SQL Server\110\Tools\Binn folder or similar for your version of SQLServer.

cscript cscript c:\ee\sql_backup.vbs
Option Explicit

	Const ForReading = 1, ForWriting = 2, ForAppending = 8 
	dim oSQLs , oSQLDB , sResult, oFSO , oQres , sServer, oShell
	
	dim sMA_name , sUser,sPwd, sDBname , logFile

	dim sqlRun , i , Outputfile , sTmp, sDataLoc
	
	sServer="mySQL"
	sUser = "myuser"
	sPwd = "mypwd"
	sDBname = "mydb"
	
	logFile = "C:\ee\test1.txt"
	
	sMA_name = sDBname & "_now_date(" & year(now) & "," & month(now) & "," & day(now) & "," & hour(now) & "," & minute(now) & "," & second(now) & ").bak"
	

	wscript.echo "<" & sMA_name & ">"
	
	set oShell = Createobject("wscript.shell")
	set oFSO = CreateObject("Scripting.FileSystemObject")	


'Phase 0 : ==== setup logging

	
	dim f , oFS 

	if oFSO.FileExists(logFile) then
    		set f = oFSO.GetFile(logFile)
    
    		set oFS = f.OpenAsTextStream(ForAppending,0)
  	else
 
    		set oFS = oFSO.CreateTextFile(logFile, True)

	end if
	oFS.writeline "Start : " & now()

	
'Phase 4 : ======== detach the DB ============
	'on error resume next
	'on error goto 0	
	
	'C:\Program Files\Microsoft SQL Server\110\Tools\Binn
	Outputfile = "BACKUP DATABASE " & sDBname & " TO DISK='" & sMA_name & "' WITH FORMAT"
	
	SQLrun = "sqlCmd -U " & sUser & " -P " & sPwd & " -Q " & chr(34) & Outputfile & chr(34)
	wscript.echo "<" & sqlRun & ">"

	sResult = oShell.run (SQLRun, 2, True)
	
	if sResult=0 then
		oFS.WriteLine "4.1 bakup ok"
		wscript.echo "<ok"
	 else
		oFS.WriteLine "4.2 Error bakup"
		wscript.echo "<error"
	end if
	
	if Err.number > 0 then
		oFS.WriteLine "Error@4.1 : " & err.number &  Err.description
		oFS.WriteLine SQLrun
		wscript.quit
	end if
	
	
	Err.Clear
	on error resume next
	
		

Open in new window

0
 

Author Comment

by:LD16
ID: 40625491
Hello,

I don't see in the code above where can I modify the ouput folder in which I want to place the backup file.

Thank you
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40625518
ok.

it is the
sMA_name = sDBname & "_now_date("

Open in new window

 line 17

sOutputFolder = "c:\tester\"   'note the trailing backslash
sMA_name = sOutputFolder  & sDBname & "_now_date(" 

Open in new window

0
 

Author Comment

by:LD16
ID: 40625627
@robr : your script works, however I made some modifications in the sMA_name:

sMA_name = sDBname & "_now_date(" & year(now) & "," & month(now) & "," & day(now) & "," & hour(now) & "," & minute(now) & "," & second(now) & ").bak"

revised name :
sMA_name = sDBname & "_now_date_" & year(now) & "_" & month(now) & "_" & day(now) & "_" & hour(now) & "_" & minute(now) & "_" & second(now).bak"

Special characters such as "(", ")", ",", block the exports.

I would like to know if we can improve the script by enhancing the log. It should contains with the following options:

1-Error: The sOutputFolder doesn't exist
2-Error: Unable to access to the database sDBname
3-Error: Database name set up contains special character please check sMA_name

If the 3 option is not possible at least the two options will be ok for me.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40626912
1.
sOutputFolder = "c:\tester\"   'note the trailing backslash
sMA_name = sOutputFolder  & sDBname & "_now_date("

'.Phase0.
exists = fso.FolderExists(sOutputFolder )

if not exists then 
    		oFS.WriteLine "Error  Outputfolder not found : " & sOutputFolder
		wscript.quit
end if

if not IsValidFile(sDBname) then
    		oFS.WriteLine "Error  DBName has invalid characters : " & sDBname
		wscript.quit
end if

Open in new window


2. Accessible DB ?  there used to be a feature called SQLDMO that I used for this in 2006, but now replaced by SMO. I haven't had cause to use this object at all but expect It could check if a db was online.  I dont know of other easy way at the moment.

3. DBName has special characters ?  Only way would be to try to write to a text file with the name of the output bak file and check for error ?  also see http://windowssecrets.com/forums/showthread.php/122981-Parsing-for-forbidden-characters-in-file-name
'HansV  @ WindowsSecrets 2009
Public Function IsValidFile(sFileName As String) As Boolean
  IsValidFile = Not (sFileName Like "*[/\|<>:*?""]*")
End Function

Open in new window

0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40626927
update. a quick search seems to indicate Microsoft .. We have announced no plans to provide vbscript support for SMO.
As you have discovered, DMO continues to work well, but when it comes time to script to new 2005 features, it comes up short.
 

so item 2 is not a simple process for me.
0
 

Author Comment

by:LD16
ID: 40626933
Hello robr,

Thank you for your comment, I will test your script tomorrow with the log proposal.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 2000 total points
ID: 40626953
some errors in the bits i pasted.

new script attached.
Option Explicit

	Const ForReading = 1, ForWriting = 2, ForAppending = 8 
	dim oSQLs , oSQLDB , sResult, oFSO , oQres , sServer, oShell
	
	dim sMA_name , sUser,sPwd, sDBname , logFile , sOutputFolder

	dim  sqlRun , i , Outputfile , sTmp, sDataLoc, exists
	
	sServer="mySQL"
	sUser = "myuser"
	sPwd = "mypwd"
	sDBname = "mydb"
	
	logFile = "C:\ee\test1.txt"
	
	sOutputFolder = "c:\ee\"   'note the trailing backslash
  
  sMA_name = sOutputFolder  & sDBname & "_now_date("  & year(now) & "_" & month(now) & "_" & day(now) & "_" & hour(now) & "_" & minute(now) & "_" & second(now) & ").bak"
	

	wscript.echo "<" & sMA_name & ">"
	
	set oShell = Createobject("wscript.shell")
	set oFSO = CreateObject("Scripting.FileSystemObject")	


'------ Phase 0 : ==== setup logging
	
	dim f , oFS 

	if oFSO.FileExists(logFile) then
    		set f = oFSO.GetFile(logFile)  
    		set oFS = f.OpenAsTextStream(ForAppending,0)
  	else
    		set oFS = oFSO.CreateTextFile(logFile, True)
	end if
	oFS.writeline "Start : " & now()
	
'------- Phase 1 : ==== checks
	
exists = oFSO.FolderExists(sOutputFolder )

if not exists then 
    oFS.WriteLine "Error  Outputfolder not found : " & sOutputFolder
    wscript.echo "<error5"
		wscript.quit
end if

if not IsValidFile(sDBname) then
    oFS.WriteLine "Error  DBName has invalid characters : " & sDBname
    wscript.echo "<error6"
		wscript.quit
end if
	
'Phase 4 : ======== detach the DB ============
	'on error resume next
	'on error goto 0	
	
	'C:\Program Files\Microsoft SQL Server\110\Tools\Binn
	Outputfile = "BACKUP DATABASE " & sDBname & " TO DISK='" & sMA_name & "' WITH FORMAT"
	
	SQLrun = "sqlCmd -U " & sUser & " -P " & sPwd & " -Q " & chr(34) & Outputfile & chr(34)
	wscript.echo "<" & sqlRun & ">"

	sResult = oShell.run (SQLRun, 2, True)
	
	if sResult=0 then
		oFS.WriteLine "4.1 bakup ok"
		wscript.echo "<ok"
	 else
		oFS.WriteLine "4.2 Error bakup"
		wscript.echo "<error"
	end if
	
	if Err.number > 0 then
		oFS.WriteLine "Error@4.1 : " & err.number &  Err.description
		oFS.WriteLine SQLrun
		wscript.quit
	end if
	
	
	Err.Clear
	on error resume next
	


Public Function IsValidFile(sFileName ) 
'-------------------------------------------------------------------------
' Procedure : IsValidFile
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 31-10-2009
' Purpose   : Returns TRUE if filename contains no invalid characters
'-------------------------------------------------------------------------
    dim sInvalidChars 
    sInvalidChars = "<>?[]:|*!"
    Dim lCt 
    If (Len(sFileName) > 0) Then
        For lCt = 1 To Len(sInvalidChars)
            If InStr(sFileName, Mid(sInvalidChars, lCt, 1)) > 0 Then
                IsValidFile = False
                Exit Function
            End If
        Next
        IsValidFile = True
    End If
End Function

Open in new window

0
 

Author Comment

by:LD16
ID: 40627645
Hello Robber,

Thank you for this revised version it works!
I have some remarks and imporvement if you can do it (it would be great):
1-Is there a way to add a log case such as "sOutputFolder doesn't exist"?
2-If backup has been performed successfully can we add  Now & "Backup has been performed successfully".
3-Retention files, I will like to add a loop in which I remove all the files with .bak extension older than retentiondate, something like this

For Each File In Folder.Files 
        If If Right(LCase(File.Name), 3) = LCase("bak") and (DateDiff("d", File.DateLastModified, DateSysteme) > CInt(reteniondate)) then File.Delete()

Open in new window


Thank you again for your help!
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40628004
1. it is already there. line 44-48
2. its there partly. change line 69
	if sResult=0 then
		oFS.WriteLine Now & " = Backup has been performed successfully"
		wscript.echo "<ok"
	 else

Open in new window

3. its something like that code u posted. try it out.
0
 

Author Comment

by:LD16
ID: 40628095
Cool, it works, thank you. Last question, is there a risk to launch this script if the db is used by the users?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40629226
dont think so as you are doing backup usingthe server processes
0
 

Author Comment

by:LD16
ID: 40629409
Ok, thank you!
0
 

Author Comment

by:LD16
ID: 40630321
I've requested that this question be closed as follows:

Accepted answer: 0 points for LD16's comment #a40626933

for the following reason:

Full code provided by the expert.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40629986
my response 40626953 should be selected as solution.
0
 

Author Comment

by:LD16
ID: 40630323
Done, sorry I thought it was done before.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft will be releasing the Windows 10 Creators Update in just a matter of weeks. Are you prepared? Follow these steps to ensure everything goes smoothly and you don't lose valuable data on your PC.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Suggested Courses

770 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