Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jorolas
Starting Member
4 Posts |
 Posted - 2008-12-16 : 05:31:56
|
| Hi everybody. I am new to SQL, so I have some problems which I am trying to solve step by step. One of my main issues, is related to backup procedures. I have been reading a lot of doc from MS knowledge base, so I think I am more or less able to use Management Studio for facing backups either basic, complete, with/without transactions, full recovery models/basic recovery models and so on.I am wondering if instead of using basic backup copy of a DDBB from management studio, I could use external application (ex: Windows backup utility) for backing up .mdf and .ldf files from folder MSSQL\Data. I mean, Could I backup and recover later my DDBB just coping/pasting these files, in case the only thing I want to do is a Basic Backup in a basic recovery model?Thanks in advance for your time, Jorge |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-12-16 : 06:18:53
|
| To make it simple: no. A simple file backup of the .mdf/.ldf files will not work, you will have to do sql server backups.- Lumbago |
 |
|
|
JamesRyan
Starting Member
18 Posts |
Posted - 2008-12-16 : 07:23:34
|
| Hey thereSSMS is the place for your SQL backups, have a read up on Maintenance Plans - extrememly effective and easy to use.The trouble with backing up your mdf/ldf directly is that they will still be attached to the database and therefore your backup sw will likely return with an error about exclusive access. To get round this you would have to first detach them, which would then leave you offline for a period.SQL backup files are usually compressed to about a third of the original file size so thats useful for housekeeping. The real key though is the use of differential backups, which record only the changes since the last full backup and could therefore vastly reduce the load that backup procedures place on your environment.The point is that you should use SQL backups, which you produce through your maintenance plans.http://www.sqlhowto.co.uk/post/how-can-i-design-a-backup-strategy-.html&page=Hope this helps...James Ryanwww.sqlhowto.co.uk |
|
|
|
jorolas
Starting Member
4 Posts |
Posted - 2008-12-16 : 07:45:46
|
| Ok, understand, I will do it that way then. Thanks everybody, Best regards, Jorge |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-16 : 09:14:39
|
quote:
Originally posted by JamesRyan
Hey thereSSMS is the place for your SQL backups, have a read up on Maintenance Plans - extrememly effective and easy to use.The trouble with backing up your mdf/ldf directly is that they will still be attached to the database and therefore your backup sw will likely return with an error about exclusive access. To get round this you would have to first detach them, which would then leave you offline for a period.SQL backup files are usually compressed to about a third of the original file size so thats useful for housekeeping. The real key though is the use of differential backups, which record only the changes since the last full backup and could therefore vastly reduce the load that backup procedures place on your environment.The point is that you should use SQL backups, which you produce through your maintenance plans.Hope this helps...James Ryanwww.sqlhowto.co.uk
That is if you SQL 2008 |
|
|
|
JamesRyan
Starting Member
18 Posts |
Posted - 2008-12-16 : 09:59:53
|
| I use SQL2005... and a 45 gig database file backs up to 16 gig. So 2005 does compress back up files as part of the process.I think you may be thinking about Data Compression (including Backup Compression) which was only introduced in SQL 2008.James Ryanwww.sqlhowto.co.uk |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-16 : 10:01:22
|
| Ok.You mean unused space. |
|
|
|
|
|
|
|
|
Question about backup procedures