backup question

sql server 2000 sp3.
I would like to backup a database frequently, maybe every 1/2 hour. I would
like to backup to a file. Can a create a backup device (file) on a network
server so the backup goes directly to another server? Or do I have to backup
to the lcoal hard drive and schedule a script to copy it off the drive?
any info is appreciated. thanks.What you can do is map a network drive on your server, then you can
create a backup device on that mapped drive
Eric Li
SQL DBA
MCDBA
djc wrote:

> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I woul
d
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to back
up
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||If the service account for the SQL Server service has permissions on the dri
ve, then yes, you can use a
network drive. Address it using UNC naming.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...[vbc
ol=seagreen]
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I woul
d
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to back
up
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>[/vbcol]|||Hi,
Do not perform the FULL database backup every 30 minutes. This will create
issue once your
database grow big. I recommend you to do a transaction log backup every 30
minutes to a remote server and
perform the FULL database backup once every night.
Prerequsites to do a backup remotely
---
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Transaction log backup
--
BACKUP log <dbname> to disk='\\computername\sharename\dbname_tr
an1.trn'
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
MCDBA
"djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> sql server 2000 sp3.
> I would like to backup a database frequently, maybe every 1/2 hour. I
would
> like to backup to a file. Can a create a backup device (file) on a network
> server so the backup goes directly to another server? Or do I have to
backup
> to the lcoal hard drive and schedule a script to copy it off the drive?
> any info is appreciated. thanks.
>|||thats what I was thinking. Will I need to log on as the SQLServer Service
account and map the drive? In other words, I was concerned that if I map the
drive under my account it would not exist under the context of the scheduled
task?
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eB2F$XxUEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> What you can do is map a network drive on your server, then you can
> create a backup device on that mapped drive
> --
> Eric Li
> SQL DBA
> MCDBA
> djc wrote:
>
would[vbcol=seagreen]
network[vbcol=seagreen]
backup[vbcol=seagreen]|||thanks for the reply.
can I do this through GUI Enterprise manager? (under database, management,
backup) I am not very familiar with TSQL at this point.
I think I tried to create a new backup device using a UNC name and it would
not let me? (which leads me to believe what your refering to has to be done
using TSQL)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> If the service account for the SQL Server service has permissions on the
drive, then yes, you can use a
> network drive. Address it using UNC naming.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
would[vbcol=seagreen]
network[vbcol=seagreen]
backup[vbcol=seagreen]
>|||thanks for the reply. Is using TSQL the only way to accomplish this? or
could I use enterprise manager gui. I'm not that familiar with TSQL at this
point.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OfzbFf4UEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do not perform the FULL database backup every 30 minutes. This will create
> issue once your
> database grow big. I recommend you to do a transaction log backup every 30
> minutes to a remote server and
> perform the FULL database backup once every night.
> Prerequsites to do a backup remotely
> ---
> There are Few Pre requisites to do backup remotely;
> 1. You Should start SQL server using Domain user who got access to remote
> machine Share
> 2. Should have share in the remote machine
> 3. If you need to schedule this as a job then SQL Agent should use the
same
> Domain user in which SQL server was started
> 4. Restart the services
> Now you can execute the Backup script with UNC path
> BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with
> init
> Transaction log backup
> --
> BACKUP log <dbname> to disk='\\computername\sharename\dbname_tr
an1.trn'
> Note:
> Backup to remote machine will not work if you start SQL server using Local
> system account
> Thanks
> Hari
> MCDBA
>
>
> "djc" <noone@.nowhere.com> wrote in message
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> would
network[vbcol=seagreen]
> backup
>|||Yep, you can create the backup device using EM (I just tried it), you just h
ave to type the full UNC name, and
ignore the warning that EM cannot confirm that you have access to the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...[vbc
ol=seagreen]
> thanks for the reply.
> can I do this through GUI Enterprise manager? (under database, management,
> backup) I am not very familiar with TSQL at this point.
> I think I tried to create a new backup device using a UNC name and it woul
d
> not let me? (which leads me to believe what your refering to has to be don
e
> using TSQL)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u6BUZaxUEHA.644@.tk2msftngp13.phx.gbl...
> drive, then yes, you can use a
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> would
> network
> backup
>[/vbcol]|||I should have added that your problem can very well be permissions problem.
Check out the service account for
SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just have to
type the full UNC name,
and
> ignore the warning that EM cannot confirm that you have access to the file
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message news:u1np1n5UEHA.1356@.TK2MSFTNG
P09.phx.gbl...
>|||ok. Great. I had only very quickly given it one attempt before... and I had
an administrative share in the path (\\server\d$ ...etc...) so maybe that
had something to do with it as well. I will also setup the appropriate
permissions for the sql service account... Thanks for the info!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ef5Wi15UEHA.544@.TK2MSFTNGP11.phx.gbl...
> Yep, you can create the backup device using EM (I just tried it), you just
have to type the full UNC name, and
> ignore the warning that EM cannot confirm that you have access to the
file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:u1np1n5UEHA.1356@.TK2MSFTNGP09.phx.gbl...
management,[vbcol=seagreen]
would[vbcol=seagreen]
done[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
drive?[vbcol=seagreen]
>