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 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.
>|||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...
> 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.
>|||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_tran1.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...
> 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
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.
> >
> >|||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...
> > 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.
> >
> >
>|||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_tran1.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.
> >
> >
>|||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...
> 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...
> > > 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.
> > >
> > >
> >
> >
>|||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@.TK2MSFTNGP09.phx.gbl...
> > 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...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>|||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...
> > 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...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>