Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup SQL 2008 Small DB-Need Profesional Help Expand / Collapse
Author
Message
Posted Saturday, June 15, 2013 10:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
I am not an expert in SQL and our system crashed.
The backup we set up did not work.
Two our RAID 5 hard disk fail and using the services of a Data Recovery company we were lucky to recover the data.
I know with all the knowledge is in here and books available I can learn the proper way to backup our data, however in the meantime I need urgent help to make sure our system is backing up properly.
Is a very small business.
Total size of DB is 500Mg.
Please advise if you anyone can help, of course I am willing to compensate.
Sorry if this request is outside of the scope of this forum.
Thanks a lot.
Norbert
Post #1463877
Posted Saturday, June 15, 2013 11:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
BACKUP DATABASE your_database_name TO DISK='D:\db_15_06_2013.bak'

Run that in the query window & you will have a backup for the shortest time fix.
Then YouTube "sql server maintenance plans" and in 5-10 minutes you can learn step by step how to have your database backed up regularly (and automatically) by a few simple GUI menus.



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463885
Posted Saturday, June 15, 2013 11:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:43 PM
Points: 111, Visits: 613

The backup command listed in the previous post will work for you.


Assuming you are running the Standard version of SQL Server and not the Express edition,
if you want a simple way to both backup and schedule the backup to occur every night, you should read up on maintenance plans.
In Sql Server Management studio, connect to your database intance.
On the menu bar, go to View, Object explorer.
This will show the server you are connected to.
Expand out Management, Maintenance Plans.
Right click on Maintenance plans, and choose Maintenance plan wizard.
Press Next and change change default plan name to be MP_BACKUP
Press Change and change the schedule to be DAILY.
Press OK
Press Next
On the Select maintenance tasks, choose to Back Up Database (Full)
Press Next
In the drop down, choose the radio button for ALL Databases and press OK.
In the folder text box, pick the drive and folder where you want the backups to go to
IMPORTANT: Pick the check box that says Verify Backup Integrity.
Press Next.
In the Select Report Options pane, choose where you want the log file to be created.
and press finish.

This will create a SQL Agent job that will run daily at the time you specified and create your backups.

It would be best after making a backup, to attempt to restore that backup file to another server to make sure it can be done.



Post #1463886
Posted Saturday, June 15, 2013 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
Great help!
Yes is SQL 2008 (Enterprise Edition).
What about Shrink Database, index, the database gets larger from 300MB to 600MB.

Should I plan to backup the log files every 15 min?

If so what is the best way.

Post #1463889
Posted Saturday, June 15, 2013 12:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
drnorbert (6/15/2013)

Yes is SQL 2008 (Enterprise Edition).
What about Shrink Database, index, the database gets larger from 300MB to 600MB.


The database will get larger as you include more data. Unless the storage is very small you shouldn't need to worry about this so much unless it went from 300mb to 600mb shortly?

[quote]Should I plan to backup the log files every 15 min? (6/15/2013)

SELECT name 
FROM master.sys.databases
WHERE recovery_model_desc != 'SIMPLE'

If you run this on the database does it return any rows besides "model"? If not then you don't need to worry about backing up the transaction logs. If there is more then you need to backup those transaction logs but you won't need to do it with that much frequency...I'm guessing your database isn't very busy? 500mb would say it isn't. Running them every hour should be enough; having said that it doesn't really matter.

Watch the videos on YouTube for how to use maintenance plans. You can use this to backup your database (nightly full backup) and your transaction log (every 15 minutes)



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463890
Posted Saturday, June 15, 2013 3:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
SELECT name
FROM master.sys.databases
WHERE recovery_model_desc != 'SIMPLE'

Results:
model
ReportServer
DATA2007
model
ISMDATA

Users are changing and or adding an average of 100 records. Shall I still backup the log every hour?
Post #1463905
Posted Saturday, June 15, 2013 4:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
OK so there is 3-4 databases for you to do transaction log backups of. They are changing/adding 100 records, is that per day? per hour? per week? How important is the data? These answers will decide how frequently you will want to back them up.

The transaction log backups will be backed up to a different server/different physical disk right?

You will only need the transaction log backups if the drive containing the transaction log suddenly fails.
If this happens and you have no transaction log backups then you will lose the data from that day (assuming you have nightly full backups).
If you backup the log every 1 hour then you lose data up to 60 minutes old.
If you backup the log every 15 minutes then you only lose up to 15 minutes of data.
How important the data is will decide how regular you do them. Many of our databases at work are SIMPLE meaning that we can only return to the state from the day before; I guess because they deemed that an acceptable amount of loss.

