SQL backup via SQL Express management studio : reliable ??

Forums › IT Pro and developers › SQL backup via SQL Express management studio : reliable ??

1101

3141 posts

Uber Geek
+1 received by user: 1143

#155931 13-Nov-2014 13:35

Hi Guys.
How reliable is backuping sql from the express management studio. I know how to do this, I wonder just how reliable it is.

I have had a tech from a particular softwares help desk tell me that method doesnt allways work.
I have had it fail (backup wasnt complete) myself when moving the database from 1 server to another (I then used a 3rd party program
instead)

The plan is to setup a batch file to run a weekly sql backup & spit that backup over to an old server or NAS. The 'normal' Win backups will still
be run regardless .
The idea being if the server had a complete meltdown, we could get the companies software up & running quite quickly by importing that backup
onto another old server or workstation, both of which are ready to go just in case - just needing the current database. Not perfect but would get the company semi-operational again , quickly.

Cheers

networkn

Networkn
32864 posts

Uber Geek
+1 received by user: 15454

ID Verified

Trusted

Lifetime subscriber

#1175191 13-Nov-2014 13:45

I would recommend sqlmaint.exe for backing up express, it's free and we have used it and restored with backups taken from it for a long time.

I'd be happy to provide you a batch file we use so long as you understand that it's 100% all care no responsibility.

Killerkiwi2005

374 posts

Ultimate Geek
+1 received by user: 85

Trusted

#1175217 13-Nov-2014 13:53

Never has an issue with it, of course that assumes the backup completes and the there are no disk errors

Ragnor

8279 posts

Uber Geek
+1 received by user: 585

Trusted

#1175244 13-Nov-2014 14:25

You need to ask yourself the following questions
1: How much data are you comfortable losing? eg: If you only have a full backup from say 2am Monday and your DB dies at 1am Tuesday you've lost 23hrs of data. Databases in Full mode need regular LOG backups (every few minutes or less) not just once off FULL backups.
2: How are you monitoring whether your backups are successful or if there is an integrity problem with a database?
3: How fast do you need to restore your data?

Brent Ozar has some great blogs and videos about this
http://www.brentozar.com/sql/backup-best-practices/

Also Ola Hallengren has awesome scripts for the "can't afford to buy Redgate tools" camp
https://ola.hallengren.com/

1101

3141 posts

Uber Geek
+1 received by user: 1143

#1175281 13-Nov-2014 15:57

Ragnor: You need to ask yourself the following questions
1: How much data are you comfortable losing? eg: If you only have a full backup from say 2am Monday and your DB dies at 1am Tuesday you've lost 23hrs of data. Databases in Full mode need regular LOG backups (every few minutes or less) not just once off FULL backups.
2: How are you monitoring whether your backups are successful or if there is an integrity problem with a database?
3: How fast do you need to restore your data?

1) Good point. At this stage the previous days backup will be OK. More frequent backups could be scheduled later if needed
2) Just using SBS2011 built in backup : emails auto sent that show backup successful or fail. Of course backups needed to be occasionally tested etc.
3) ASAP, but have to be reasonable about this, anywhere from an hour to a day. No different than a power cut etc.

Im not aiming for 100% perfection or redundancy , just an added bit of security with a 2nd backup, so that the db can be imported to another working pc/server if everything goes very very bad on the server , taking that out of action for more than a few days.

============================================================
What I used to do, as well as SBS backup, was a backup script as below in bat files , as below

mon.sql is the backup script created to SQL Management Exp

mon.bat is the batch file to run/schedule

--------------------

Monday.bat
sqlcmd -S localhost\xxxSERVER -i mon.sql -o mon-log.txt

Mon.sql
BACKUP DATABASE [xxxx] TO DISK = N'C:\storage\SQL Backup\Mon.bak' WITH NOFORMAT,

INIT, NAME = N'xxxxx-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO