SQL Server Performance Forum – Threads Archive

Backup questions

I was wondering if someone could point me in the right direction and/or give
me a quick breakdown on SQL Server backups. I work in Environmental
Consulting, and all of my databases are geochemical/hydrogeological
databases. None of them are very big(300 megs for all my databases), and the
data rarely changes. I do add data to the databases when it comes in, but I
do have sp’s that I am updating/creating on a weekly basis. I really only
need to backup my databases once a week or maybe twice a month, and I have
been shutting down the SQL Service Manager, copying the db files to cd, then
restarting the SQL service. My sys admin wants to use the backup procedure
within SQL Server, but I really dont understand how the restoring works. If
you use the backup procedures within SQL Server, and your physical server
blows up, can you restore the db’s on another machine. How does this work?
I really like being able to have two detached database files which can be
re-attached to any machine, but do not understand how to restore the backup
db files, or how this works even. Thanks for you help. Archer

Is no easy, in one post, cover all kind of backups. I suggest to read Backups in Books on Line, and came back with specific questions. In short, you can restore backup in any box with same sql version. Just copy backup in new server and restore. May be you have to move (i.e. if original path was F: and in new server is H<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I do not need to cover all backups. I want a simple full backup of all databases once a week. I have been struggling for half a day just trying to backup one database from my server and retoring the db to my laptop. I cannot understand why this is so freakin difficult!!! I am using EM. I use the ‘backup db’ to backup to a file called ‘wrc_test.bak’, then I move the ‘wrc_test.bak’ file to my laptop and try to restore to SQL Server on my laptop. I keep getting some error like ‘Device Activation Error. The physical file name ‘E:wrc_test_mdf may be incorrect …’ Why is it so easy to detach and reattach a db, but so difficult to use the backup/restore functions of SQL Server 2000??? Please HELP!!! Archer

‘E:wrc_test_mdf may be incorrect …’
That’s is what I said in first post. Do you have E: in your laptop?
If not, using EM, you have to change E: to any valid drive and that is all, and of course no difficult at all.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

IF you want to attach the database then it must be detached previously, otherwise you may get similar device activation errors. For the better understanding of BACKUP/RESTORE process as suggested Books onlne is the best resource. If the database size is not more 700 MB then you can write the BACKUP file to a CD and copy the same file on the laptop to restore it. Perform a local backup on the server to a file and then burn it to the CD, then do the rest.
HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I changed the destination directory in the EM gui and it worked fine. Thanks for the help. Archer

BP tip: I suggest you to use the BACKUP/RESTORE statements rather than depending upon the GUI to do the work, this way you can be confident about the process. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>