That said; if you will keep the backups on the same server, on the same disk (database and backups both on D: or the backups on a logical E: partition) then there's no need to backup with such frequency and you can just schedule it for once per night. This setup isn't recommended though because if the disk fails you will lose everything. This is why it's better to backup to a different server. But because you have Enterprise Edition I guess your workplace should have plenty of servers you can use to keep backups on.



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463909
Posted Saturday, June 15, 2013 4:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
Sorry They are changing/adding 100 records per Hour.
Yes we have 2 servers, can be backup to the other server, also because our experience during this week I would like to backup to outside of the office (web service?) and to an external backup drive.

As we have 2 small servers, is any way to plan that when one is down the other takes over?

Also I understand that I could get auto email if the backup creates an error.


Post #1463910
Posted Saturday, June 15, 2013 6:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
I don't think a remote backup is really required. How many disks does your server have? Assuming there's one spare for backups you could just do this to maximise reliability:
backup to backups to 2-3 different locations. e.g. lets say you have your main machine (named SV01), the other one you mentioned (SV02) and managed to find another computer that has quite a bit of free space & isn't used that much (SV03). and your work computer (DRNOR) also has quite a bit of space. You would then be able to do this:
BACKUP DATABASE DATA2007 TO DISK='E:\backups\db.bak'
MIRROR TO DISK='\\SV02\backups\db.bak'
MIRROR TO DISK='\\SV03\backups\db.bak'
MIRROR TO DISK='\\DRNOR\backups\db.bak'

Your backups are then copied to 4 different computers (the first is on the database server)...you'd have to be pretty unlucky for all 4 computers to have hardware failures on the same day The same process can be done with the transaction logs too.

I think email alerts is easy to set up if your company already has an SMTP mail server. I've never done it but if you have the details it should just be filling them into the Database Mail config wizard. You can ask someone at work whether you have SMTP there. If so then YouTube should have short videos for configuring the Database Mail feature.

drnorbert (6/15/2013)
Sorry They are changing/adding 100 records per Hour.

You will probably want to backup every 15-30 minutes then to limit the potential loss.

drnorbert (6/15/2013)
As we have 2 small servers, is any way to plan that when one is down the other takes over?

There is but if you're doing things "by the books" then this would probably cost a lot of money because of the licensing fees. I don't know of the license fees for Database Mirroring; this would be something for you to look at. I also don't know if the Enterprise Edition fee is a one-off payment or an annual charge. Whoever arranged for EE at your company will have a better idea on how the license costs work.
- If it is a one-off payment then I think they would be reluctant to pay an additional charge for mirroring.
- If it is an annual fee then something worth investigating is how much it would cost to have the 2 servers using Standard Edition with Database Mirroring instead of having 1 server using Enterprise Edition...I have no idea how much more expensive the mirroring will make it but it may be worth asking the question. This would have the advantage of the possibility of automatic failovers and near to 0 data loss. But at the cost of higher license fees (maybe) and added complexity to your database configuration.



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463915
Posted Saturday, June 15, 2013 7:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
Dird:
I don't think a remote backup is really required. How many disks does your server have? Assuming there's one spare for backups you could just do this to maximise reliability:
Norbert:
Both servers have RAID 5: 4 hard disks each.

Dird:
backup to backups to 2-3 different locations. e.g. lets say you have your main machine (named SV01), the other one you mentioned (SV02) and managed to find another computer that has quite a bit of free space & isn't used that much (SV03). and your work computer (DRNOR) also has quite a bit of space. You would then be able to do this:
BACKUP DATABASE DATA2007 TO DISK='E:\backups\db.bak'
MIRROR TO DISK='\\SV02\backups\db.bak'
MIRROR TO DISK='\\SV03\backups\db.bak'
MIRROR TO DISK='\\DRNOR\backups\db.bak'
Your backups are then copied to 4 different computers (the first is on the database server)...you'd have to be pretty unlucky for all 4 computers to have hardware failures on the same day The same process can be done with the transaction logs too.

Norbert:
This is a great solution.
This command are execute in a query?


Dird:
I think email alerts is easy to set up if your company already has an SMTP mail server. I've never done it but if you have the details it should just be filling them into the Database Mail config wizard. You can ask someone at work whether you have SMTP there. If so then YouTube should have short videos for configuring the Database Mail feature.

Norbert:
No SMTP server. Can we use an live.com?
I can't find YouTube for configuring the Mail feature.

Dird:
You will probably want to backup every 15-30 minutes then to limit the potential loss.

Norbert:
Thanks make sense.

Dird:
There is but if you're doing things "by the books" then this would probably cost a lot of money because of the licensing fees. I don't know of the license fees for Database Mirroring; this would be something for you to look at. I also don't know if the Enterprise Edition fee is a one-off payment or an annual charge. Whoever arranged for EE at your company will have a better idea on how the license costs work.
- If it is a one-off payment then I think they would be reluctant to pay an additional charge for mirroring.

Norbert:
We have already in both servers SQL with all the licenses.
No one else is in the company to advise, I am kind of stuck.
Do you know any reference of how to figure out Database Mirroring?

Post #1463917
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse