| Author |
Topic |
|
aki123
Starting Member
20 Posts |
 Posted - 2006-04-25 : 06:04:59
|
| hi alli have sql 2005 express.what do i need to backup to be able to do a good restoration?there are the system databases and the organization that i manually backed up. simply by right click and backup.but what about the database itself?should i stop the sql service and just copy the mdf and ldf file?does this work and what better ways are there?thanksaki |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-25 : 07:58:04
|
| not sure what you want to do and for what...backup like what you do with the system databasesdo a system backupafter determining how often you'd like to perform the above procedurecreate jobs that will automatically do this for you scheduled on the period you've set--------------------keeping it simple... |
 |
|
|
aki123
Starting Member
20 Posts |
Posted - 2006-04-25 : 08:41:10
|
| hi againi just dont understand a simple thingi have a sql server.when i go the the server managment i see the databases.there are system databases and "user" databases' witch i can install manauly' simply by right click them and backup.i already configured a device for each database, and backupto that device.before every backup i delete the old backup file.but there is also the batabase itself. the mdf and ldf file.and i need to backup them to.how to backup them?should i stop the sql services and copy them manualy.thos this work.? are there better options?what do i missing?thanksaki |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-25 : 09:21:04
|
when you do the backup as you described, you are backing up the mdf and ldf files  if you try to restore them on another server or database, you'll see what i mean--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-25 : 10:57:37
|
"should i stop the sql service and just copy the mdf and ldf file?"No, don't do that - otherwise you'll almost certainly be back here needing help when you have an urgent need to restore them!You should set up a scheduled backup - in SQL 2000 that was called a Maintenance Plan, I don't know the equivalent jargon in SQL2k5.You need to decide whether, say, a Daily Backup is sufficient - i.e. worst case you are prepared to lose a day's work - or whether you need to be able to restore to point-in-time.For a once-a-day process set up the maintenance plan to make a Full Backup once a day - preferably in the wee hours of the night when the database is quiet. SQL Server can happily take backups without being shut down. Make sure you set the database Recovery Model to Simple if you go down this route - otherwise your Log files (.LDF) will become huge!For a point-in-time disaster recovery plan you need to also set up Transaction Log Backups - if the business is critical then once every 10 minutes is a probably OK, if less critical then maybe once and hour is sufficient. Set the Recovery Model to FULL for this strategy.For Recovery you would then restore a Full Backup made at a time preceeding the failure, followed by all the subsequent TLog Backups, in turn, up to the point of failure (you can optionally restore to a given time - which can be part way through a TLog backup).So if you accidentally deleted all your customers  just after 4PM then you could restore to 15:59 ...The SQL 2000 Maintenance Wizard let you set up a single plan that would back up ALL User Databases - that route ensures that any new database will be automatically included in the backup strategy - not a bad idea!The Maintenance Wizard is not without its problems, and detractors - myself included - but its a pretty fair place to start.Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-25 : 11:17:38
|
Kristen... how about that blog of yours?? anytime soon?  Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-25 : 12:16:25
|
| "how about that blog of yours?? anytime soon?"Its all here mate, and in easy bite sized installments too!Kristen |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-04-25 : 14:08:38
|
quote:
in SQL 2000 that was called a Maintenance Plan, I don't know the equivalent jargon in SQL2k5.
Its easy ... there isn´t one in SQL2K5 Express  , gone is the Agent.You have to create a script and run it with Windows's Scheduler using Sqlcmd to run the script. MS has a link about how to do it, somewhere ...--This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-25 : 14:36:38
|
| Thanks PSamsig"Its easy ... there isn´t one in SQL2K5 Express , gone is the Agent."handy eh? So all the little guys who don't know how to look after their data don't have a Wizard to do it; and all the big guys who despise wizards get one to not use!Mind you, I could never get the Maintenance Plans to work on MSDE either.You do get what you pay for though ...Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-04-26 : 00:59:02
|
quote:
You should set up a scheduled backup - in SQL 2000 that was called a Maintenance Plan, I don't know the equivalent jargon in SQL2k5.
In 2k5, it's called a maintenance plan. How have you been Kristen? If you can hold for a little bit on Express, they're coming out with a new administrative tool anytime soon (or so I heard). It might have something to help you. Until then, you can do waht PSamsig said.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
aki123
Starting Member
20 Posts |
Posted - 2006-04-26 : 05:17:09
|
| thank u allu have been very helpfull.i restore like jen - dont honey me - said,and then i show those big files of mdf and ldf files.and it makes me wonder of the operation sql server makewith the files and databases he uses.thanks againaki |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-26 : 06:06:15
|
| sorry...you just made me smile with your response (but rest assured it was made in good mood) over copying the physical files, won't happen again--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-26 : 15:44:44
|
| "In 2k5, it's called a maintenance plan. How have you been Kristen?"I've been fine, but we only got one weeks skiing. And I still haven't been anywhere need SQL2k5 ... but then you knew that already, right?!Kristen |
|
|
|
|
sql backup