Luis Diaz
asked on
Backup SQL DB with Vbscript
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,s econd)" and should be place at the following path (C:\backups-folder. In the script I should be able to put the db user, password
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
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
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
ASKER
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
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
ok.
it is the
it is the
sMA_name = sDBname & "_now_date("
line 17sOutputFolder = "c:\tester\" 'note the trailing backslash
sMA_name = sOutputFolder & sDBname & "_now_date("
ASKER
@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.
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.
1.
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
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
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
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.
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.
ASKER
Hello robr,
Thank you for your comment, I will test your script tomorrow with the log proposal.
Thank you for your comment, I will test your script tomorrow with the log proposal.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thank you again for your help!
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()
Thank you again for your help!
1. it is already there. line 44-48
2. its there partly. change line 69
2. its there partly. change line 69
if sResult=0 then
oFS.WriteLine Now & " = Backup has been performed successfully"
wscript.echo "<ok"
else
3. its something like that code u posted. try it out.
ASKER
Cool, it works, thank you. Last question, is there a risk to launch this script if the db is used by the users?
dont think so as you are doing backup usingthe server processes
ASKER
Ok, thank you!
ASKER
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.
Accepted answer: 0 points for LD16's comment #a40626933
for the following reason:
Full code provided by the expert.
my response 40626953 should be selected as solution.
ASKER
Done, sorry I thought it was done before.
http://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.
-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.