Hi, my vc server uses a sql express 2005 database.
With SQL management studio, I can do a manual backup.
But how can I do to schedule this backup ?
Can't. This isn't a function of SQL Express.
You should upgrade to SQL 2005 Standard, or continue to do manual backups.
That is the difference between Express and Standard/Enterprise version of SQL, functionality.
That's why the option "create job" is disable ...
As I can create a sql script, do you know how to launch it manually ?
Task manager technically is a way to automate functions, so theorhetically you can create a new task job point it to the sql script, and run it regularly. I would test it thoroughly and make sure the script runs perfect before making it automated, you could really mess things up.
Task manager can run just about anything just by setting up a job and pointing it to any batch, script, or executable.
You can use various COM objects to do an SQL backup or there is the command-line osql utility.
Creating a backup with these methods isn't terribly difficult but restoring can be a little tricky. What I reccomend is to create a batchfile which stops the MSDE engine, makes a file copy of the master and VC databases and then restarts the service.
There are downsides such as VC will not be able to talk to the database for 30-90 seconds. Since an MSDE engine isn't supporting clients or hosting additional databases this seems a fairly reasonable lapse in VC availability. Write the batchfile and schedule it in Task Manager for off-hours. Restoration at this point becomes very simple. Just intsall the MSDE, shutdown the engine, copy the backed up master and VC databases back and restart the engine.
: Create Backup Folder (Just one example)
FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
net stop "SQL Server (OFFICE SERVERS)"
:Copy the Databases
copy "C:\Program Files\Microsoft Office Servers\12.0\Data\MSSQL.1\MSSQL\Data\*.*" "C:\backups\%date%\"
net start "SQL Server (OFFICE SERVERS)"
You should test the syntax using your own configuration information and also do a test restoration before using this for anything important as I haven't tested it with newer versions of MSDE.
I also only have the SQL version shipped with the VC. So I tried to make a backup with it.....
I wrote a batch file like this one:
echo Start DB Backup > c:\backup_db\backup.log
sqlcmd -S SERVER\MSDE_VC -i c:\Backup_DB\backup_db.sql >> c:\backup_db\backup.log
echo Start Logs Backup >> c:\backup_db\backup.log
sqlcmd -S SERVER\MSDE_VC -i c:\Backup_DB\backup_log.sql >> c:\backup_db\backup.log
The two sql-files look like this:
BACKUP DATABASE VCDB TO DISK='C:\Backup_DB\VCDB.bak'
BACKUP LOG VCDB TO DISK='C:\Backup_DB\VCDB_LOG.bak'
The batchfile runs every nigth with the windows taskplanner that's all.
For me it's sufficent to only have the last state of the db in the night, but actually I'm not sure if it's okay to delete the old backup files, especially the logs file, before the new backup runs. And I never tested a restore.
Because I'm not confirm with MS SQL Express it would be great if someone could have a look at my script.