Is this the correct NG for a backup Q?
Right now I'm wondering if the only way to copy a backup file from one serve
r to
another is by DTS(?)
1. I'd like to schedule a back from one server to another recieving server t
o be
used in an emergency.
2. I'd like to be able to automate the restore of that database on the recie
ving
emergency server to reduce recovery time in the event of the primary server
becoming unavailable.
TIA
JeffP...> Right now I'm wondering if the only way to copy a backup file from one
> server to
> another is by DTS(?)
No. You can use batch files or ActiveX scripts (FileSystem object) - both
of which can be scheduled as SQL jobs.
You might look into Log Shipping - not automated for the restore but for the
backup and copy including transaction log backups.
HTH
Jerry
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ewfxk$dfGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Is this the correct NG for a backup Q?
> Right now I'm wondering if the only way to copy a backup file from one
> server to
> another is by DTS(?)
> 1. I'd like to schedule a back from one server to another recieving server
> to be
> used in an emergency.
> 2. I'd like to be able to automate the restore of that database on the
> recieving
> emergency server to reduce recovery time in the event of the primary
> server
> becoming unavailable.
> TIA
> JeffP...
>|||You can take backup directly on remote server. You need to share drive
on remote server with proper permission.
Also you can use net use and map a drive at time of backup.
For high availability use log shipping , replication or if you are
using SQL Server 2005 you can use mirroring.
Regards
Amish Shah.
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Tuesday, March 20, 2012
backup db occurs error
Today I backup the SQLServer7.0,it prompt me error:
"Microsoft SQL-DMO(ODBC SQLState:42000)
Backup,CHECKALLOC,bulk copy,SELECT INTO,and file manipulation(such as CREATE FILE) operations on a database must be serialized.
Reissue the statement after the current backup,CHECKALLOC,or file manipulation operation is completed.
Backup or RESTORE OPERATION terminating abnormally."
Pls help me. Thanks with best regards.The error simply means that at the time the backup dump operation was attempted it could not be executed (because of other activity in progress).
Often, this is a result of attempting to run backups when maintenance is in progress.
If that is the issue try to run or schedule backup dumps at times when DB maintenance is complete (not in progress).sql
"Microsoft SQL-DMO(ODBC SQLState:42000)
Backup,CHECKALLOC,bulk copy,SELECT INTO,and file manipulation(such as CREATE FILE) operations on a database must be serialized.
Reissue the statement after the current backup,CHECKALLOC,or file manipulation operation is completed.
Backup or RESTORE OPERATION terminating abnormally."
Pls help me. Thanks with best regards.The error simply means that at the time the backup dump operation was attempted it could not be executed (because of other activity in progress).
Often, this is a result of attempting to run backups when maintenance is in progress.
If that is the issue try to run or schedule backup dumps at times when DB maintenance is complete (not in progress).sql
Monday, March 19, 2012
Backup Database or Copy
Using Sql Server 2000, is it better to use the Backup Database from EM and
schedular to do backups. I always do Full backups as incremental seems more
of a problem for the size database we are dealing with.
I was curious if that is better than just copying the .mdf and ldf.
At the moment, we do the Sql Server Full Backup and then our Veritas backup
program copies that backup to another Server.
The question is what is the drawback to just using Veritas to copy the mdf
and ldf to a couple of different servers (and an external drive that we take
off site).
Thanks,
Tom
You will likely run into problems getting Microsoft and/or VERITAS to
support a problem should you have one.
I don't know a lot about VERITAS, but it sounds like you have to detach the
database to do the copy. This will result in the database going off-line -
which normally is not desired.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>
|||In addition to what Hilary said, the database files (mdf, ldf, etc) are
usually larger than the database backup file, sometime much larger when you
have a lot of free space inside the database.
Linchi
"Hilary Cotter" wrote:
> You will likely run into problems getting Microsoft and/or VERITAS to
> support a problem should you have one.
> I don't know a lot about VERITAS, but it sounds like you have to detach the
> database to do the copy. This will result in the database going off-line -
> which normally is not desired.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
>
|||Hello,
Just copying MDF and LDF is not a safe method to backup. If you do a detach
and copy the MDF and LDF then it is safe.
So do:-
1. Detach the database
2. COpy the MDF and LDF to a new folder
3. Attach the database
4. Use Veritas to backup the MDF and LDF
Still I suggest you to do below as a backup strategy:-
1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
2. Do a FULL database backup during weekend [BACKUP DATABASE]
Thanks
Hari
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uZXmfKqPHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Just copying MDF and LDF is not a safe method to backup. If you do a
> detach and copy the MDF and LDF then it is safe.
> So do:-
> 1. Detach the database
> 2. COpy the MDF and LDF to a new folder
> 3. Attach the database
> 4. Use Veritas to backup the MDF and LDF
> Still I suggest you to do below as a backup strategy:-
> 1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
> 2. Do a FULL database backup during weekend [BACKUP DATABASE]
Actually, I do a full backup each night.
Any reason why I shouldn't do that?
Thanks,
Tom
> Thanks
> Hari
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
schedular to do backups. I always do Full backups as incremental seems more
of a problem for the size database we are dealing with.
I was curious if that is better than just copying the .mdf and ldf.
At the moment, we do the Sql Server Full Backup and then our Veritas backup
program copies that backup to another Server.
The question is what is the drawback to just using Veritas to copy the mdf
and ldf to a couple of different servers (and an external drive that we take
off site).
Thanks,
Tom
You will likely run into problems getting Microsoft and/or VERITAS to
support a problem should you have one.
I don't know a lot about VERITAS, but it sounds like you have to detach the
database to do the copy. This will result in the database going off-line -
which normally is not desired.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>
|||In addition to what Hilary said, the database files (mdf, ldf, etc) are
usually larger than the database backup file, sometime much larger when you
have a lot of free space inside the database.
Linchi
"Hilary Cotter" wrote:
> You will likely run into problems getting Microsoft and/or VERITAS to
> support a problem should you have one.
> I don't know a lot about VERITAS, but it sounds like you have to detach the
> database to do the copy. This will result in the database going off-line -
> which normally is not desired.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
>
|||Hello,
Just copying MDF and LDF is not a safe method to backup. If you do a detach
and copy the MDF and LDF then it is safe.
So do:-
1. Detach the database
2. COpy the MDF and LDF to a new folder
3. Attach the database
4. Use Veritas to backup the MDF and LDF
Still I suggest you to do below as a backup strategy:-
1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
2. Do a FULL database backup during weekend [BACKUP DATABASE]
Thanks
Hari
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uZXmfKqPHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Just copying MDF and LDF is not a safe method to backup. If you do a
> detach and copy the MDF and LDF then it is safe.
> So do:-
> 1. Detach the database
> 2. COpy the MDF and LDF to a new folder
> 3. Attach the database
> 4. Use Veritas to backup the MDF and LDF
> Still I suggest you to do below as a backup strategy:-
> 1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
> 2. Do a FULL database backup during weekend [BACKUP DATABASE]
Actually, I do a full backup each night.
Any reason why I shouldn't do that?
Thanks,
Tom
> Thanks
> Hari
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
Backup Database or Copy
Using Sql Server 2000, is it better to use the Backup Database from EM and
schedular to do backups. I always do Full backups as incremental seems more
of a problem for the size database we are dealing with.
I was curious if that is better than just copying the .mdf and ldf.
At the moment, we do the Sql Server Full Backup and then our Veritas backup
program copies that backup to another Server.
The question is what is the drawback to just using Veritas to copy the mdf
and ldf to a couple of different servers (and an external drive that we take
off site).
Thanks,
TomYou will likely run into problems getting Microsoft and/or VERITAS to
support a problem should you have one.
I don't know a lot about VERITAS, but it sounds like you have to detach the
database to do the copy. This will result in the database going off-line -
which normally is not desired.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||In addition to what Hilary said, the database files (mdf, ldf, etc) are
usually larger than the database backup file, sometime much larger when you
have a lot of free space inside the database.
Linchi
"Hilary Cotter" wrote:
> You will likely run into problems getting Microsoft and/or VERITAS to
> support a problem should you have one.
> I don't know a lot about VERITAS, but it sounds like you have to detach th
e
> database to do the copy. This will result in the database going off-line -
> which normally is not desired.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
>|||Hello,
Just copying MDF and LDF is not a safe method to backup. If you do a detach
and copy the MDF and LDF then it is safe.
So do:-
1. Detach the database
2. COpy the MDF and LDF to a new folder
3. Attach the database
4. Use Veritas to backup the MDF and LDF
Still I suggest you to do below as a backup strategy:-
1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
2. Do a FULL database backup during weekend [BACKUP DATABASE]
Thanks
Hari
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uZXmfKqPHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Just copying MDF and LDF is not a safe method to backup. If you do a
> detach and copy the MDF and LDF then it is safe.
> So do:-
> 1. Detach the database
> 2. COpy the MDF and LDF to a new folder
> 3. Attach the database
> 4. Use Veritas to backup the MDF and LDF
> Still I suggest you to do below as a backup strategy:-
> 1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
> 2. Do a FULL database backup during weekend [BACKUP DATABASE]
Actually, I do a full backup each night.
Any reason why I shouldn't do that?
Thanks,
Tom
> Thanks
> Hari
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
schedular to do backups. I always do Full backups as incremental seems more
of a problem for the size database we are dealing with.
I was curious if that is better than just copying the .mdf and ldf.
At the moment, we do the Sql Server Full Backup and then our Veritas backup
program copies that backup to another Server.
The question is what is the drawback to just using Veritas to copy the mdf
and ldf to a couple of different servers (and an external drive that we take
off site).
Thanks,
TomYou will likely run into problems getting Microsoft and/or VERITAS to
support a problem should you have one.
I don't know a lot about VERITAS, but it sounds like you have to detach the
database to do the copy. This will result in the database going off-line -
which normally is not desired.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||In addition to what Hilary said, the database files (mdf, ldf, etc) are
usually larger than the database backup file, sometime much larger when you
have a lot of free space inside the database.
Linchi
"Hilary Cotter" wrote:
> You will likely run into problems getting Microsoft and/or VERITAS to
> support a problem should you have one.
> I don't know a lot about VERITAS, but it sounds like you have to detach th
e
> database to do the copy. This will result in the database going off-line -
> which normally is not desired.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
>|||Hello,
Just copying MDF and LDF is not a safe method to backup. If you do a detach
and copy the MDF and LDF then it is safe.
So do:-
1. Detach the database
2. COpy the MDF and LDF to a new folder
3. Attach the database
4. Use Veritas to backup the MDF and LDF
Still I suggest you to do below as a backup strategy:-
1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
2. Do a FULL database backup during weekend [BACKUP DATABASE]
Thanks
Hari
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uZXmfKqPHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Just copying MDF and LDF is not a safe method to backup. If you do a
> detach and copy the MDF and LDF then it is safe.
> So do:-
> 1. Detach the database
> 2. COpy the MDF and LDF to a new folder
> 3. Attach the database
> 4. Use Veritas to backup the MDF and LDF
> Still I suggest you to do below as a backup strategy:-
> 1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
> 2. Do a FULL database backup during weekend [BACKUP DATABASE]
Actually, I do a full backup each night.
Any reason why I shouldn't do that?
Thanks,
Tom
> Thanks
> Hari
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
Backup Database or Copy
Using Sql Server 2000, is it better to use the Backup Database from EM and
schedular to do backups. I always do Full backups as incremental seems more
of a problem for the size database we are dealing with.
I was curious if that is better than just copying the .mdf and ldf.
At the moment, we do the Sql Server Full Backup and then our Veritas backup
program copies that backup to another Server.
The question is what is the drawback to just using Veritas to copy the mdf
and ldf to a couple of different servers (and an external drive that we take
off site).
Thanks,
TomYou will likely run into problems getting Microsoft and/or VERITAS to
support a problem should you have one.
I don't know a lot about VERITAS, but it sounds like you have to detach the
database to do the copy. This will result in the database going off-line -
which normally is not desired.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||In addition to what Hilary said, the database files (mdf, ldf, etc) are
usually larger than the database backup file, sometime much larger when you
have a lot of free space inside the database.
Linchi
"Hilary Cotter" wrote:
> You will likely run into problems getting Microsoft and/or VERITAS to
> support a problem should you have one.
> I don't know a lot about VERITAS, but it sounds like you have to detach the
> database to do the copy. This will result in the database going off-line -
> which normally is not desired.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> > Using Sql Server 2000, is it better to use the Backup Database from EM and
> > schedular to do backups. I always do Full backups as incremental seems
> > more of a problem for the size database we are dealing with.
> >
> > I was curious if that is better than just copying the .mdf and ldf.
> >
> > At the moment, we do the Sql Server Full Backup and then our Veritas
> > backup program copies that backup to another Server.
> >
> > The question is what is the drawback to just using Veritas to copy the mdf
> > and ldf to a couple of different servers (and an external drive that we
> > take off site).
> >
> > Thanks,
> >
> > Tom
> >
>
>|||Hello,
Just copying MDF and LDF is not a safe method to backup. If you do a detach
and copy the MDF and LDF then it is safe.
So do:-
1. Detach the database
2. COpy the MDF and LDF to a new folder
3. Attach the database
4. Use Veritas to backup the MDF and LDF
Still I suggest you to do below as a backup strategy:-
1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
2. Do a FULL database backup during weekend [BACKUP DATABASE]
Thanks
Hari
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uZXmfKqPHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Just copying MDF and LDF is not a safe method to backup. If you do a
> detach and copy the MDF and LDF then it is safe.
> So do:-
> 1. Detach the database
> 2. COpy the MDF and LDF to a new folder
> 3. Attach the database
> 4. Use Veritas to backup the MDF and LDF
> Still I suggest you to do below as a backup strategy:-
> 1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
> 2. Do a FULL database backup during weekend [BACKUP DATABASE]
Actually, I do a full backup each night.
Any reason why I shouldn't do that?
Thanks,
Tom
> Thanks
> Hari
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>> Using Sql Server 2000, is it better to use the Backup Database from EM
>> and schedular to do backups. I always do Full backups as incremental
>> seems more of a problem for the size database we are dealing with.
>> I was curious if that is better than just copying the .mdf and ldf.
>> At the moment, we do the Sql Server Full Backup and then our Veritas
>> backup program copies that backup to another Server.
>> The question is what is the drawback to just using Veritas to copy the
>> mdf and ldf to a couple of different servers (and an external drive that
>> we take off site).
>> Thanks,
>> Tom
>
schedular to do backups. I always do Full backups as incremental seems more
of a problem for the size database we are dealing with.
I was curious if that is better than just copying the .mdf and ldf.
At the moment, we do the Sql Server Full Backup and then our Veritas backup
program copies that backup to another Server.
The question is what is the drawback to just using Veritas to copy the mdf
and ldf to a couple of different servers (and an external drive that we take
off site).
Thanks,
TomYou will likely run into problems getting Microsoft and/or VERITAS to
support a problem should you have one.
I don't know a lot about VERITAS, but it sounds like you have to detach the
database to do the copy. This will result in the database going off-line -
which normally is not desired.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||In addition to what Hilary said, the database files (mdf, ldf, etc) are
usually larger than the database backup file, sometime much larger when you
have a lot of free space inside the database.
Linchi
"Hilary Cotter" wrote:
> You will likely run into problems getting Microsoft and/or VERITAS to
> support a problem should you have one.
> I don't know a lot about VERITAS, but it sounds like you have to detach the
> database to do the copy. This will result in the database going off-line -
> which normally is not desired.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> > Using Sql Server 2000, is it better to use the Backup Database from EM and
> > schedular to do backups. I always do Full backups as incremental seems
> > more of a problem for the size database we are dealing with.
> >
> > I was curious if that is better than just copying the .mdf and ldf.
> >
> > At the moment, we do the Sql Server Full Backup and then our Veritas
> > backup program copies that backup to another Server.
> >
> > The question is what is the drawback to just using Veritas to copy the mdf
> > and ldf to a couple of different servers (and an external drive that we
> > take off site).
> >
> > Thanks,
> >
> > Tom
> >
>
>|||Hello,
Just copying MDF and LDF is not a safe method to backup. If you do a detach
and copy the MDF and LDF then it is safe.
So do:-
1. Detach the database
2. COpy the MDF and LDF to a new folder
3. Attach the database
4. Use Veritas to backup the MDF and LDF
Still I suggest you to do below as a backup strategy:-
1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
2. Do a FULL database backup during weekend [BACKUP DATABASE]
Thanks
Hari
"tshad" <t@.home.com> wrote in message
news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Using Sql Server 2000, is it better to use the Backup Database from EM and
> schedular to do backups. I always do Full backups as incremental seems
> more of a problem for the size database we are dealing with.
> I was curious if that is better than just copying the .mdf and ldf.
> At the moment, we do the Sql Server Full Backup and then our Veritas
> backup program copies that backup to another Server.
> The question is what is the drawback to just using Veritas to copy the mdf
> and ldf to a couple of different servers (and an external drive that we
> take off site).
> Thanks,
> Tom
>|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uZXmfKqPHHA.3668@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Just copying MDF and LDF is not a safe method to backup. If you do a
> detach and copy the MDF and LDF then it is safe.
> So do:-
> 1. Detach the database
> 2. COpy the MDF and LDF to a new folder
> 3. Attach the database
> 4. Use Veritas to backup the MDF and LDF
> Still I suggest you to do below as a backup strategy:-
> 1. Do a transaction log backup every 30 minutes or so [BACKUP LOG]
> 2. Do a FULL database backup during weekend [BACKUP DATABASE]
Actually, I do a full backup each night.
Any reason why I shouldn't do that?
Thanks,
Tom
> Thanks
> Hari
>
> "tshad" <t@.home.com> wrote in message
> news:uBld7JpPHHA.1240@.TK2MSFTNGP03.phx.gbl...
>> Using Sql Server 2000, is it better to use the Backup Database from EM
>> and schedular to do backups. I always do Full backups as incremental
>> seems more of a problem for the size database we are dealing with.
>> I was curious if that is better than just copying the .mdf and ldf.
>> At the moment, we do the Sql Server Full Backup and then our Veritas
>> backup program copies that backup to another Server.
>> The question is what is the drawback to just using Veritas to copy the
>> mdf and ldf to a couple of different servers (and an external drive that
>> we take off site).
>> Thanks,
>> Tom
>
Thursday, March 8, 2012
backup cannot make a copy of the database
I have a client who would like to automate the backup. I have used nti
backup for cd's and microsofts backup utility that came with windows
2000. They both state that the files cannot be backed up because the
files are in use.
any Ideas on how to automate the backup' and bypass the exclusive
holdings of the files'Yes. You have to tell SQL Server to backup the database to a file.
Then you can backup the file using whatever backup utility you like.
The command looks like this:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
After the command is executed within SQL Server (HINT: you can schedule a
job to run automatically within the SQL Server Agent) the file will be in
the location specified (the root of the X drive in this example).
Keith Kratochvil
"Mike Ryan" <miker@.centralutahcomputers.com> wrote in message
news:%23IVw6PNhGHA.4104@.TK2MSFTNGP04.phx.gbl...
> I have a client who would like to automate the backup. I have used nti
> backup for cd's and microsofts backup utility that came with windows 2000.
> They both state that the files cannot be backed up because the files are
> in use.
> any Ideas on how to automate the backup' and bypass the exclusive
> holdings of the files'
>|||Keith Kratochvil wrote:
> Yes. You have to tell SQL Server to backup the database to a file.
> Then you can backup the file using whatever backup utility you like.
> The command looks like this:
> BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
> After the command is executed within SQL Server (HINT: you can schedule a
> job to run automatically within the SQL Server Agent) the file will be in
> the location specified (the root of the X drive in this example).
>
Additionally you (or your client) should read up on BACKUP and RESTORE
strategy in Books On Line. It sounds like you aren't very familiar with
SQL backups but there are a few concepts you need to be aware of like
Recovery Model (FULL/SIMPLE), difference between FULL Database backup
and log backup and maybe even more important - RESTORE options.
Regards
Steen Schlter Persson
DBA|||Good point!
Knowing how to backup and restore a database is very important. Books
Online is a great resource and contains good information.
Keith Kratochvil
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%23LLSG3XhGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Keith Kratochvil wrote:
> Additionally you (or your client) should read up on BACKUP and RESTORE
> strategy in Books On Line. It sounds like you aren't very familiar with
> SQL backups but there are a few concepts you need to be aware of like
> Recovery Model (FULL/SIMPLE), difference between FULL Database backup and
> log backup and maybe even more important - RESTORE options.
> --
> Regards
> Steen Schlter Persson
> DBA
backup for cd's and microsofts backup utility that came with windows
2000. They both state that the files cannot be backed up because the
files are in use.
any Ideas on how to automate the backup' and bypass the exclusive
holdings of the files'Yes. You have to tell SQL Server to backup the database to a file.
Then you can backup the file using whatever backup utility you like.
The command looks like this:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
After the command is executed within SQL Server (HINT: you can schedule a
job to run automatically within the SQL Server Agent) the file will be in
the location specified (the root of the X drive in this example).
Keith Kratochvil
"Mike Ryan" <miker@.centralutahcomputers.com> wrote in message
news:%23IVw6PNhGHA.4104@.TK2MSFTNGP04.phx.gbl...
> I have a client who would like to automate the backup. I have used nti
> backup for cd's and microsofts backup utility that came with windows 2000.
> They both state that the files cannot be backed up because the files are
> in use.
> any Ideas on how to automate the backup' and bypass the exclusive
> holdings of the files'
>|||Keith Kratochvil wrote:
> Yes. You have to tell SQL Server to backup the database to a file.
> Then you can backup the file using whatever backup utility you like.
> The command looks like this:
> BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
> After the command is executed within SQL Server (HINT: you can schedule a
> job to run automatically within the SQL Server Agent) the file will be in
> the location specified (the root of the X drive in this example).
>
Additionally you (or your client) should read up on BACKUP and RESTORE
strategy in Books On Line. It sounds like you aren't very familiar with
SQL backups but there are a few concepts you need to be aware of like
Recovery Model (FULL/SIMPLE), difference between FULL Database backup
and log backup and maybe even more important - RESTORE options.
Regards
Steen Schlter Persson
DBA|||Good point!
Knowing how to backup and restore a database is very important. Books
Online is a great resource and contains good information.
Keith Kratochvil
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%23LLSG3XhGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Keith Kratochvil wrote:
> Additionally you (or your client) should read up on BACKUP and RESTORE
> strategy in Books On Line. It sounds like you aren't very familiar with
> SQL backups but there are a few concepts you need to be aware of like
> Recovery Model (FULL/SIMPLE), difference between FULL Database backup and
> log backup and maybe even more important - RESTORE options.
> --
> Regards
> Steen Schlter Persson
> DBA
backup cannot make a copy of the database
I have a client who would like to automate the backup. I have used nti
backup for cd's and microsofts backup utility that came with windows
2000. They both state that the files cannot be backed up because the
files are in use.
any Ideas on how to automate the backup' and bypass the exclusive
holdings of the files'Yes. You have to tell SQL Server to backup the database to a file.
Then you can backup the file using whatever backup utility you like.
The command looks like this:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
After the command is executed within SQL Server (HINT: you can schedule a
job to run automatically within the SQL Server Agent) the file will be in
the location specified (the root of the X drive in this example).
Keith Kratochvil
"Mike Ryan" <miker@.centralutahcomputers.com> wrote in message
news:%23IVw6PNhGHA.4104@.TK2MSFTNGP04.phx.gbl...
> I have a client who would like to automate the backup. I have used nti
> backup for cd's and microsofts backup utility that came with windows 2000.
> They both state that the files cannot be backed up because the files are
> in use.
> any Ideas on how to automate the backup' and bypass the exclusive
> holdings of the files'
>|||Keith Kratochvil wrote:
> Yes. You have to tell SQL Server to backup the database to a file.
> Then you can backup the file using whatever backup utility you like.
> The command looks like this:
> BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
> After the command is executed within SQL Server (HINT: you can schedule a
> job to run automatically within the SQL Server Agent) the file will be in
> the location specified (the root of the X drive in this example).
>
Additionally you (or your client) should read up on BACKUP and RESTORE
strategy in Books On Line. It sounds like you aren't very familiar with
SQL backups but there are a few concepts you need to be aware of like
Recovery Model (FULL/SIMPLE), difference between FULL Database backup
and log backup and maybe even more important - RESTORE options.
--
Regards
Steen Schlüter Persson
DBA|||Good point!
Knowing how to backup and restore a database is very important. Books
Online is a great resource and contains good information.
--
Keith Kratochvil
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%23LLSG3XhGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Keith Kratochvil wrote:
>> Yes. You have to tell SQL Server to backup the database to a file.
>> Then you can backup the file using whatever backup utility you like.
>> The command looks like this:
>> BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
>> After the command is executed within SQL Server (HINT: you can schedule a
>> job to run automatically within the SQL Server Agent) the file will be in
>> the location specified (the root of the X drive in this example).
>>
> Additionally you (or your client) should read up on BACKUP and RESTORE
> strategy in Books On Line. It sounds like you aren't very familiar with
> SQL backups but there are a few concepts you need to be aware of like
> Recovery Model (FULL/SIMPLE), difference between FULL Database backup and
> log backup and maybe even more important - RESTORE options.
> --
> Regards
> Steen Schlüter Persson
> DBA
backup for cd's and microsofts backup utility that came with windows
2000. They both state that the files cannot be backed up because the
files are in use.
any Ideas on how to automate the backup' and bypass the exclusive
holdings of the files'Yes. You have to tell SQL Server to backup the database to a file.
Then you can backup the file using whatever backup utility you like.
The command looks like this:
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
After the command is executed within SQL Server (HINT: you can schedule a
job to run automatically within the SQL Server Agent) the file will be in
the location specified (the root of the X drive in this example).
Keith Kratochvil
"Mike Ryan" <miker@.centralutahcomputers.com> wrote in message
news:%23IVw6PNhGHA.4104@.TK2MSFTNGP04.phx.gbl...
> I have a client who would like to automate the backup. I have used nti
> backup for cd's and microsofts backup utility that came with windows 2000.
> They both state that the files cannot be backed up because the files are
> in use.
> any Ideas on how to automate the backup' and bypass the exclusive
> holdings of the files'
>|||Keith Kratochvil wrote:
> Yes. You have to tell SQL Server to backup the database to a file.
> Then you can backup the file using whatever backup utility you like.
> The command looks like this:
> BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
> After the command is executed within SQL Server (HINT: you can schedule a
> job to run automatically within the SQL Server Agent) the file will be in
> the location specified (the root of the X drive in this example).
>
Additionally you (or your client) should read up on BACKUP and RESTORE
strategy in Books On Line. It sounds like you aren't very familiar with
SQL backups but there are a few concepts you need to be aware of like
Recovery Model (FULL/SIMPLE), difference between FULL Database backup
and log backup and maybe even more important - RESTORE options.
--
Regards
Steen Schlüter Persson
DBA|||Good point!
Knowing how to backup and restore a database is very important. Books
Online is a great resource and contains good information.
--
Keith Kratochvil
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%23LLSG3XhGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Keith Kratochvil wrote:
>> Yes. You have to tell SQL Server to backup the database to a file.
>> Then you can backup the file using whatever backup utility you like.
>> The command looks like this:
>> BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
>> After the command is executed within SQL Server (HINT: you can schedule a
>> job to run automatically within the SQL Server Agent) the file will be in
>> the location specified (the root of the X drive in this example).
>>
> Additionally you (or your client) should read up on BACKUP and RESTORE
> strategy in Books On Line. It sounds like you aren't very familiar with
> SQL backups but there are a few concepts you need to be aware of like
> Recovery Model (FULL/SIMPLE), difference between FULL Database backup and
> log backup and maybe even more important - RESTORE options.
> --
> Regards
> Steen Schlüter Persson
> DBA
Wednesday, March 7, 2012
Backup And Restore problem
Hi ,
I am trying to copy a Database from one server to another with backup and restore. The problem is that after restore when I compare the 2 databases I found some difference between Source DB and target DB.
I know its seems strange but its a fact.
I comprea the DB with SqlCompare and SqlDatacompre.
can someone help with thatAfter the backup completed, was there any insert, update, or delete in the database?|||Hi,
No . there where no changes, how ever the differnce between the 2 dataase are changes that were made a few hours b4 backup was taken.
I though it might have somthing to do with checkpoint so I run the command checpoint on database before backup and even shutdown the server itself ,however same results. I don'n know if its matter but recovery model is SIMPLE (it is a development DB)
10x
Sandu|||What is different ?|||2 new tables and some data in other tables|||Just out of curiosity - have you tried the copy database wizard or dts ?|||No , but probably will not have a choice but to try this methods|||Well, I would try this to help troubleshoot.|||Hi,
Well the prolem with the copy wizard is that if I want to copy it to a differnt name I can't do it . if for example I have 2 servers srv1 with db1
and srv2 with db1 and I want to create on srv2 a db called db2 which is copy of srv1.db1 I have to rename Databases and I want other people no DBA to be able to do it with one click|||At least test it out to see if there are other issues. So, this sounds like a process you will repeat ?|||What type of interface will these users use ? And what will they have access to and what parameters can they change ?|||Hi ,
I am a part of a project develpment team , the projects has 2 databases
one is a metadata DB and one is the application DB changes made in metaData shold be transfer to Testint and QA enviroments till now we did it with DTS that moved the Data but this process had to remove FK and than Rebuild them so I thought Backup /restore will be better but then I found the diffrences so maybe I 'll go back to the DTS even if I don't like it|||It just sounds like your backup is not up to date - especially since the news tables do not appear.|||I perform the backup b 4 the restore I do not use Old backup|||Have you tried the same operations on a database like pubs/northwind - assuming you still have them - meaning creating additional tables, backup and restore on the other server ?|||going to try it|||I have tried this on northwind as follows:
created 2 tables on nothwind aaaa and cccc
on EM all tasks ->backup database I checked database complete and on options tab I marked all
I copied the Destination of the backup and opend another server and there All tasks restore database I changed the name to nwind then cheked from device paste the full path and file name of the backup file
then on opton Tab corrected the pathe of file location and copleted the restore
the 2 files aaaa and cccc are not in nwind DB
amazing isnt't it ??|||Try detaching and attaching the database.|||HI ,
Thats a good Idea will try first hting after the Weekend
10x
Sandu
I am trying to copy a Database from one server to another with backup and restore. The problem is that after restore when I compare the 2 databases I found some difference between Source DB and target DB.
I know its seems strange but its a fact.
I comprea the DB with SqlCompare and SqlDatacompre.
can someone help with thatAfter the backup completed, was there any insert, update, or delete in the database?|||Hi,
No . there where no changes, how ever the differnce between the 2 dataase are changes that were made a few hours b4 backup was taken.
I though it might have somthing to do with checkpoint so I run the command checpoint on database before backup and even shutdown the server itself ,however same results. I don'n know if its matter but recovery model is SIMPLE (it is a development DB)
10x
Sandu|||What is different ?|||2 new tables and some data in other tables|||Just out of curiosity - have you tried the copy database wizard or dts ?|||No , but probably will not have a choice but to try this methods|||Well, I would try this to help troubleshoot.|||Hi,
Well the prolem with the copy wizard is that if I want to copy it to a differnt name I can't do it . if for example I have 2 servers srv1 with db1
and srv2 with db1 and I want to create on srv2 a db called db2 which is copy of srv1.db1 I have to rename Databases and I want other people no DBA to be able to do it with one click|||At least test it out to see if there are other issues. So, this sounds like a process you will repeat ?|||What type of interface will these users use ? And what will they have access to and what parameters can they change ?|||Hi ,
I am a part of a project develpment team , the projects has 2 databases
one is a metadata DB and one is the application DB changes made in metaData shold be transfer to Testint and QA enviroments till now we did it with DTS that moved the Data but this process had to remove FK and than Rebuild them so I thought Backup /restore will be better but then I found the diffrences so maybe I 'll go back to the DTS even if I don't like it|||It just sounds like your backup is not up to date - especially since the news tables do not appear.|||I perform the backup b 4 the restore I do not use Old backup|||Have you tried the same operations on a database like pubs/northwind - assuming you still have them - meaning creating additional tables, backup and restore on the other server ?|||going to try it|||I have tried this on northwind as follows:
created 2 tables on nothwind aaaa and cccc
on EM all tasks ->backup database I checked database complete and on options tab I marked all
I copied the Destination of the backup and opend another server and there All tasks restore database I changed the name to nwind then cheked from device paste the full path and file name of the backup file
then on opton Tab corrected the pathe of file location and copleted the restore
the 2 files aaaa and cccc are not in nwind DB
amazing isnt't it ??|||Try detaching and attaching the database.|||HI ,
Thats a good Idea will try first hting after the Weekend
10x
Sandu
Saturday, February 25, 2012
backup and restore database on different servers
SQL SERVER 2000 (8.0.2039 ) on both servers.
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on the
destination server. When I try to restore the database on destination server,
On the Restore screen... In the option to select the .bak files (Show backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canadaDB,
You need to select the "Restore from device" radio button, then add the file
that you want to restore from.
Any server only has backup history for itself. It does not look for files
on disk of any particular pattern, it looks at the history in msdb.
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> SQL SERVER 2000 (8.0.2039 ) on both servers.
> I need to copy a database from one server to another server. I have
> created
> a backup of the database on source server. Moved the backup(.bak file) on
> the
> destination server. When I try to restore the database on destination
> server,
> On the Restore screen... In the option to select the .bak files (Show
> backups
> of database) I can see the backups of existing databases of the
> destination
> server but not the backup I want to restore which was taken on the source
> server.
> What needs to be done?
> --
> DB
> ontario, canada|||Have it backup from a device and specify the file there. Also, your build
of SQL Server 2000 has a known bug. Check out the following build chart and
apply the hotfix:
http://aspfaq.com/SQL2000Builds.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
SQL SERVER 2000 (8.0.2039 ) on both servers.
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on
the
destination server. When I try to restore the database on destination
server,
On the Restore screen... In the option to select the .bak files (Show
backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canada|||Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file) on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||Yep. Often the path on the destination serer is different from the original
server. Just specify whatever path you want.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the
file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file)
on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the
source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||DB,
Yes, you can give it the new paths where you want the .mdf and .ldf files to
reside.
Of course, I recommend always testing new commands on a test server. (I
just tested this on one of my SQL 2000 servers, using both SQL Server
Management Studio 2005 and SQL Server 2000 Enterprise Manager.)
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
> Hi Russel
> Under the options
> 1. Logical file name: I will keep same logical file names.
> 2. Move to Physical file name: Can I change it and give the path where I
> want to install the .mdf and .ldf files on the destination server.
> I know this is a silly question but I do not want to test it as working on
> production server.
> Thanks
> DB
>
> --
> ontario, canada
>
> "Russell Fields" wrote:
>> DB,
>> You need to select the "Restore from device" radio button, then add the
>> file
>> that you want to restore from.
>> Any server only has backup history for itself. It does not look for
>> files
>> on disk of any particular pattern, it looks at the history in msdb.
>> RLF
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
>> > SQL SERVER 2000 (8.0.2039 ) on both servers.
>> > I need to copy a database from one server to another server. I have
>> > created
>> > a backup of the database on source server. Moved the backup(.bak file)
>> > on
>> > the
>> > destination server. When I try to restore the database on destination
>> > server,
>> > On the Restore screen... In the option to select the .bak files (Show
>> > backups
>> > of database) I can see the backups of existing databases of the
>> > destination
>> > server but not the backup I want to restore which was taken on the
>> > source
>> > server.
>> > What needs to be done?
>> > --
>> > DB
>> > ontario, canada
>>
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on the
destination server. When I try to restore the database on destination server,
On the Restore screen... In the option to select the .bak files (Show backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canadaDB,
You need to select the "Restore from device" radio button, then add the file
that you want to restore from.
Any server only has backup history for itself. It does not look for files
on disk of any particular pattern, it looks at the history in msdb.
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> SQL SERVER 2000 (8.0.2039 ) on both servers.
> I need to copy a database from one server to another server. I have
> created
> a backup of the database on source server. Moved the backup(.bak file) on
> the
> destination server. When I try to restore the database on destination
> server,
> On the Restore screen... In the option to select the .bak files (Show
> backups
> of database) I can see the backups of existing databases of the
> destination
> server but not the backup I want to restore which was taken on the source
> server.
> What needs to be done?
> --
> DB
> ontario, canada|||Have it backup from a device and specify the file there. Also, your build
of SQL Server 2000 has a known bug. Check out the following build chart and
apply the hotfix:
http://aspfaq.com/SQL2000Builds.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
SQL SERVER 2000 (8.0.2039 ) on both servers.
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on
the
destination server. When I try to restore the database on destination
server,
On the Restore screen... In the option to select the .bak files (Show
backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canada|||Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file) on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||Yep. Often the path on the destination serer is different from the original
server. Just specify whatever path you want.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the
file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file)
on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the
source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||DB,
Yes, you can give it the new paths where you want the .mdf and .ldf files to
reside.
Of course, I recommend always testing new commands on a test server. (I
just tested this on one of my SQL 2000 servers, using both SQL Server
Management Studio 2005 and SQL Server 2000 Enterprise Manager.)
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
> Hi Russel
> Under the options
> 1. Logical file name: I will keep same logical file names.
> 2. Move to Physical file name: Can I change it and give the path where I
> want to install the .mdf and .ldf files on the destination server.
> I know this is a silly question but I do not want to test it as working on
> production server.
> Thanks
> DB
>
> --
> ontario, canada
>
> "Russell Fields" wrote:
>> DB,
>> You need to select the "Restore from device" radio button, then add the
>> file
>> that you want to restore from.
>> Any server only has backup history for itself. It does not look for
>> files
>> on disk of any particular pattern, it looks at the history in msdb.
>> RLF
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
>> > SQL SERVER 2000 (8.0.2039 ) on both servers.
>> > I need to copy a database from one server to another server. I have
>> > created
>> > a backup of the database on source server. Moved the backup(.bak file)
>> > on
>> > the
>> > destination server. When I try to restore the database on destination
>> > server,
>> > On the Restore screen... In the option to select the .bak files (Show
>> > backups
>> > of database) I can see the backups of existing databases of the
>> > destination
>> > server but not the backup I want to restore which was taken on the
>> > source
>> > server.
>> > What needs to be done?
>> > --
>> > DB
>> > ontario, canada
>>
backup and restore
I have a database xxxx on server a, I want to copy this DB to server b.How can I do this By back and restore.
WHere can take this back up?I want to take this to a personal folder and wanted to restore to server b from this folder.Is it possible?
Thanks.On server a:
backup database xxx to disk = '\\b\sharename\xxx.bak' with init
and then on server b:
restore database xxx from disk = 'd:\sharename\xxx.bak' with replace,
move 'xxx_data' to 'e:\xxx_data.mdf',
move 'xxx_log' to 'f:\xxx_log.ldf'|||You can backup to a network location, then have a seperate job run the restore using that location. See backup [described] in BOL.
HTH
WHere can take this back up?I want to take this to a personal folder and wanted to restore to server b from this folder.Is it possible?
Thanks.On server a:
backup database xxx to disk = '\\b\sharename\xxx.bak' with init
and then on server b:
restore database xxx from disk = 'd:\sharename\xxx.bak' with replace,
move 'xxx_data' to 'e:\xxx_data.mdf',
move 'xxx_log' to 'f:\xxx_log.ldf'|||You can backup to a network location, then have a seperate job run the restore using that location. See backup [described] in BOL.
HTH
backup and resore encrypted data
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
--
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@.id int,
@.var1 varchar(100)
AS
BEGIN
DECLARE @.var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@.var1 )
insert into dbo.Test(id,Data,EData)
values(@.id,@.var1,@.var2)
CLOSE SYMMETRIC KEY symkey_demo
END
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@.id int
AS
BEGIN
DECLARE @.var1 varbinary(256)
DECLARE @.var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @.var1=EData from Test where id=@.id
Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))
print @.var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).
When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
For more detailed information go to BOL:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul.
I had done two things.
1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
2. Again the symmetric key doesn't work.
Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.
so I have to change the symmetric key generation code to,
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES,
identity_value = 'Test Data encryption key',
key_source = 'test key'
ENCRYPTION BY CERTIFICATE cert_demo
Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.
Thanks
Amit
|||I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.
Thanks a lot for letting us know, and let us know if you have further questions or comments.
-Raul Garcia
SDE/T
SQL Server Engine
backup and resore encrypted data
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
--
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@.id int,
@.var1 varchar(100)
AS
BEGIN
DECLARE @.var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @.var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@.var1 )
insert into dbo.Test(id,Data,EData)
values(@.id,@.var1,@.var2)
CLOSE SYMMETRIC KEY symkey_demo
END
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@.id int
AS
BEGIN
DECLARE @.var1 varbinary(256)
DECLARE @.var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @.var1=EData from Test where id=@.id
Set @.var2 = convert( varchar(100), DecryptByKey( @.var1))
print @.var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
The certificate protecting the key encrypting your data is protected by the DB master key (DBMK). In the server where the DBMK was created, the DBMK is protected by the password and by the service master kit (SMK). The SMK protection allows to use the DBMK automatically (i.e. without the need to open the DBMK explicitly).
When you move the DB to a different server, the protection by the SMK is no longer valid, and you need to manually open the DBMK and encrypt it with the current SMK. This is a one-time operation.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<password>>'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
For more detailed information go to BOL:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul.
I had done two things.
1.The service master key doesn't working. So I have to manually open the DBMK and encrypt with the current SMK.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password!'
Go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
2. Again the symmetric key doesn't work.
Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases.
so I have to change the symmetric key generation code to,
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES,
identity_value = 'Test Data encryption key',
key_source = 'test key'
ENCRYPTION BY CERTIFICATE cert_demo
Now I backed up the production database and restore it in a different server. I followed the stpe 1 and it works. Now I am able to decrypt the data in the production copy database.
Thanks
Amit
|||I see, when you described a copy of the datbase I assumed (incorrectly) that you were referring to a copy based on a backup.
Thanks a lot for letting us know, and let us know if you have further questions or comments.
-Raul Garcia
SDE/T
SQL Server Engine
Friday, February 24, 2012
Backup Agent for SQL Server ?
We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Backup Agent for SQL Server ?
We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Backup Agent for SQL Server
We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.
Backup Agent for SQL Server
We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.
Thursday, February 16, 2012
BackUp
I have a real time database and backup database. I want to create a DTS package to copy the data from real time to backup in bulk mode.You might think about using standard backup and restore, it is faster than a DTS copy of any sort. I use a utility called robocopy from one of the res kits for Windows to copy only the new files to a remote server (tran logs and backups)
HTH|||you can use log shipping to transfer transaction logs to another server
HTH|||you can use log shipping to transfer transaction logs to another server
backup
Hi All,
i want backup my SQL server (2005) but i do not have SQL agnet for backup
can i use follow script to copy whole database file and backup this file
with NTBackup? can it's backup be restore and work fine? pls advise.
net stop "SQL Server (MSSMLBIZ)"
xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
/Y
net start "SQL Server (MSSMLBIZ)"
thanks,
William
yes this should work.
William Cheung wrote:
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
> /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>
|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>
|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>
i want backup my SQL server (2005) but i do not have SQL agnet for backup
can i use follow script to copy whole database file and backup this file
with NTBackup? can it's backup be restore and work fine? pls advise.
net stop "SQL Server (MSSMLBIZ)"
xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
/Y
net start "SQL Server (MSSMLBIZ)"
thanks,
William
yes this should work.
William Cheung wrote:
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
> /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>
|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>
|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>
backup
Hi All,
i want backup my SQL server (2005) but i do not have SQL agnet for backup
can i use follow script to copy whole database file and backup this file
with NTBackup? can it's backup be restore and work fine? pls advise.
net stop "SQL Server (MSSMLBIZ)"
xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
/Y
net start "SQL Server (MSSMLBIZ)"
thanks,
Williamyes this should work.
William Cheung wrote:
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
> /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Arnie Rowland wrote:
> Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
> Standard Edition (which includes SQL Agent.)
>
I think that he means that he hasn't got the backup SW's SQL agent - not
the SQL Server Agent.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
i want backup my SQL server (2005) but i do not have SQL agnet for backup
can i use follow script to copy whole database file and backup this file
with NTBackup? can it's backup be restore and work fine? pls advise.
net stop "SQL Server (MSSMLBIZ)"
xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
/Y
net start "SQL Server (MSSMLBIZ)"
thanks,
Williamyes this should work.
William Cheung wrote:
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
> /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Arnie Rowland wrote:
> Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
> Standard Edition (which includes SQL Agent.)
>
I think that he means that he hasn't got the backup SW's SQL agent - not
the SQL Server Agent.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Tuesday, February 14, 2012
backup
Hi All,
i want backup my SQL server (2005) but i do not have SQL agnet for backup
can i use follow script to copy whole database file and backup this file
with NTBackup? can it's backup be restore and work fine? pls advise.
net stop "SQL Server (MSSMLBIZ)"
xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
/Y
net start "SQL Server (MSSMLBIZ)"
thanks,
Williamyes this should work.
William Cheung wrote:
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ
"
> /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Arnie Rowland wrote:
> Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
> Standard Edition (which includes SQL Agent.)
>
I think that he means that he hasn't got the backup SW's SQL agent - not
the SQL Server Agent.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
i want backup my SQL server (2005) but i do not have SQL agnet for backup
can i use follow script to copy whole database file and backup this file
with NTBackup? can it's backup be restore and work fine? pls advise.
net stop "SQL Server (MSSMLBIZ)"
xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ"
/Y
net start "SQL Server (MSSMLBIZ)"
thanks,
Williamyes this should work.
William Cheung wrote:
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1" "C:\Backup\BIZ
"
> /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
Standard Edition (which includes SQL Agent.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:%23azj7ViAHHA.4844@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> i want backup my SQL server (2005) but i do not have SQL agnet for backup
> can i use follow script to copy whole database file and backup this file
> with NTBackup? can it's backup be restore and work fine? pls advise.
> net stop "SQL Server (MSSMLBIZ)"
> xcopy /e /f "C:\Program Files\Microsoft SQL Server\MSSQL.1"
> "C:\Backup\BIZ" /Y
> net start "SQL Server (MSSMLBIZ)"
> thanks,
> William
>|||Arnie Rowland wrote:
> Why don't you have SQL Agent? (Your other post indicated you had SQL 2005
> Standard Edition (which includes SQL Agent.)
>
I think that he means that he hasn't got the backup SW's SQL agent - not
the SQL Server Agent.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Subscribe to:
Posts (Atom)