June 15, 2013 at 10:40 am
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
June 15, 2013 at 11:36 am
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.
June 15, 2013 at 11:56 am
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.
June 15, 2013 at 12:37 pm
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.
June 15, 2013 at 12:47 pm
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?
Should I plan to backup the log files every 15 min? (6/15/2013)
SELECT nameFROM 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)
June 15, 2013 at 3:44 pm
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?
June 15, 2013 at 4:04 pm
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.
June 15, 2013 at 4:15 pm
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.
June 15, 2013 at 6:31 pm
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.
June 15, 2013 at 7:33 pm
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?
June 15, 2013 at 7:48 pm
drnorbert (6/15/2013)
This is a great solution.This command are execute in a query?
It can be executed in a query to test it. First you would need to create the folders on the machines & share them with read/write access.
Once you've tested it successfully (for database & transaction log backups) then you can create a Maintenance Plan that will backup to those servers the databases (full; every night) and transaction logs (every 15 minutes).
drnorbert (6/15/2013)
No SMTP server. Can we use an live.com?I can't find YouTube for configuring the Mail feature.
It seems you can. Read this page for details on setting up the Mail feature to send to your live.com account.
June 16, 2013 at 6:13 am
Hi Dird
I am trying to figure out how you are attaching my responses with your reply massages as well as the images such as:
🙂
Thanks for the details about setting up the Mail with live.com.
I am executing the query as per your instructions:
1)
Execute:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
Message:
Processed 273864 pages for database 'ISMDATA', file 'ISMDATA_dat' on file 1.
Processed 1 pages for database 'ISMDATA', file 'ISMDATA_log' on file 1.
BACKUP DATABASE successfully processed 273865 pages in 58.470 seconds (36.592 MB/sec).
Is this means that the log is backup together with each execution or is a different command for the transaction log every 15 min?
2)
When add MIRROR I am getting an error msg.
Execute:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak'
Message:
Msg 3215, Level 16, State 1, Line 1
Use WITH FORMAT to create a new mirrored backup set.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
3)
Do I need to shrink the DB?
June 16, 2013 at 6:22 am
drnorbert (6/16/2013)
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'Is this means that the log is backup together with each execution or is a different command for the transaction log every 15 min?
I don't understand your question. The statement you ran backed up the database. To backup the transaction log you would need to use a different command (BACKUP LOG) to a different file on disk. You will need to use a scheduled job to backup them automatically on a regular basis (e.g. database backup 1 time per day; log backup every 15 minutes).
drnorbert (6/16/2013)
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak'
Message:
Use WITH FORMAT to create a new mirrored backup set.
Because you already used db.bak for a non-mirrored backup you must recreate the formatting. The first time you run it use:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak' WITH FORMAT
After the first time you can remove the WITH FORMAT option.
drnorbert (6/16/2013)
Do I need to shrink the DB?
No.
Edit: To attach the text you click the "Quote" button instead of "Reply" but then I make them shorter by adding extra (quote) (/quote) statements and deleting text that isn't important.
June 16, 2013 at 6:41 am
Dird (6/16/2013)
drnorbert (6/16/2013)
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'Is this means that the log is backup together with each execution or is a different command for the transaction log every 15 min?
I don't understand your question. The statement you ran backed up the database. To backup the transaction log you would need to use a different command (BACKUP LOG) to a different file on disk. You will need to use a scheduled job to backup them automatically on a regular basis (e.g. database backup 1 time per day; log backup every 15 minutes).
drnorbert (6/16/2013)
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak'
Message:
Use WITH FORMAT to create a new mirrored backup set.
Because you already used db.bak for a non-mirrored backup you must recreate the formatting. The first time you run it use:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak' WITH FORMAT
After the first time you can remove the WITH FORMAT option.
drnorbert (6/16/2013)
Do I need to shrink the DB?No.
Edit: To attach the text you click the "Quote" button instead of "Reply" but then I make them shorter by adding extra (quote) (/quote) statements and deleting text that isn't important.
June 16, 2013 at 6:50 am
Thanks Dird you helped me a lot.
Can I get your contact info in case I need your professional services?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply