backing up an SQL dbase

[AHarvey]

NEVER MIND, YOUTUBE VIDEO TO THE RESCUE

 

[Pho]

I know you've already answered it.. but we use ExpressMaint (free) for scheduled backups. It runs each night as a scheduled task and is automatically compressed with 7-zip ready to be copied to our backup FTP server. All done through batch files .

 

[ThorpedoUK]

I know you've already answered it.. but we use ExpressMaint (free) for scheduled backups. It runs each night as a scheduled task and is automatically compressed with 7-zip ready to be copied to our backup FTP server. All done through batch files .

We use backupexec with the SQL Agent, even then its not perfect!

I like the look of ExpressMaint and will definately check that out! thanks

 

[Pho]

It's been running for several months without problems for us. Here's my batch file if it's of any use. Just make it run with the task scheduler to automate it:

@echo off

echo DBNAME - Reindexing..
expressmaint -S (local) -D DBNAME -T REINDEX -R C:\DBNAME\Backups\Database\MSSQL\Backups\reports -RU DAYS -RV 1
	
echo DBNAME - Updating statistics..
expressmaint -S (local) -D DBNAME -T STATSFULL -R C:\DBNAME\Backups\Database\MSSQL\Backups\reports -RU DAYS -RV 1

echo DBNAME - Performing full backup..
ExpressMaint -S (local) -D DBNAME -T DB -R C:\DBNAME\Backups\Database\MSSQL\Backups\reports -RU WEEKS -RV 1 -B C:\DBNAME\Backups\Database\MSSQL\Backups -BU DAYS -BV 31 -V -C

rem Compress with 7zip
CD Backups\DBNAME

echo.
echo DBNAME - Compressing all .bak files and moving to \compressed
for /f %%a IN ('dir /b *.bak') do "../../7za" a -mx9 compressed\%%a.7z %%a

echo DBNAME - Moving all uncompressed .bak files to \uncompressed
for /f %%a IN ('dir /b *.bak') do move %%a uncompressed

CD ../../

For 7-zip to work place 7za.exe in the same directory as the batch file.

You'll also need to create a backups\DBNAME\uncompressed and a backups\DBNAME\compressed folder inside the same folder as the batch file. The compressed folder is then monitored and backed up. We use Cobain backup set to only copy new files via the FTP.

You can probably shorten the path names a bit too, but it works as it is so I haven't touched it .

 

[ThorpedoUK]

Excellent mate, thanks for the additional info - I am going to give this a go Monday, I'll let you know how it goes!

Ive been stuck a lot of the day installing SQL Server 2008 64bit STD on Windows 2008 Server STD (both of which look really good) - first time - not used either before, only SQL 2005/WIN 2003.

Have you? any major differences between 2005/2008?

 

[Pho]

Our office server here runs Server 2003 and our rack runs 2008 / SQL Server 2008. If you've used Vista/W7 before then you should find 2008 much easier to use, and SQL Server 2008 is very much like 2005 really .