DataBase Backup Job [IMG GUIDE]

Hi to all MU Server admin, this guide will help you backup your database using sql job agent.

Code:

---

DECLARE @file varchar(70)
SET @file = N'D:\BACKUP'+REPLACE(REPLACE(CAST(getdate() AS varchar), ' ', '_'), ':', '_')
SELECT @file
BACKUP DATABASE [MuOnline] TO DISK = @file WITH INIT , NOUNLOAD , NAME = N'MuOnline backup', NOSKIP , STATS = 10, NOFORMAT

---

Above is the Script which will backup your database

now go to Start>All programs>Microsoft SQL Server>Enterprise Manager

Follow the image below.

Note: ON the Script above(the one has red color) it must exist or else the job will gives you a error.

1.http://img260.imageshack.us/img260/6180/sqljob1qk1.jpg
2.http://img183.imageshack.us/img183/9038/sqljob2ul8.jpg
3.http://img260.imageshack.us/img260/9082/sqljob3ik1.jpg
4.http://img260.imageshack.us/img260/3343/sqljob4ap5.jpg
5.http://img183.imageshack.us/img183/3746/sqljob5fa8.jpg
6.http://img183.imageshack.us/img183/5959/sqljob6du1.jpg
7.http://img183.imageshack.us/img183/9548/sqljob7my4.jpg
8.http://img142.imageshack.us/img142/315/sqljob8up6.jpg

Ok, when you finish, you may Right Click the Job and select Start Job
The job continue to occure depends on the Occure Time you set for that Job.

Credits:
mafiux for the new script
DWT

it will be of great use to ALL Admins :)

Very nice indeed liselotte, now if someone doesn't get this guide it's pretty much impossible to help that person xD.

great guide, but if something happens to the current db, how to i restore from sql?

Does this overwrite the files? I mean, If you backup every 3 hours will the second backup overwrite the first one?

Im still looking for the perfect backup method like explained on this post: http://forum.ragezone.com/mu-server-...ds-206598.html

I already have the program seted to upload the file to my hosting automaticly and would like to make a guide on how to have safe backups so people stop loosing their servers ^^

Edit: Added to Rz Guide Center

Quote:

---

Originally Posted by Dios

Does this overwrite the files?

---

Yes

PS: very neat guide center ^^

wow nice, there will be no problem regarding the lags and everything when this job is operating right?

yep, it take only 2-4 secs to fully backup the database.

on the job it says fail... i dont know why, i made a folder and put the database bacup on it,D:/muserver/db backs/muonline.bak, but still its failed, any posible problem on me?... i will appreciate someone help me...

make sure Sql Server Agent is Enable

When I finish all things by this guide (P.S. I Can't see the last pic) and I try to start the job it show no erorr just this (SQL Agent enabled).
http://img182.imageshack.us/img182/7711/untitledxh8.jpg

Quote:

---

Originally Posted by liselotte

make sure Sql Server Agent is Enable

---

Read above

Make your last picture work.. :bomb:

WTF

gnomiux
Ur rIGHT

Mines still don't work :punch: It shows no erorr but it wont start.. :bad:

well i have suggestion for you... just go to your database right click just like what you do when you are restoring and choose backup then point it to your backup files or MuOnline.bak then click schedule then choose when you want to set it up...

Thanks -Jumong-, you helped me alot :eek:, now it's working.. 8)

How to make backup if my server's dbs are on C:\muserver\db\
??

always same shit. Error icon and The job failed. Please help

change the Directory to C:\xxx\xxx instead of D:\xxx\xxx

oki thx :P

Nice guide, but how can I make a backup of the db on other pc (direct/static ip)

Thx!
:)

how to restore the backup file?

Quote:

---

Originally Posted by -Jumong-

well i have suggestion for you... just go to your database right click just like what you do when you are restoring and choose backup then point it to your backup files or MuOnline.bak then click schedule then choose when you want to set it up...

---

Thank you DUDE !!! now it worked :wiggle:

THX !

Quote:

---

Originally Posted by gnomiux

When I finish all things by this guide (P.S. I Can't see the last pic) and I try to start the job it show no erorr just this (SQL Agent enabled).
http://img182.imageshack.us/img182/7711/untitledxh8.jpg

---

I have this error too!

you must run SQL Agent first before starting the job. please read the guide carefully

Great work, liselotte. Thanks for a perfect guide....! Waitting for your next guides :)

good guide...Thanks!

goood nice

nice guide . Thanks!

purrfect thanks

Thanks!
Very useful =)

Thank You

Good Guide indeed

Quote:

---

Originally Posted by -Jumong-

well i have suggestion for you... just go to your database right click just like what you do when you are restoring and choose backup then point it to your backup files or MuOnline.bak then click schedule then choose when you want to set it up...

---

This works fast ... Though its manualy executed.. but its much easier.. i think

Hi lisotte, this is a very good guide but i followed carefully your guide and when i start the job it says the SQL Agent is not running, and then i Start SQL agent, nothing happens..The SQL Agent Icon still has the Red Square dot the signifies Stop or not running.. How come it is not running? Can u help me with this? are there any solutions for this? Thanks in advance and More Respect!

Quote:

---

Originally Posted by scudz69

Hi lisotte, this is a very good guide but i followed carefully your guide and when i start the job it says the SQL Agent is not running, and then i Start SQL agent, nothing happens..The SQL Agent Icon still has the Red Square dot the signifies Stop or not running.. How come it is not running? Can u help me with this? are there any solutions for this? Thanks in advance and More Respect!

---

have you tried to click the green triangle?

Quote:

---

Originally Posted by liselotte

have you tried to click the green triangle?

---

no, what i did is right click on the SQL Server Agent Icon then click on START, but it seems nothing happens, the Icon still has the Red Square, the Job is still not running. Pls Help.. Thanks!

how to restore ur database backup?

Thanks, very usefull guide 10/10

Thanks!

Very useful, thanks alot.

Quote:

---

DECLARE @file varchar(70)
SET @file = N'D:\BACKUP'+REPLACE(REPLACE(CAST(getdate() AS varchar), ' ', '_'), ':', '_')
SELECT @file
BACKUP DATABASE [MuOnline] TO DISK = @file WITH INIT , NOUNLOAD , NAME = N'MuOnline backup', NOSKIP , STATS = 10, NOFORMAT

---

Use this SQL Script ;)

does that going to replace the previous back up? or it will be named by date?

named by date ;)

ah ok, that cool. ill update my post with that query.

thanks

what about sql 2005?

just go to enterprice manager -> right click the DB -> all tasks -> backup database ^^

was it so hard? :P

well sir, people tend to be lazy sometimes and don't have the time to open and program and so it themself. instead, making a automated script helps them.

Just a single task and the job is done. Script run, db backup created.

how i restore the backup database?????????????????

how can i delete this query from database ?