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.
> > > >
> > > >
> > >
> > >
> >
> >
>
Showing posts with label frequently. Show all posts
Showing posts with label frequently. Show all posts
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 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?
>
Wednesday, March 7, 2012
Backup one server, restore to another
I frequently restore the backups of my production server to my standby/test
server. They (the servers) aren't completely identical, however. Production
has 3 logical disks for OS, data and logs while standby has only two disks.
The real problem though is the time it takes to do a restore using EM. It
seems that I have to restore the full backup then each transaction log
separately. It would be very convenient to be able to specify (using add
device) the full backup and all of the logs I want at once, then only have
to change the paths to the database and log files one time. I've read
through BOL but it is silent on this issue. Is it possible through EM? Or
should I be looking at a third-party app from e.g. Red Gate?You could write a SQL script that implements the WITH MOVE option of the
backup command. Enterprise Manager is a nice tool, but I haven't figured
out how to automate a series of mouse clicks. For repeatability, you have
to go to T_SQL scripts.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:e3vH%23OT0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>I frequently restore the backups of my production server to my standby/test
> server. They (the servers) aren't completely identical, however.
> Production
> has 3 logical disks for OS, data and logs while standby has only two
> disks.
> The real problem though is the time it takes to do a restore using EM. It
> seems that I have to restore the full backup then each transaction log
> separately. It would be very convenient to be able to specify (using add
> device) the full backup and all of the logs I want at once, then only have
> to change the paths to the database and log files one time. I've read
> through BOL but it is silent on this issue. Is it possible through EM? Or
> should I be looking at a third-party app from e.g. Red Gate?
>
server. They (the servers) aren't completely identical, however. Production
has 3 logical disks for OS, data and logs while standby has only two disks.
The real problem though is the time it takes to do a restore using EM. It
seems that I have to restore the full backup then each transaction log
separately. It would be very convenient to be able to specify (using add
device) the full backup and all of the logs I want at once, then only have
to change the paths to the database and log files one time. I've read
through BOL but it is silent on this issue. Is it possible through EM? Or
should I be looking at a third-party app from e.g. Red Gate?You could write a SQL script that implements the WITH MOVE option of the
backup command. Enterprise Manager is a nice tool, but I haven't figured
out how to automate a series of mouse clicks. For repeatability, you have
to go to T_SQL scripts.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:e3vH%23OT0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>I frequently restore the backups of my production server to my standby/test
> server. They (the servers) aren't completely identical, however.
> Production
> has 3 logical disks for OS, data and logs while standby has only two
> disks.
> The real problem though is the time it takes to do a restore using EM. It
> seems that I have to restore the full backup then each transaction log
> separately. It would be very convenient to be able to specify (using add
> device) the full backup and all of the logs I want at once, then only have
> to change the paths to the database and log files one time. I've read
> through BOL but it is silent on this issue. Is it possible through EM? Or
> should I be looking at a third-party app from e.g. Red Gate?
>
Backup one server, restore to another
I frequently restore the backups of my production server to my standby/test
server. They (the servers) aren't completely identical, however. Production
has 3 logical disks for OS, data and logs while standby has only two disks.
The real problem though is the time it takes to do a restore using EM. It
seems that I have to restore the full backup then each transaction log
separately. It would be very convenient to be able to specify (using add
device) the full backup and all of the logs I want at once, then only have
to change the paths to the database and log files one time. I've read
through BOL but it is silent on this issue. Is it possible through EM? Or
should I be looking at a third-party app from e.g. Red Gate?You could write a SQL script that implements the WITH MOVE option of the
backup command. Enterprise Manager is a nice tool, but I haven't figured
out how to automate a series of mouse clicks. For repeatability, you have
to go to T_SQL scripts.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:e3vH%23OT0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>I frequently restore the backups of my production server to my standby/test
> server. They (the servers) aren't completely identical, however.
> Production
> has 3 logical disks for OS, data and logs while standby has only two
> disks.
> The real problem though is the time it takes to do a restore using EM. It
> seems that I have to restore the full backup then each transaction log
> separately. It would be very convenient to be able to specify (using add
> device) the full backup and all of the logs I want at once, then only have
> to change the paths to the database and log files one time. I've read
> through BOL but it is silent on this issue. Is it possible through EM? Or
> should I be looking at a third-party app from e.g. Red Gate?
>
server. They (the servers) aren't completely identical, however. Production
has 3 logical disks for OS, data and logs while standby has only two disks.
The real problem though is the time it takes to do a restore using EM. It
seems that I have to restore the full backup then each transaction log
separately. It would be very convenient to be able to specify (using add
device) the full backup and all of the logs I want at once, then only have
to change the paths to the database and log files one time. I've read
through BOL but it is silent on this issue. Is it possible through EM? Or
should I be looking at a third-party app from e.g. Red Gate?You could write a SQL script that implements the WITH MOVE option of the
backup command. Enterprise Manager is a nice tool, but I haven't figured
out how to automate a series of mouse clicks. For repeatability, you have
to go to T_SQL scripts.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:e3vH%23OT0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>I frequently restore the backups of my production server to my standby/test
> server. They (the servers) aren't completely identical, however.
> Production
> has 3 logical disks for OS, data and logs while standby has only two
> disks.
> The real problem though is the time it takes to do a restore using EM. It
> seems that I have to restore the full backup then each transaction log
> separately. It would be very convenient to be able to specify (using add
> device) the full backup and all of the logs I want at once, then only have
> to change the paths to the database and log files one time. I've read
> through BOL but it is silent on this issue. Is it possible through EM? Or
> should I be looking at a third-party app from e.g. Red Gate?
>
Saturday, February 25, 2012
backup of database
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
Daily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh
|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
Daily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh
|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
backup of database
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
backup of database
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
--
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
MangeshDaily backups should suffice.
--
Keith
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
> Hi
> I am new to sqlserver.
> My question?
> How frequently is recommended to take the backup of
> model, master and msdb database.
> Right now I am backing up only the user databases and transaction logs.
> Please share your views.
> Thanks
> Mangesh|||The only requirement would be whenever there are modifications. As master
and model should not change frequently, very few backups are required;
usually only right after a system modification. However, this is sometimes
difficult to ascertain. So, we regularly run FULL database backups on a
nightly basis.
The msdb, however, and in contrast to the other two, is modified whenever
jobs are ran, which sould be daily. So, you might want to, at least, run a
FULL backup daily, and even a few DIFFERENTIALs throughout the day, if there
is a heavy load. Also, be aware, that the SQL Agent service will reset the
RECOVERY mode of msdb to SIMPLE whenever it is restarted. So, if you desire
to also perform meaningful transaction log backups, you would have to create
a startup job to reset the msdb back to FULL or BULK LOGGED RECOVERY.
Sincerely,
Anthony Thomas
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AD73ECD8-C5AD-4DEB-944E-2A39502F6AA3@.microsoft.com...
Hi
I am new to sqlserver.
My question?
How frequently is recommended to take the backup of
model, master and msdb database.
Right now I am backing up only the user databases and transaction logs.
Please share your views.
Thanks
Mangesh
Subscribe to:
Posts (Atom)