[RESOLVED] Database Backup Strategy

[ovi_gm]

Hello,

I came across some documentation on msdn about some backup strategies.
I am pretty new to sql so all that information seems very complicated.

As far as I understood a very simple way to backup the database is to simply copy the mdf and the ldf file to a backup directory, but this could be done safely ONLY if the access to the database is blocked before the backup begins and until it is finished.

Now, I have an application for my company which is like a project organizer and information for each separate project. The database is pretty small and after 4 o clock p.m. the "off peak" period means zero access because everybody goes home. So my database isn't on the internet, it is on a local server and is accessed by 5 to 6 computers. After 4 o'clock there is the possibility to stop any access and make the backup.

Keeping this in mind, is it safe to back up like that or not? If not please explain me why. And if it is safe, is there some code to shut down the sql server and start it up again at runtime? Or is it enough just to stop all the reading / writing operations in my app during backup,without stopping the sql server?

I am using VB2010 express and SQL Server 2008R2.

Thx.

[techgnome]

Personally... I don't think it's safe... because of the reasons you stated... it's jsut too risky...

What you can (and should do, and I recommend) is to set up a Maintenance Plan from Management Studio, there's a number of options available to you, including making regular backups... just schedule it for the over night time, when the database isn't in use or use is low. The advantage of using the built-in backup mechanism is that you don't need to take the db offline... it can happen while people are even using it. Example - in a prior life, we'd setup our clients with hourly transactional backup, with a daily (at night) full back up...

-tg

[ovi_gm]

Well, the fact is I have no idea how to do that. I suppose I have to work in SQL Server Management Studio but I wouldn't know where to start.

I tried to attach my database but with no success. Right now I don't know how it works so I would have to study it and that takes some time.

But please tell me what are the risks of copying this file? I mean it's just a file. Why shouldn't it be ok? If there is absolutely no reading or writing at the time the backup is made... what could possibly happen?

I really don't know the exact mechanism of sql server and that's why I'm so confused. This is my first app using databases.

First I wanted to develop my app using excel worksheets but somebody on the forum enlightened me and I realized that using a database it's much better and safer.

But now I am really confused with this backup thing and I really want to keep my data safe.

So maybe someone can explain me...why is this so risky at a time where there are zero operations over the database.

[ovi_gm]

Ok. I entered SQL Server Management Studio. I have a window "Connect to server". I have server type: Database engine & SQL Server Compact. I tried both these options and for Server name I have entered Browse for more and the way to my mdf file. Then Windows Authentication - gives me error. Then I tried SQL Server Authentication - also error.

Maybe I'm on the wrong track. Any guidance here?

[ovi_gm]

Anyone?

[FunkyDexter]

But please tell me what are the risks of copying this file?

It's not so much that it's risky, it's just alot more akward than using proper back ups. I'd second TG that proper backups is what you should be doing.

Ok. I entered SQL Server Management Studio. I have a window "Connect to server". I have server type: Database engine & SQL Server Compact. I tried both these options and for Server name I have entered Browse for more and the way to my mdf file. Then Windows Authentication - gives me error. Then I tried SQL Server Authentication - also error.

OK, this might get a little tricky to follow but really shouldn't be too bad. From I've seen of your questions you're going to be fine following this and are really going to thank us once you've done it.

All your trying to do is connect Management studio to a database SERVER (nb. not an individual database), once you've done that you'll see all the databases that are on the server along with a bunch of options to manage them, one of which will be to back it up and/or create a scheduled plan to automatically back it up regularly.

Here's a few questions that will help us answer you:-
Do you know what type of sql server you're using? Did you use compact or did you use a "normal" sql server?
How did you create the database in the first place?
Do you connect to theis database with an application and, if you do, do you have the connection string that the application uses?

edit>just spotted in your first post that you're using sql server 2008, that answers question 1. In that case the option you should be picking is "Database Engine", ignore the compact edition stuff. Now if you can identify the connection string you're using from visual studio that will contain the server name you'll need to enter in the server box.

Then we're going to need to get into the Authentication model you're using. When you installed sql server it asked you whether you wanted to use Windows Authentication or mixed mode, do you remember what you picked? You will have been asked to set an SA password, do you remember what you set (DON'T post it here - just confirm whether you know it or not)

[ovi_gm]

Finally I did it. Thank you all for your answers.

I managed to connect to the server and attach the database. I also found the backup section.

Now I will have to make some tests and see if it works ok.

Thx again.

Cheers