Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


1101

2318 posts

Uber Geek


#155931 13-Nov-2014 13:35
Send private message

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

Create new topic
networkn
23479 posts

Uber Geek

Trusted
Lifetime subscriber

  #1175191 13-Nov-2014 13:45
Send private message

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
352 posts

Ultimate Geek

Trusted

  #1175217 13-Nov-2014 13:53
Send private message

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




 


 
 
 
 


Ragnor
8035 posts

Uber Geek

Trusted

  #1175244 13-Nov-2014 14:25
Send private message

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

2318 posts

Uber Geek


  #1175281 13-Nov-2014 15:57
Send private message

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





Create new topic





News »

Huawei launches IdeaHub Pro in New Zealand
Posted 27-Oct-2020 16:41


Southland-based IT specialist providing virtual services worldwide
Posted 27-Oct-2020 15:55


NASA discovers water on sunlit surface of Moon
Posted 27-Oct-2020 08:30


Huawei introduces new features to Petal Search, Maps and Docs
Posted 26-Oct-2020 18:05


Nokia selected by NASA to build first ever cellular network on the Moon
Posted 21-Oct-2020 08:34


Nanoleaf enhances lighting line with launch of Triangles and Mini Triangles
Posted 17-Oct-2020 20:18


Synology unveils DS16211+
Posted 17-Oct-2020 20:12


Ingram Micro introduces FootfallCam to New Zealand channel
Posted 17-Oct-2020 20:06


Dropbox adopts Virtual First working policy
Posted 17-Oct-2020 19:47


OPPO announces Reno4 Series 5G line-up in NZ
Posted 16-Oct-2020 08:52


Microsoft Highway to a Hundred expands to Asia Pacific
Posted 14-Oct-2020 09:34


Spark turns on 5G in Auckland
Posted 14-Oct-2020 09:29


AMD Launches AMD Ryzen 5000 Series Desktop Processors
Posted 9-Oct-2020 10:13


Teletrac Navman launches integrated multi-camera solution for transport and logistics industry
Posted 8-Oct-2020 10:57


Farmside hits 10,000 RBI customers
Posted 7-Oct-2020 15:32









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.