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 th
e
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.
ArcherYes, You can restore the backup to any other server.
See this article:-
http://www.databasejournal.com/feat...cle.php/2232371
Thanks
Hari
SQL Server MVP
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:31ECF89A-A1E8-40C3-8934-406227006912@.microsoft.com...
>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|||I have been struggling for half a day just trying to backup one database fro
m
my server and restoring 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 lik
e
'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
"Hari Prasad" wrote:

> Yes, You can restore the backup to any other server.
> See this article:-
> http://www.databasejournal.com/feat...cle.php/2232371
> Thanks
> Hari
> SQL Server MVP
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
> news:31ECF89A-A1E8-40C3-8934-406227006912@.microsoft.com...
>
>|||"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:55F4FCE1-0CD9-4C58-A698-2BBBFCF68AA6@.microsoft.com...
>I have been struggling for half a day just trying to backup one database
>from
> my server and restoring 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 ...'
Do you have an E: drive on your laptop? My guess is no.
From the EM backup dialog, click the Options tab, and change the path[s]
for
the file[s] destination[s] accordingly.

> Why is it so easy to detach and reattach a db, but so difficult to use the
> backup/restore functions of SQL Server 2000?
It really isn't... sometimes it helps to actually read the error message.
:-)
-Mark
[vbcol=seagreen]
> Please HELP!!!
> Archer
>
> "Hari Prasad" wrote:
>|||bagman3rd wrote:[vbcol=seagreen]
> I have been struggling for half a day just trying to backup one database f
rom
> my server and restoring 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 lapt
op
> and try to restore to SQL Server on my laptop. I keep getting some error l
ike
> '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
>
> "Hari Prasad" wrote:
>
If you want to restore a database to another machine (whether it be a
server or not) the simplest way forward is to create a blank database on
the desktop/server concerned via EM then use the restore database
function and set the "force overwrite" option to on.
The error you get is because a straight forward restore will try to do
so using the exact file path of the original, thus if the mdf was
originally stored on a path of E:\SQL2K\MSSQL\Data\Database.mdf, that
is where it will try to restore to and fail if your laptop does not have
such a path/drive letter/foldername etc available to it.
Hope this helps
Regards
Dazza
SQL DBA