We want to set up another office as an online disaster recovery site. Can I
clister between two SQL servers over a T1?
Your best bet is using third party software, though you might be able to use
MNS (Majority Node Set). Have you looked into Log Shipping with SQL?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Rick Vines" <Rick Vines@.discussions.microsoft.com> wrote in message
news:640BDCB1-F523-4B21-AF91-AB24D58D2DCD@.microsoft.com...
> We want to set up another office as an online disaster recovery site. Can
> I
> clister between two SQL servers over a T1?
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Based on backup and recovery
hello,
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..
as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..
as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).
Thursday, March 8, 2012
Backups Question?
I am using a Full recovery model on SQL 2005 with a daily full and 15 min
transactional. The disk constantly keeps getting full and I have to manually
delete the .bak and .trn files that are three days old to allow for more disk
space. My first option was to only do a Full backup once a week and keep
transaction backups at 15 min. Regardless, I still have to have an automated
way to do this. Is there a way, tool, script somewhere that I could use to
manage this automatically? Thank you.
morphius wrote:
> I am using a Full recovery model on SQL 2005 with a daily full and 15 min
> transactional. The disk constantly keeps getting full and I have to manually
> delete the .bak and .trn files that are three days old to allow for more disk
> space. My first option was to only do a Full backup once a week and keep
> transaction backups at 15 min. Regardless, I still have to have an automated
> way to do this. Is there a way, tool, script somewhere that I could use to
> manage this automatically? Thank you.
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
Note that this uses xp_cmdshell, so you'll need to enable that since
you're on SQL 2005.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks
"Tracy McKibben" wrote:
> morphius wrote:
> http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
> Note that this uses xp_cmdshell, so you'll need to enable that since
> you're on SQL 2005.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Thanks
"Tibor Karaszi" wrote:
> Maintenance Plans can create new and delete old backup files. The feature of removing old backup
> files is not available in the wizard (until sp2), so make sure to edit the package to add such task.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:1A9D1A49-5BBA-4022-84FF-3158790E0B5C@.microsoft.com...
>
transactional. The disk constantly keeps getting full and I have to manually
delete the .bak and .trn files that are three days old to allow for more disk
space. My first option was to only do a Full backup once a week and keep
transaction backups at 15 min. Regardless, I still have to have an automated
way to do this. Is there a way, tool, script somewhere that I could use to
manage this automatically? Thank you.
morphius wrote:
> I am using a Full recovery model on SQL 2005 with a daily full and 15 min
> transactional. The disk constantly keeps getting full and I have to manually
> delete the .bak and .trn files that are three days old to allow for more disk
> space. My first option was to only do a Full backup once a week and keep
> transaction backups at 15 min. Regardless, I still have to have an automated
> way to do this. Is there a way, tool, script somewhere that I could use to
> manage this automatically? Thank you.
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
Note that this uses xp_cmdshell, so you'll need to enable that since
you're on SQL 2005.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks
"Tracy McKibben" wrote:
> morphius wrote:
> http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
> Note that this uses xp_cmdshell, so you'll need to enable that since
> you're on SQL 2005.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Thanks
"Tibor Karaszi" wrote:
> Maintenance Plans can create new and delete old backup files. The feature of removing old backup
> files is not available in the wizard (until sp2), so make sure to edit the package to add such task.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:1A9D1A49-5BBA-4022-84FF-3158790E0B5C@.microsoft.com...
>
Backups Question?
I am using a Full recovery model on SQL 2005 with a daily full and 15 min
transactional. The disk constantly keeps getting full and I have to manually
delete the .bak and .trn files that are three days old to allow for more dis
k
space. My first option was to only do a Full backup once a week and keep
transaction backups at 15 min. Regardless, I still have to have an automated
way to do this. Is there a way, tool, script somewhere that I could use to
manage this automatically? Thank you.morphius wrote:
> I am using a Full recovery model on SQL 2005 with a daily full and 15 min
> transactional. The disk constantly keeps getting full and I have to manual
ly
> delete the .bak and .trn files that are three days old to allow for more d
isk
> space. My first option was to only do a Full backup once a week and keep
> transaction backups at 15 min. Regardless, I still have to have an automat
ed
> way to do this. Is there a way, tool, script somewhere that I could use t
o
> manage this automatically? Thank you.
http://realsqlguy.com/serendipity/a...th-The-Old.html
Note that this uses xp_cmdshell, so you'll need to enable that since
you're on SQL 2005.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Maintenance Plans can create new and delete old backup files. The feature of
removing old backup
files is not available in the wizard (until sp2), so make sure to edit the p
ackage to add such task.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:1A9D1A49-5BBA-4022-84FF-3158790E0B5C@.microsoft.com...
>I am using a Full recovery model on SQL 2005 with a daily full and 15 min
> transactional. The disk constantly keeps getting full and I have to manual
ly
> delete the .bak and .trn files that are three days old to allow for more d
isk
> space. My first option was to only do a Full backup once a week and keep
> transaction backups at 15 min. Regardless, I still have to have an automat
ed
> way to do this. Is there a way, tool, script somewhere that I could use t
o
> manage this automatically? Thank you.|||Thanks
"Tracy McKibben" wrote:
> morphius wrote:
> http://realsqlguy.com/serendipity/a...th-The-Old.html
> Note that this uses xp_cmdshell, so you'll need to enable that since
> you're on SQL 2005.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Thanks
"Tibor Karaszi" wrote:
> Maintenance Plans can create new and delete old backup files. The feature
of removing old backup
> files is not available in the wizard (until sp2), so make sure to edit the
package to add such task.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:1A9D1A49-5BBA-4022-84FF-3158790E0B5C@.microsoft.com...
>
transactional. The disk constantly keeps getting full and I have to manually
delete the .bak and .trn files that are three days old to allow for more dis
k
space. My first option was to only do a Full backup once a week and keep
transaction backups at 15 min. Regardless, I still have to have an automated
way to do this. Is there a way, tool, script somewhere that I could use to
manage this automatically? Thank you.morphius wrote:
> I am using a Full recovery model on SQL 2005 with a daily full and 15 min
> transactional. The disk constantly keeps getting full and I have to manual
ly
> delete the .bak and .trn files that are three days old to allow for more d
isk
> space. My first option was to only do a Full backup once a week and keep
> transaction backups at 15 min. Regardless, I still have to have an automat
ed
> way to do this. Is there a way, tool, script somewhere that I could use t
o
> manage this automatically? Thank you.
http://realsqlguy.com/serendipity/a...th-The-Old.html
Note that this uses xp_cmdshell, so you'll need to enable that since
you're on SQL 2005.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Maintenance Plans can create new and delete old backup files. The feature of
removing old backup
files is not available in the wizard (until sp2), so make sure to edit the p
ackage to add such task.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:1A9D1A49-5BBA-4022-84FF-3158790E0B5C@.microsoft.com...
>I am using a Full recovery model on SQL 2005 with a daily full and 15 min
> transactional. The disk constantly keeps getting full and I have to manual
ly
> delete the .bak and .trn files that are three days old to allow for more d
isk
> space. My first option was to only do a Full backup once a week and keep
> transaction backups at 15 min. Regardless, I still have to have an automat
ed
> way to do this. Is there a way, tool, script somewhere that I could use t
o
> manage this automatically? Thank you.|||Thanks
"Tracy McKibben" wrote:
> morphius wrote:
> http://realsqlguy.com/serendipity/a...th-The-Old.html
> Note that this uses xp_cmdshell, so you'll need to enable that since
> you're on SQL 2005.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Thanks
"Tibor Karaszi" wrote:
> Maintenance Plans can create new and delete old backup files. The feature
of removing old backup
> files is not available in the wizard (until sp2), so make sure to edit the
package to add such task.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:1A9D1A49-5BBA-4022-84FF-3158790E0B5C@.microsoft.com...
>
Saturday, February 25, 2012
Backups
Hello,
Our backups are failing as we have connections to them,
and the error is that the database must be in single mode.
Our recovery plan is set to full, so I was wondering what
the best practise is i.e could we set the database to
single mode killing off all connections, use some SQL
commands to kill off connection, or change the recovery
model to something else.
Any help would be appreciated.
Thanks
PeterHello, Peter!
Is this through a maintenance plan ?
SQL Server backups can be taken with users still on the system. A Database
cannot be restored with users in the database. Is this error being
generated by SQL Server trying to put the DB into Single User mode to fix
minor errors ?
You can quite easily test the BACKUP ni isolation by going to QA and issuing
the statement there.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Our backups are failing as we have connections to them,
: and the error is that the database must be in single mode.
: Our recovery plan is set to full, so I was wondering what
: the best practise is i.e could we set the database to
: single mode killing off all connections, use some SQL
: commands to kill off connection, or change the recovery
: model to something else.
: Any help would be appreciated.
-- Microsoft CDO for Windows 2000|||Thank you, I will change my options.
Peter
>--Original Message--
>Yes, I agree with Allan in that this is due to the fact
you have the option
>checked to do an integrity check before the backups and
that is what needs
>to be in single user mode. This is a bad option anyway
because people
>usually have the integrity jobs running from the other
page of the MP
>dialog. So now it does DBCC's 2 or 3 times.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in
message
>news:enfKY0UQDHA.3016@.TK2MSFTNGP10.phx.gbl...
>> Hello, Peter!
>> Is this through a maintenance plan ?
>> SQL Server backups can be taken with users still on the
system. A
>Database
>> cannot be restored with users in the database. Is this
error being
>> generated by SQL Server trying to put the DB into
Single User mode to fix
>> minor errors ?
>>
>> You can quite easily test the BACKUP ni isolation by
going to QA and
>issuing
>> the statement there.
>>
>>
>> --
>> Allan Mitchell (Microsoft SQL Server MVP)
>> MCSE,MCDBA
>> www.SQLDTS.com
>> I support PASS - the definitive, global community
>> for SQL Server professionals - http://www.sqlpass.org
>> : Our backups are failing as we have connections to
them,
>> : and the error is that the database must be in single
mode.
>> : Our recovery plan is set to full, so I was wondering
what
>> : the best practise is i.e could we set the database to
>> : single mode killing off all connections, use some SQL
>> : commands to kill off connection, or change the
recovery
>> : model to something else.
>> : Any help would be appreciated.
>> -- Microsoft CDO for Windows 2000
>>
>
>.
>
Our backups are failing as we have connections to them,
and the error is that the database must be in single mode.
Our recovery plan is set to full, so I was wondering what
the best practise is i.e could we set the database to
single mode killing off all connections, use some SQL
commands to kill off connection, or change the recovery
model to something else.
Any help would be appreciated.
Thanks
PeterHello, Peter!
Is this through a maintenance plan ?
SQL Server backups can be taken with users still on the system. A Database
cannot be restored with users in the database. Is this error being
generated by SQL Server trying to put the DB into Single User mode to fix
minor errors ?
You can quite easily test the BACKUP ni isolation by going to QA and issuing
the statement there.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Our backups are failing as we have connections to them,
: and the error is that the database must be in single mode.
: Our recovery plan is set to full, so I was wondering what
: the best practise is i.e could we set the database to
: single mode killing off all connections, use some SQL
: commands to kill off connection, or change the recovery
: model to something else.
: Any help would be appreciated.
-- Microsoft CDO for Windows 2000|||Thank you, I will change my options.
Peter
>--Original Message--
>Yes, I agree with Allan in that this is due to the fact
you have the option
>checked to do an integrity check before the backups and
that is what needs
>to be in single user mode. This is a bad option anyway
because people
>usually have the integrity jobs running from the other
page of the MP
>dialog. So now it does DBCC's 2 or 3 times.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in
message
>news:enfKY0UQDHA.3016@.TK2MSFTNGP10.phx.gbl...
>> Hello, Peter!
>> Is this through a maintenance plan ?
>> SQL Server backups can be taken with users still on the
system. A
>Database
>> cannot be restored with users in the database. Is this
error being
>> generated by SQL Server trying to put the DB into
Single User mode to fix
>> minor errors ?
>>
>> You can quite easily test the BACKUP ni isolation by
going to QA and
>issuing
>> the statement there.
>>
>>
>> --
>> Allan Mitchell (Microsoft SQL Server MVP)
>> MCSE,MCDBA
>> www.SQLDTS.com
>> I support PASS - the definitive, global community
>> for SQL Server professionals - http://www.sqlpass.org
>> : Our backups are failing as we have connections to
them,
>> : and the error is that the database must be in single
mode.
>> : Our recovery plan is set to full, so I was wondering
what
>> : the best practise is i.e could we set the database to
>> : single mode killing off all connections, use some SQL
>> : commands to kill off connection, or change the
recovery
>> : model to something else.
>> : Any help would be appreciated.
>> -- Microsoft CDO for Windows 2000
>>
>
>.
>
Sunday, February 19, 2012
Backup/Restore File groups
I have a very large database(150G) with two filegroups.
One of the most frequent operation on this database is
bulk insert. The Recovery model is set to Simple for
avoiding the log file to grow. Since the db is so big I
need to individualy backup each file group and, in case
something happens, to be able to restore one filegroup. My
problem is that since I cannot afford to have recovery
model FULL, I don't have all the log backups required in
order to restore only one filegroup. There is any solution
to this problem?
Thank you
What exactly is the reason you can't back up a 150GB db? Is it disk space?
You may want to check out a product called SQL LiteSpeed that can compress
your backups down to 70 or 80%.
Andrew J. Kelly SQL MVP
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:90ba01c43300$ad2b9e30$a001280a@.phx.gbl...
> I have a very large database(150G) with two filegroups.
> One of the most frequent operation on this database is
> bulk insert. The Recovery model is set to Simple for
> avoiding the log file to grow. Since the db is so big I
> need to individualy backup each file group and, in case
> something happens, to be able to restore one filegroup. My
> problem is that since I cannot afford to have recovery
> model FULL, I don't have all the log backups required in
> order to restore only one filegroup. There is any solution
> to this problem?
> Thank you
One of the most frequent operation on this database is
bulk insert. The Recovery model is set to Simple for
avoiding the log file to grow. Since the db is so big I
need to individualy backup each file group and, in case
something happens, to be able to restore one filegroup. My
problem is that since I cannot afford to have recovery
model FULL, I don't have all the log backups required in
order to restore only one filegroup. There is any solution
to this problem?
Thank you
What exactly is the reason you can't back up a 150GB db? Is it disk space?
You may want to check out a product called SQL LiteSpeed that can compress
your backups down to 70 or 80%.
Andrew J. Kelly SQL MVP
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:90ba01c43300$ad2b9e30$a001280a@.phx.gbl...
> I have a very large database(150G) with two filegroups.
> One of the most frequent operation on this database is
> bulk insert. The Recovery model is set to Simple for
> avoiding the log file to grow. Since the db is so big I
> need to individualy backup each file group and, in case
> something happens, to be able to restore one filegroup. My
> problem is that since I cannot afford to have recovery
> model FULL, I don't have all the log backups required in
> order to restore only one filegroup. There is any solution
> to this problem?
> Thank you
Thursday, February 16, 2012
backup/recovery strategy
Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...
Hi,
See the below URL's
http://www.microsoft.com/technet/pro...ackuprest.mspx
http://www1.us.dell.com/content/topi...555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_..._practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...
Hi,
See the below URL's
http://www.microsoft.com/technet/pro...ackuprest.mspx
http://www1.us.dell.com/content/topi...555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_..._practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
backup/recovery strategy
Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/pr...
555&l=en&s=biz
http://vyaskn.tripod.com/ sql_serve...ices
.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/pr...
555&l=en&s=biz
http://vyaskn.tripod.com/ sql_serve...ices
.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
backup/recovery strategy
Currently we have about 60 SQL Servers all shape and size (the biggest is
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
payroll dbs 20gb). I would like very much to standartize backup and recovery
procedure for all servers and dbs. Pls, fellows, help with advice for
procedures, 3rd party tools, backup devices and so on...
I will be grateful if you share your experience...Hi,
See the below URL's
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www1.us.dell.com/content/topics/global.aspx/power/en/ps4q00_martin?c=us&cs=555&l=en&s=biz
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8F912D14-F11A-4693-89D2-23B50213ED02@.microsoft.com...
> Currently we have about 60 SQL Servers all shape and size (the biggest is
> payroll dbs 20gb). I would like very much to standartize backup and
> recovery
> procedure for all servers and dbs. Pls, fellows, help with advice for
> procedures, 3rd party tools, backup devices and so on...
> I will be grateful if you share your experience...
Backup/DR configuration using mirroring and log shipping
I'm trying to come up with a db backup/Disaster Recovery plan using SQL
Server 2005 Mirroring and Log shipping and I need help understanding which of
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG
Server 2005 Mirroring and Log shipping and I need help understanding which of
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG
Backup/DR configuration using mirroring and log shipping
I'm trying to come up with a db backup/Disaster Recovery plan using SQL
Server 2005 Mirroring and Log shipping and I need help understanding which o
f
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG
Server 2005 Mirroring and Log shipping and I need help understanding which o
f
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG
Sunday, February 12, 2012
Backup Transaction Log - Event ID 17055
I recently changed the recovery model from simple to full on a specific SQL
Server 2000 database. I am backing up the transaction log every two hours
via a maintenance plan. I have other SQL Server 2000 DB's defined with the
full recovery model and do not receive these messages. The following error,
not a warning, appears in the application event log when the log backup
completes also in the database log. Are these backups valid? Found KB
818202 on this, but I cannot determine what action needs to be taken, if any.
Thoughts'?
Source: MSSQLSERVER
Category: (6)
Event ID: 17055
Description: 18278 Database log truncated: Database: dbname.Hi,
Can you perform a full database backup and then follow the transaction log
backup.
This is required when you change the recovery model from SIMPLE to FULL
Thanks
Hari
MCDBA
"paldba" wrote:
> I recently changed the recovery model from simple to full on a specific SQL
> Server 2000 database. I am backing up the transaction log every two hours
> via a maintenance plan. I have other SQL Server 2000 DB's defined with the
> full recovery model and do not receive these messages. The following error,
> not a warning, appears in the application event log when the log backup
> completes also in the database log. Are these backups valid? Found KB
> 818202 on this, but I cannot determine what action needs to be taken, if any.
> Thoughts'?
> Source: MSSQLSERVER
> Category: (6)
> Event ID: 17055
> Description: 18278 Database log truncated: Database: dbname.|||Yes, that has been completed.
"Hari Prasad" wrote:
> Hi,
> Can you perform a full database backup and then follow the transaction log
> backup.
> This is required when you change the recovery model from SIMPLE to FULL
> Thanks
> Hari
> MCDBA
> "paldba" wrote:
> > I recently changed the recovery model from simple to full on a specific SQL
> > Server 2000 database. I am backing up the transaction log every two hours
> > via a maintenance plan. I have other SQL Server 2000 DB's defined with the
> > full recovery model and do not receive these messages. The following error,
> > not a warning, appears in the application event log when the log backup
> > completes also in the database log. Are these backups valid? Found KB
> > 818202 on this, but I cannot determine what action needs to be taken, if any.
> > Thoughts'?
> >
> > Source: MSSQLSERVER
> > Category: (6)
> > Event ID: 17055
> > Description: 18278 Database log truncated: Database: dbname.|||It looks like someone is running a Backup LOG command with
NO_LOG or TRUNCATE_ONLY option. Investigate if anyone or
any job is scheduled to run this command. Also, check your
own backup log command to see if it includes this option.
Lll the Log backups after a truncate will be invalid till
you take a FULL backup.
>--Original Message--
>I recently changed the recovery model from simple to full
on a specific SQL
>Server 2000 database. I am backing up the transaction
log every two hours
>via a maintenance plan. I have other SQL Server 2000 DB's
defined with the
>full recovery model and do not receive these messages.
The following error,
>not a warning, appears in the application event log when
the log backup
>completes also in the database log. Are these backups
valid? Found KB
>818202 on this, but I cannot determine what action needs
to be taken, if any.
> Thoughts'?
>Source: MSSQLSERVER
>Category: (6)
>Event ID: 17055
>Description: 18278 Database log truncated: Database:
dbname.
>.
>
Server 2000 database. I am backing up the transaction log every two hours
via a maintenance plan. I have other SQL Server 2000 DB's defined with the
full recovery model and do not receive these messages. The following error,
not a warning, appears in the application event log when the log backup
completes also in the database log. Are these backups valid? Found KB
818202 on this, but I cannot determine what action needs to be taken, if any.
Thoughts'?
Source: MSSQLSERVER
Category: (6)
Event ID: 17055
Description: 18278 Database log truncated: Database: dbname.Hi,
Can you perform a full database backup and then follow the transaction log
backup.
This is required when you change the recovery model from SIMPLE to FULL
Thanks
Hari
MCDBA
"paldba" wrote:
> I recently changed the recovery model from simple to full on a specific SQL
> Server 2000 database. I am backing up the transaction log every two hours
> via a maintenance plan. I have other SQL Server 2000 DB's defined with the
> full recovery model and do not receive these messages. The following error,
> not a warning, appears in the application event log when the log backup
> completes also in the database log. Are these backups valid? Found KB
> 818202 on this, but I cannot determine what action needs to be taken, if any.
> Thoughts'?
> Source: MSSQLSERVER
> Category: (6)
> Event ID: 17055
> Description: 18278 Database log truncated: Database: dbname.|||Yes, that has been completed.
"Hari Prasad" wrote:
> Hi,
> Can you perform a full database backup and then follow the transaction log
> backup.
> This is required when you change the recovery model from SIMPLE to FULL
> Thanks
> Hari
> MCDBA
> "paldba" wrote:
> > I recently changed the recovery model from simple to full on a specific SQL
> > Server 2000 database. I am backing up the transaction log every two hours
> > via a maintenance plan. I have other SQL Server 2000 DB's defined with the
> > full recovery model and do not receive these messages. The following error,
> > not a warning, appears in the application event log when the log backup
> > completes also in the database log. Are these backups valid? Found KB
> > 818202 on this, but I cannot determine what action needs to be taken, if any.
> > Thoughts'?
> >
> > Source: MSSQLSERVER
> > Category: (6)
> > Event ID: 17055
> > Description: 18278 Database log truncated: Database: dbname.|||It looks like someone is running a Backup LOG command with
NO_LOG or TRUNCATE_ONLY option. Investigate if anyone or
any job is scheduled to run this command. Also, check your
own backup log command to see if it includes this option.
Lll the Log backups after a truncate will be invalid till
you take a FULL backup.
>--Original Message--
>I recently changed the recovery model from simple to full
on a specific SQL
>Server 2000 database. I am backing up the transaction
log every two hours
>via a maintenance plan. I have other SQL Server 2000 DB's
defined with the
>full recovery model and do not receive these messages.
The following error,
>not a warning, appears in the application event log when
the log backup
>completes also in the database log. Are these backups
valid? Found KB
>818202 on this, but I cannot determine what action needs
to be taken, if any.
> Thoughts'?
>Source: MSSQLSERVER
>Category: (6)
>Event ID: 17055
>Description: 18278 Database log truncated: Database:
dbname.
>.
>
Backup Transaction Log - Event ID 17055
I recently changed the recovery model from simple to full on a specific SQL
Server 2000 database. I am backing up the transaction log every two hours
via a maintenance plan. I have other SQL Server 2000 DB's defined with the
full recovery model and do not receive these messages. The following error,
not a warning, appears in the application event log when the log backup
completes also in the database log. Are these backups valid? Found KB
818202 on this, but I cannot determine what action needs to be taken, if any.
Thoughts??
Source: MSSQLSERVER
Category: (6)
Event ID: 17055
Description: 18278 Database log truncated: Database: dbname.
Hi,
Can you perform a full database backup and then follow the transaction log
backup.
This is required when you change the recovery model from SIMPLE to FULL
Thanks
Hari
MCDBA
"paldba" wrote:
> I recently changed the recovery model from simple to full on a specific SQL
> Server 2000 database. I am backing up the transaction log every two hours
> via a maintenance plan. I have other SQL Server 2000 DB's defined with the
> full recovery model and do not receive these messages. The following error,
> not a warning, appears in the application event log when the log backup
> completes also in the database log. Are these backups valid? Found KB
> 818202 on this, but I cannot determine what action needs to be taken, if any.
> Thoughts??
> Source: MSSQLSERVER
> Category: (6)
> Event ID: 17055
> Description: 18278 Database log truncated: Database: dbname.
|||Yes, that has been completed.
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> Can you perform a full database backup and then follow the transaction log
> backup.
> This is required when you change the recovery model from SIMPLE to FULL
> Thanks
> Hari
> MCDBA
> "paldba" wrote:
Server 2000 database. I am backing up the transaction log every two hours
via a maintenance plan. I have other SQL Server 2000 DB's defined with the
full recovery model and do not receive these messages. The following error,
not a warning, appears in the application event log when the log backup
completes also in the database log. Are these backups valid? Found KB
818202 on this, but I cannot determine what action needs to be taken, if any.
Thoughts??
Source: MSSQLSERVER
Category: (6)
Event ID: 17055
Description: 18278 Database log truncated: Database: dbname.
Hi,
Can you perform a full database backup and then follow the transaction log
backup.
This is required when you change the recovery model from SIMPLE to FULL
Thanks
Hari
MCDBA
"paldba" wrote:
> I recently changed the recovery model from simple to full on a specific SQL
> Server 2000 database. I am backing up the transaction log every two hours
> via a maintenance plan. I have other SQL Server 2000 DB's defined with the
> full recovery model and do not receive these messages. The following error,
> not a warning, appears in the application event log when the log backup
> completes also in the database log. Are these backups valid? Found KB
> 818202 on this, but I cannot determine what action needs to be taken, if any.
> Thoughts??
> Source: MSSQLSERVER
> Category: (6)
> Event ID: 17055
> Description: 18278 Database log truncated: Database: dbname.
|||Yes, that has been completed.
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> Can you perform a full database backup and then follow the transaction log
> backup.
> This is required when you change the recovery model from SIMPLE to FULL
> Thanks
> Hari
> MCDBA
> "paldba" wrote:
Friday, February 10, 2012
Backup to NULL
Hi,
I have a database that was left with recovery set to full and receving a lot
of updates. Log file has become huge but there were no transaction log
backups being done. Until I needed to trim the transaction log file now
being 25 GB (data file is only 1.5GB) because there is no more space in the
drive.
To be able to shrink it I plan to back it up first, then change the recovery
from full to simple and then shrink the log file. My problem is I don't have
any more disk space to hold the transaction log backup. Is there a way to
just backup the 20GB+ transaction log to the great void? Null? or wherever?
Please share me your insights, comments and suggestion are welcome.
TIA.
CarloVino
...
You can specify WITH TRUNCATE_ONLY on the BACKUP LOG command so that
committed data are removed from the log without creating a backup file.
Then shrink the log file to the desired size.
Afterwards, backup the database and schedule regular transaction log backups
to keep your log size reasonable.
Hope this helps.
Dan Guzman
SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
|||BACKUP LOG MyDatabase with TRUNCATE_ONLY. Look it up in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
I have a database that was left with recovery set to full and receving a lot
of updates. Log file has become huge but there were no transaction log
backups being done. Until I needed to trim the transaction log file now
being 25 GB (data file is only 1.5GB) because there is no more space in the
drive.
To be able to shrink it I plan to back it up first, then change the recovery
from full to simple and then shrink the log file. My problem is I don't have
any more disk space to hold the transaction log backup. Is there a way to
just backup the 20GB+ transaction log to the great void? Null? or wherever?
Please share me your insights, comments and suggestion are welcome.
TIA.
CarloVino
...
You can specify WITH TRUNCATE_ONLY on the BACKUP LOG command so that
committed data are removed from the log without creating a backup file.
Then shrink the log file to the desired size.
Afterwards, backup the database and schedule regular transaction log backups
to keep your log size reasonable.
Hope this helps.
Dan Guzman
SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
|||BACKUP LOG MyDatabase with TRUNCATE_ONLY. Look it up in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
Backup to NULL
Hi,
I have a database that was left with recovery set to full and receving a lot
of updates. Log file has become huge but there were no transaction log
backups being done. Until I needed to trim the transaction log file now
being 25 GB (data file is only 1.5GB) because there is no more space in the
drive.
To be able to shrink it I plan to back it up first, then change the recovery
from full to simple and then shrink the log file. My problem is I don't have
any more disk space to hold the transaction log backup. Is there a way to
just backup the 20GB+ transaction log to the great void? Null? or wherever?
Please share me your insights, comments and suggestion are welcome.
TIA.
CarloVino
--
...You can specify WITH TRUNCATE_ONLY on the BACKUP LOG command so that
committed data are removed from the log without creating a backup file.
Then shrink the log file to the desired size.
Afterwards, backup the database and schedule regular transaction log backups
to keep your log size reasonable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...|||BACKUP LOG MyDatabase with TRUNCATE_ONLY. Look it up in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
I have a database that was left with recovery set to full and receving a lot
of updates. Log file has become huge but there were no transaction log
backups being done. Until I needed to trim the transaction log file now
being 25 GB (data file is only 1.5GB) because there is no more space in the
drive.
To be able to shrink it I plan to back it up first, then change the recovery
from full to simple and then shrink the log file. My problem is I don't have
any more disk space to hold the transaction log backup. Is there a way to
just backup the 20GB+ transaction log to the great void? Null? or wherever?
Please share me your insights, comments and suggestion are welcome.
TIA.
CarloVino
--
...You can specify WITH TRUNCATE_ONLY on the BACKUP LOG command so that
committed data are removed from the log without creating a backup file.
Then shrink the log file to the desired size.
Afterwards, backup the database and schedule regular transaction log backups
to keep your log size reasonable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...|||BACKUP LOG MyDatabase with TRUNCATE_ONLY. Look it up in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
Backup to NULL
Hi,
I have a database that was left with recovery set to full and receving a lot
of updates. Log file has become huge but there were no transaction log
backups being done. Until I needed to trim the transaction log file now
being 25 GB (data file is only 1.5GB) because there is no more space in the
drive.
To be able to shrink it I plan to back it up first, then change the recovery
from full to simple and then shrink the log file. My problem is I don't hav
e
any more disk space to hold the transaction log backup. Is there a way to
just backup the 20GB+ transaction log to the great void? Null? or wherever?
Please share me your insights, comments and suggestion are welcome.
TIA.
CarloVino
--
...You can specify WITH TRUNCATE_ONLY on the BACKUP LOG command so that
committed data are removed from the log without creating a backup file.
Then shrink the log file to the desired size.
Afterwards, backup the database and schedule regular transaction log backups
to keep your log size reasonable.
Hope this helps.
Dan Guzman
SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...|||BACKUP LOG MyDatabase with TRUNCATE_ONLY. Look it up in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
I have a database that was left with recovery set to full and receving a lot
of updates. Log file has become huge but there were no transaction log
backups being done. Until I needed to trim the transaction log file now
being 25 GB (data file is only 1.5GB) because there is no more space in the
drive.
To be able to shrink it I plan to back it up first, then change the recovery
from full to simple and then shrink the log file. My problem is I don't hav
e
any more disk space to hold the transaction log backup. Is there a way to
just backup the 20GB+ transaction log to the great void? Null? or wherever?
Please share me your insights, comments and suggestion are welcome.
TIA.
CarloVino
--
...You can specify WITH TRUNCATE_ONLY on the BACKUP LOG command so that
committed data are removed from the log without creating a backup file.
Then shrink the log file to the desired size.
Afterwards, backup the database and schedule regular transaction log backups
to keep your log size reasonable.
Hope this helps.
Dan Guzman
SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...|||BACKUP LOG MyDatabase with TRUNCATE_ONLY. Look it up in BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:06741C7C-D44A-4D7E-B65B-8F2E8C23B7B1@.microsoft.com...
> Hi,
> I have a database that was left with recovery set to full and receving a
> lot
> of updates. Log file has become huge but there were no transaction log
> backups being done. Until I needed to trim the transaction log file now
> being 25 GB (data file is only 1.5GB) because there is no more space in
> the
> drive.
> To be able to shrink it I plan to back it up first, then change the
> recovery
> from full to simple and then shrink the log file. My problem is I don't
> have
> any more disk space to hold the transaction log backup. Is there a way to
> just backup the 20GB+ transaction log to the great void? Null? or
> wherever?
> Please share me your insights, comments and suggestion are welcome.
> TIA.
> CarloVino
> --
> ...
Backup to Network
We just recently experiences a catastrophic hard drive failure. It is off
right now to the data recovery folks and I hope we get something back. In
the mean time I have been asked to come up with some backup plan. Because
the backups were on the same drive as the database, this is not a good
solution. We are implementing a RAID system, but I have some questions
about the following:
1. How well does log shipping work. The data is not real-time and we could
afford to loose the little data we would produce in the time it would take
to switch to the new server?
2. Can I back up the files to a network. I would like to do a Complete
backup once a sunday, full backups once a sunday, then do incremental
backups every 15-30 minutes. I would like to make the backups to the local
drive and two network locations. How can I back up across the network?
When I set up the backups I only get my local drives (c:,d
.
We are using SQL Server 2000 (will migrate to 2005 in a year) with the
current service pack running on Win 2k3
John
Having your database on the same drive on your backup not only leaves you
vulnerable to the crash you experience but also leads to IO contention
between your backup and your database files.
1) Log shipping works well, but it is not really scalable for very large
databases or large numbers of databases. The main reason people don't use
log shipping is that their exposure to data loss is significantly greater
than with other HA technologies.
2) Yes you can but it is not recommended. You should backup locally and then
copy the backup off the local drive to a network location.
Here is one way you can do backup across the network :
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
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
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
> We just recently experiences a catastrophic hard drive failure. It is off
> right now to the data recovery folks and I hope we get something back. In
> the mean time I have been asked to come up with some backup plan. Because
> the backups were on the same drive as the database, this is not a good
> solution. We are implementing a RAID system, but I have some questions
> about the following:
> 1. How well does log shipping work. The data is not real-time and we
> could afford to loose the little data we would produce in the time it
> would take to switch to the new server?
> 2. Can I back up the files to a network. I would like to do a Complete
> backup once a sunday, full backups once a sunday, then do incremental
> backups every 15-30 minutes. I would like to make the backups to the
> local drive and two network locations. How can I back up across the
> network? When I set up the backups I only get my local drives (c:,d.
> We are using SQL Server 2000 (will migrate to 2005 in a year) with the
> current service pack running on Win 2k3
>
> John
>
|||If having the backup on the same drive as the backup leaves us this
vulnerable, then how we backup to another location. We have a c: and d:
drive (one drive two partitions) and they are the only devices that show.
It seems odd to me that SQL Server would not have the ability to backup
across the network.
When I ran the BACKUP DATABASE command I get the following error:
Operating system error 1385(Logon failure: the user has not been granted the
requested logon type at this computer.).
I am the administrator and have full rights to this location. What user is
this trying to back up to?
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Having your database on the same drive on your backup not only leaves you
> vulnerable to the crash you experience but also leads to IO contention
> between your backup and your database files.
> 1) Log shipping works well, but it is not really scalable for very large
> databases or large numbers of databases. The main reason people don't use
> log shipping is that their exposure to data loss is significantly greater
> than with other HA technologies.
> 2) Yes you can but it is not recommended. You should backup locally and
> then copy the backup off the local drive to a network location.
> Here is one way you can do backup across the network :
> BACKUP DATABASE DatabaseName TO DISK =
> '\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
> --
> 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
>
> "John Wright" <riley_wrightx@.hotmail.com> wrote in message
> news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
>
|||If you have your backup on the same drive as your datafile and this drive
fails you have lost your backup and your datafiles.
If you have your backup on a different drive and your backup fails you still
have your data files which is some level of protection.
If you have your backup on a different drive and your database gets corrupt
you still have your backup files which you can restore from.
If you backup to a local drive and immediately copy it off the network your
exposure is only in the time interval it takes to copy the backup off your
local drive.
If you backup to the network it takes longer so your vulnerability is more
than if you were to backup locally and then copy.
to backup to the network drive do this:
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
The error message tells me that the account that you are using to run the
backup does not have rights to write to the network share or the files and
folders beneath it.
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
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:%23IWAQ%23seHHA.4872@.TK2MSFTNGP03.phx.gbl...
> If having the backup on the same drive as the backup leaves us this
> vulnerable, then how we backup to another location. We have a c: and d:
> drive (one drive two partitions) and they are the only devices that show.
> It seems odd to me that SQL Server would not have the ability to backup
> across the network.
> When I ran the BACKUP DATABASE command I get the following error:
> Operating system error 1385(Logon failure: the user has not been granted
> the requested logon type at this computer.).
>
> I am the administrator and have full rights to this location. What user
> is this trying to back up to?
> John
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
right now to the data recovery folks and I hope we get something back. In
the mean time I have been asked to come up with some backup plan. Because
the backups were on the same drive as the database, this is not a good
solution. We are implementing a RAID system, but I have some questions
about the following:
1. How well does log shipping work. The data is not real-time and we could
afford to loose the little data we would produce in the time it would take
to switch to the new server?
2. Can I back up the files to a network. I would like to do a Complete
backup once a sunday, full backups once a sunday, then do incremental
backups every 15-30 minutes. I would like to make the backups to the local
drive and two network locations. How can I back up across the network?
When I set up the backups I only get my local drives (c:,d
.We are using SQL Server 2000 (will migrate to 2005 in a year) with the
current service pack running on Win 2k3
John
Having your database on the same drive on your backup not only leaves you
vulnerable to the crash you experience but also leads to IO contention
between your backup and your database files.
1) Log shipping works well, but it is not really scalable for very large
databases or large numbers of databases. The main reason people don't use
log shipping is that their exposure to data loss is significantly greater
than with other HA technologies.
2) Yes you can but it is not recommended. You should backup locally and then
copy the backup off the local drive to a network location.
Here is one way you can do backup across the network :
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
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
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
> We just recently experiences a catastrophic hard drive failure. It is off
> right now to the data recovery folks and I hope we get something back. In
> the mean time I have been asked to come up with some backup plan. Because
> the backups were on the same drive as the database, this is not a good
> solution. We are implementing a RAID system, but I have some questions
> about the following:
> 1. How well does log shipping work. The data is not real-time and we
> could afford to loose the little data we would produce in the time it
> would take to switch to the new server?
> 2. Can I back up the files to a network. I would like to do a Complete
> backup once a sunday, full backups once a sunday, then do incremental
> backups every 15-30 minutes. I would like to make the backups to the
> local drive and two network locations. How can I back up across the
> network? When I set up the backups I only get my local drives (c:,d
.> We are using SQL Server 2000 (will migrate to 2005 in a year) with the
> current service pack running on Win 2k3
>
> John
>
|||If having the backup on the same drive as the backup leaves us this
vulnerable, then how we backup to another location. We have a c: and d:
drive (one drive two partitions) and they are the only devices that show.
It seems odd to me that SQL Server would not have the ability to backup
across the network.
When I ran the BACKUP DATABASE command I get the following error:
Operating system error 1385(Logon failure: the user has not been granted the
requested logon type at this computer.).
I am the administrator and have full rights to this location. What user is
this trying to back up to?
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Having your database on the same drive on your backup not only leaves you
> vulnerable to the crash you experience but also leads to IO contention
> between your backup and your database files.
> 1) Log shipping works well, but it is not really scalable for very large
> databases or large numbers of databases. The main reason people don't use
> log shipping is that their exposure to data loss is significantly greater
> than with other HA technologies.
> 2) Yes you can but it is not recommended. You should backup locally and
> then copy the backup off the local drive to a network location.
> Here is one way you can do backup across the network :
> BACKUP DATABASE DatabaseName TO DISK =
> '\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
> --
> 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
>
> "John Wright" <riley_wrightx@.hotmail.com> wrote in message
> news:uQuhXzseHHA.1868@.TK2MSFTNGP04.phx.gbl...
>
|||If you have your backup on the same drive as your datafile and this drive
fails you have lost your backup and your datafiles.
If you have your backup on a different drive and your backup fails you still
have your data files which is some level of protection.
If you have your backup on a different drive and your database gets corrupt
you still have your backup files which you can restore from.
If you backup to a local drive and immediately copy it off the network your
exposure is only in the time interval it takes to copy the backup off your
local drive.
If you backup to the network it takes longer so your vulnerability is more
than if you were to backup locally and then copy.
to backup to the network drive do this:
BACKUP DATABASE DatabaseName TO DISK =
'\\ServerName\ShareName\DatabaseBackupName.bak' WITH INIT
The error message tells me that the account that you are using to run the
backup does not have rights to write to the network share or the files and
folders beneath it.
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
"John Wright" <riley_wrightx@.hotmail.com> wrote in message
news:%23IWAQ%23seHHA.4872@.TK2MSFTNGP03.phx.gbl...
> If having the backup on the same drive as the backup leaves us this
> vulnerable, then how we backup to another location. We have a c: and d:
> drive (one drive two partitions) and they are the only devices that show.
> It seems odd to me that SQL Server would not have the ability to backup
> across the network.
> When I ran the BACKUP DATABASE command I get the following error:
> Operating system error 1385(Logon failure: the user has not been granted
> the requested logon type at this computer.).
>
> I am the administrator and have full rights to this location. What user
> is this trying to back up to?
> John
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uV1cZ3seHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
Subscribe to:
Posts (Atom)