Solved

VBScript to Backup SQL

Posted on 2012-03-22
2
1,801 Views
Last Modified: 2012-03-23
Hello Experts,

Is it possible to create a VBscript to backup a SQL database?

Using the SQL backup in SQL Express 2008 R2 I was able to generate the following script and I would like to turn it into a VBS:

BACKUP DATABASE [db] TO  DISK = N'D:\db' WITH NOFORMAT, NOINIT,  NAME = N'db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Open in new window


As a final result I would like to add the VBS to Windows scheduler every 15 min. Is this a safe way to do it since my database is only 40mb, expecting it to grow maybe to 200 over the next year or two?

Lastly, I am wondering when the script creates this file, will I be able to just copy the backed up file and attach it to my development server without doing import/export?

Thanks
0
Comment
Question by:APD_Toronto
2 Comments
 
LVL 2

Accepted Solution

by:
Kelden earned 500 total points
ID: 37755119
You can execute the task scheduler without a vbs. Just create a batch file and add the following:

set OSQL=%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\osql.exe
 "%OSQL%" -U "sa" -P "<password>" -S "." -d "master" -b -e -q "BACKUP DATABASE [db] TO  DISK = N'D:\db' WITH NOFORMAT, NOINIT,  NAME = N'db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"

Open in new window


you have to change the path to your osql.exe file.
-S is the instance name. use "." for no instance or ".\instancename" if you have one.
Use -E instead of -U and -P if you want to use a trusted connection.
0
 

Author Comment

by:APD_Toronto
ID: 37755209
can you please clarify?-
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

856 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