Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup taking
place at 10 PM?
Thanks in advance.You cannot do a restore to a database that is having a backup done at the
same time - and why would you?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup
taking
place at 10 PM?
Thanks in advance.|||Also, you will have to the use the previous full backup if you want to
restore the 10 p.m. transaction log backup
Ash
"sharman" wrote:
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup taking
> place at 10 PM?
> Thanks in advance.
>|||Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up these
new backups through Enterprise Manager( I have done it this way earlier and I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>|||Yes, you don't have to use the most recent full backup. You can use a
previous full backup and then all of the logs taken after that point in
time.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F39EAE37-615B-4AF7-BA67-13C56F34AB0B@.microsoft.com...
Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction
logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up
these
new backups through Enterprise Manager( I have done it this way earlier and
I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM
> how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>
Showing posts with label sp3. Show all posts
Showing posts with label sp3. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
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.
> > > >
> > > >
> > >
> > >
> >
> >
>
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.
> > > >
> > > >
> > >
> > >
> >
> >
>
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]
>
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]
>
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...[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...[vbcol=seagreen]
would[vbcol=seagreen]
network[vbcol=seagreen]
backup
>
|||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[vbcol=seagreen]
> 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...
> would
network
> backup
>
|||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...
> drive, then yes, you can use a
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> would
> network
> backup
>
|||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...
>
|||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...[vbcol=seagreen]
management,[vbcol=seagreen]
would[vbcol=seagreen]
done[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
drive?
>
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...[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...[vbcol=seagreen]
would[vbcol=seagreen]
network[vbcol=seagreen]
backup
>
|||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[vbcol=seagreen]
> 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...
> would
network
> backup
>
|||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...
> drive, then yes, you can use a
> news:u4aglSxUEHA.2520@.TK2MSFTNGP12.phx.gbl...
> would
> network
> backup
>
|||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...
>
|||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...[vbcol=seagreen]
management,[vbcol=seagreen]
would[vbcol=seagreen]
done[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
drive?
>
Subscribe to:
Posts (Atom)