Automatic Backups of ArcSDE on SQL Server Express

[MarkVolz]

Hello,

I would like to try to create a script so I can automatically backup my ArcSDE data that is stored on SQL Server Express.  I am using ArcGIS for Server Workgroup Standard v 10.2.2.  My SQL Server is MSSQL Server Express 2008 R2 which is shipped with ArcGIS 10.2.  I have create a backup sql and batch script on the server, however when I try to restore the database using ArcGIS Catalog then ArcCatalog crashes.

The batch file looks like this:
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -i"C:\SQLBackupScripts\LQPMonday.sql" -S "localhost\sqlexpress"

The SQL file looks like this:
BACKUP DATABASE [ParcelsLQP] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\LQPParcelsMonday.bak' WITH NOFORMAT, NOINIT,  NAME = N'ParcelsLQP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Results:
The backup appears to run and it creates a backup file in about four seconds.  The file size is about twice as large compared to when I create a backup using ArcCatalog-->Database-->Administration-->Backup.

Question:
Is there a way to automate backups for SQL Server Express?

Thanks

[SamuelTompsett]

Hello Mark,

I am not sure why the .bak file size varies from ArcCatalog backup compared to SQL Server Express backup. I tested this on my machine, both sizes of the .bak files were the same. There could have been edits made to the database before the second backup was taken? The SQL script could vary from the ArcCatalog execution?

Check out this article from Microsoft. Might help out with automating the backup from SQL Server Express.

How to schedule and automate backups of SQL Server databases in SQL Server Express‌

Hope that helps!

[MarkVolz]

Samuel,

I am closer to a solution.  I figured out that by default the backup for SQL Server appends data instead of deletes data.  I fixed that part by deleting the backup file before running the backup script.  I still am having trouble as I can manually run the .bat file, but it seems that the task scheduler will not.  I will take a closer look at the link that you provided to see if that will help out.

This is what my batch file looks like now:

cd /d "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup"

del MyBackupFile.bak

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -i"C:\SQLBackupScripts\MySQLFile.sql" -S "localhost\sqlexpress"

cd /d "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup"

copy MyBackupFile.bak \\AnotherServer\AShareOnTheServer$\MyBackupFile.bak /y

cd /d "C:\SQLBackupScripts"

The SQL file has not really changed:

BACKUP DATABASE [MyDatabase] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\MyBackupFile.bak' WITH NOFORMAT, NOINIT,  NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

So once again, the above code works fine now when I manually run the backup script.  Now I need to figure out how to automate the backups.

[SamuelTompsett]

Hello Mark,

Take a look at this as well:

Might help out with the task scheduler part of things

Run a batch file with Windows task scheduler - Stack Overflow‌