topic Re: Automatic Backups of ArcSDE on SQL Server Express in Data Management Questions

Automatic Backups of ArcSDE on SQL Server Express

MarkVolz — Wed, 02 Jul 2014 20:09:48 GMT

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

Re: Automatic Backups of ArcSDE on SQL Server Express

SamuelTompsett — Thu, 10 Jul 2014 21:25:08 GMT

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!

Re: Automatic Backups of ArcSDE on SQL Server Express

MarkVolz — Fri, 11 Jul 2014 14:22:30 GMT

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

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.

Re: Automatic Backups of ArcSDE on SQL Server Express

SamuelTompsett — Fri, 11 Jul 2014 14:53:47 GMT

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‌