I am trying to come up with the best solution for the following issue:
I have a specific DB that needs to be backed up in single user mode. I
want to do this with a a Maitenance Plan but I am not sure if that is
possible or makes sense.
What are my options? Do I have to backup with a script? I would like
the Maint. Plan to kick off the ALTER DB script to kick out users.
Thanks for the help.Why is single-user mode a requirement?
That said, you can eschew the maintenance plan and just script out the
ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<wfj5444@.gmail.com> wrote in message
news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
I am trying to come up with the best solution for the following issue:
I have a specific DB that needs to be backed up in single user mode. I
want to do this with a a Maitenance Plan but I am not sure if that is
possible or makes sense.
What are my options? Do I have to backup with a script? I would like
the Maint. Plan to kick off the ALTER DB script to kick out users.
Thanks for the help.|||Tom,
is there any refrences on your method, in online books or on the web.
Thanks
"Tom Moreau" wrote:
> Why is single-user mode a requirement?
> That said, you can eschew the maintenance plan and just script out the
> ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> <wfj5444@.gmail.com> wrote in message
> news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> I am trying to come up with the best solution for the following issue:
> I have a specific DB that needs to be backed up in single user mode. I
> want to do this with a a Maitenance Plan but I am not sure if that is
> possible or makes sense.
> What are my options? Do I have to backup with a script? I would like
> the Maint. Plan to kick off the ALTER DB script to kick out users.
> Thanks for the help.
>|||Basically, check out the following in the BOL:
ALTER DATABASE
BACKUP DATABASE
sp_add_job
sp_add_jobstep
Also, I am curious as to why single-user mode is a requirement.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
Tom,
is there any refrences on your method, in online books or on the web.
Thanks
"Tom Moreau" wrote:
> Why is single-user mode a requirement?
> That said, you can eschew the maintenance plan and just script out the
> ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> <wfj5444@.gmail.com> wrote in message
> news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> I am trying to come up with the best solution for the following issue:
> I have a specific DB that needs to be backed up in single user mode. I
> want to do this with a a Maitenance Plan but I am not sure if that is
> possible or makes sense.
> What are my options? Do I have to backup with a script? I would like
> the Maint. Plan to kick off the ALTER DB script to kick out users.
> Thanks for the help.
>|||Thanks Tom.
The reason is due to a specific table that gets corrupted if users are
accessing it during backup.
I can not think of more simple solution than going single user, backing
it up and then returning to multi user.
Tom Moreau wrote:
> Basically, check out the following in the BOL:
> ALTER DATABASE
> BACKUP DATABASE
> sp_add_job
> sp_add_jobstep
> Also, I am curious as to why single-user mode is a requirement.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> Tom,
> is there any refrences on your method, in online books or on the web.
> Thanks
> "Tom Moreau" wrote:
> > Why is single-user mode a requirement?
> >
> > That said, you can eschew the maintenance plan and just script out the
> > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > <wfj5444@.gmail.com> wrote in message
> > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > I am trying to come up with the best solution for the following issue:
> >
> > I have a specific DB that needs to be backed up in single user mode. I
> > want to do this with a a Maitenance Plan but I am not sure if that is
> > possible or makes sense.
> >
> > What are my options? Do I have to backup with a script? I would like
> > the Maint. Plan to kick off the ALTER DB script to kick out users.
> >
> > Thanks for the help.
> >
> >|||If the table is getting corrupted, then it's possible that the app is not
properly designed. We'd have to know more first. You could start a
transaction and:
1) begin a transaction
2) do a SELECT on the table with TABLOCKX
3) do the backup
4) rollback the transaction
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<wfj5444@.gmail.com> wrote in message
news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
Thanks Tom.
The reason is due to a specific table that gets corrupted if users are
accessing it during backup.
I can not think of more simple solution than going single user, backing
it up and then returning to multi user.
Tom Moreau wrote:
> Basically, check out the following in the BOL:
> ALTER DATABASE
> BACKUP DATABASE
> sp_add_job
> sp_add_jobstep
> Also, I am curious as to why single-user mode is a requirement.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> Tom,
> is there any refrences on your method, in online books or on the web.
> Thanks
> "Tom Moreau" wrote:
> > Why is single-user mode a requirement?
> >
> > That said, you can eschew the maintenance plan and just script out the
> > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > <wfj5444@.gmail.com> wrote in message
> > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > I am trying to come up with the best solution for the following issue:
> >
> > I have a specific DB that needs to be backed up in single user mode. I
> > want to do this with a a Maitenance Plan but I am not sure if that is
> > possible or makes sense.
> >
> > What are my options? Do I have to backup with a script? I would like
> > the Maint. Plan to kick off the ALTER DB script to kick out users.
> >
> > Thanks for the help.
> >
> >|||Ah, before I do that let me confirm that you are correct this is an
application issue.
Since this is a boxed application, I do not want to change how it
handles this table.
Do you see any other ways to work around the problem?
Tom Moreau wrote:
> If the table is getting corrupted, then it's possible that the app is not
> properly designed. We'd have to know more first. You could start a
> transaction and:
> 1) begin a transaction
> 2) do a SELECT on the table with TABLOCKX
> 3) do the backup
> 4) rollback the transaction
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <wfj5444@.gmail.com> wrote in message
> news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> Thanks Tom.
> The reason is due to a specific table that gets corrupted if users are
> accessing it during backup.
> I can not think of more simple solution than going single user, backing
> it up and then returning to multi user.
>
>
> Tom Moreau wrote:
> > Basically, check out the following in the BOL:
> >
> > ALTER DATABASE
> > BACKUP DATABASE
> > sp_add_job
> > sp_add_jobstep
> >
> > Also, I am curious as to why single-user mode is a requirement.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> > Tom,
> > is there any refrences on your method, in online books or on the web.
> > Thanks
> >
> > "Tom Moreau" wrote:
> >
> > > Why is single-user mode a requirement?
> > >
> > > That said, you can eschew the maintenance plan and just script out the
> > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > ..
> > > <wfj5444@.gmail.com> wrote in message
> > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > > I am trying to come up with the best solution for the following issue:
> > >
> > > I have a specific DB that needs to be backed up in single user mode. I
> > > want to do this with a a Maitenance Plan but I am not sure if that is
> > > possible or makes sense.
> > >
> > > What are my options? Do I have to backup with a script? I would like
> > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> > >
> > > Thanks for the help.
> > >
> > >|||Is it that just this one table will be "corrupt" or will other tables get
corrupted as well? If it's the former, my previous method will handle it.
If it's the later, I'd be tempted to shut down the middle tier, but putting
the DB into single user mode (and killing any running connections to that
DB) would do so as well. Check out the BOL for ALTER DATABASE.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Will" <wfj5444@.gmail.com> wrote in message
news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
Ah, before I do that let me confirm that you are correct this is an
application issue.
Since this is a boxed application, I do not want to change how it
handles this table.
Do you see any other ways to work around the problem?
Tom Moreau wrote:
> If the table is getting corrupted, then it's possible that the app is not
> properly designed. We'd have to know more first. You could start a
> transaction and:
> 1) begin a transaction
> 2) do a SELECT on the table with TABLOCKX
> 3) do the backup
> 4) rollback the transaction
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <wfj5444@.gmail.com> wrote in message
> news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> Thanks Tom.
> The reason is due to a specific table that gets corrupted if users are
> accessing it during backup.
> I can not think of more simple solution than going single user, backing
> it up and then returning to multi user.
>
>
> Tom Moreau wrote:
> > Basically, check out the following in the BOL:
> >
> > ALTER DATABASE
> > BACKUP DATABASE
> > sp_add_job
> > sp_add_jobstep
> >
> > Also, I am curious as to why single-user mode is a requirement.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> > Tom,
> > is there any refrences on your method, in online books or on the web.
> > Thanks
> >
> > "Tom Moreau" wrote:
> >
> > > Why is single-user mode a requirement?
> > >
> > > That said, you can eschew the maintenance plan and just script out the
> > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > ..
> > > <wfj5444@.gmail.com> wrote in message
> > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > > I am trying to come up with the best solution for the following issue:
> > >
> > > I have a specific DB that needs to be backed up in single user mode.
> > > I
> > > want to do this with a a Maitenance Plan but I am not sure if that is
> > > possible or makes sense.
> > >
> > > What are my options? Do I have to backup with a script? I would like
> > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> > >
> > > Thanks for the help.
> > >
> > >|||Yes it is the first, the table will be corrupted causing an issue for
one type of data.
When this happens, we have to clean the table and let the app recreate
an empty version.
Sorry you refer to your former method, which method? The ALTER DB
example from above?
Tom Moreau wrote:
> Is it that just this one table will be "corrupt" or will other tables get
> corrupted as well? If it's the former, my previous method will handle it.
> If it's the later, I'd be tempted to shut down the middle tier, but putting
> the DB into single user mode (and killing any running connections to that
> DB) would do so as well. Check out the BOL for ALTER DATABASE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
> Ah, before I do that let me confirm that you are correct this is an
> application issue.
> Since this is a boxed application, I do not want to change how it
> handles this table.
> Do you see any other ways to work around the problem?
>
> Tom Moreau wrote:
> > If the table is getting corrupted, then it's possible that the app is not
> > properly designed. We'd have to know more first. You could start a
> > transaction and:
> >
> > 1) begin a transaction
> > 2) do a SELECT on the table with TABLOCKX
> > 3) do the backup
> > 4) rollback the transaction
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > <wfj5444@.gmail.com> wrote in message
> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> > Thanks Tom.
> >
> > The reason is due to a specific table that gets corrupted if users are
> > accessing it during backup.
> >
> > I can not think of more simple solution than going single user, backing
> > it up and then returning to multi user.
> >
> >
> >
> >
> > Tom Moreau wrote:
> > > Basically, check out the following in the BOL:
> > >
> > > ALTER DATABASE
> > > BACKUP DATABASE
> > > sp_add_job
> > > sp_add_jobstep
> > >
> > > Also, I am curious as to why single-user mode is a requirement.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > .
> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> > > Tom,
> > > is there any refrences on your method, in online books or on the web.
> > > Thanks
> > >
> > > "Tom Moreau" wrote:
> > >
> > > > Why is single-user mode a requirement?
> > > >
> > > > That said, you can eschew the maintenance plan and just script out the
> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> > > >
> > > > --
> > > > Tom
> > > >
> > > > ----
> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > > SQL Server MVP
> > > > Toronto, ON Canada
> > > > ..
> > > > <wfj5444@.gmail.com> wrote in message
> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > > > I am trying to come up with the best solution for the following issue:
> > > >
> > > > I have a specific DB that needs to be backed up in single user mode.
> > > > I
> > > > want to do this with a a Maitenance Plan but I am not sure if that is
> > > > possible or makes sense.
> > > >
> > > > What are my options? Do I have to backup with a script? I would like
> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> > > >
> > > > Thanks for the help.
> > > >
> > > >|||I noticed you mention corruption in a particular table - after taking the
backup
- is the backup part of a maintenance plan?
If so does the maintenance plan perform integrity check/repair and or
optimization before backup.
- How do you determine corruption of your table afterwards.
- What errors do you get when you run dbcc checkdb or checktable?
When you run select @.@.version what SQL build number are you on?
"wfj5444@.gmail.com" wrote:
> I am trying to come up with the best solution for the following issue:
> I have a specific DB that needs to be backed up in single user mode. I
> want to do this with a a Maitenance Plan but I am not sure if that is
> possible or makes sense.
> What are my options? Do I have to backup with a script? I would like
> the Maint. Plan to kick off the ALTER DB script to kick out users.
> Thanks for the help.
>|||Yes, use:
ALTER DATABASE MyDB
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE
That kicks out the users and makes the DB available only to admins - only
members of the db_owner, dbcreator, or sysadmin roles.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Will" <wfj5444@.gmail.com> wrote in message
news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
Yes it is the first, the table will be corrupted causing an issue for
one type of data.
When this happens, we have to clean the table and let the app recreate
an empty version.
Sorry you refer to your former method, which method? The ALTER DB
example from above?
Tom Moreau wrote:
> Is it that just this one table will be "corrupt" or will other tables get
> corrupted as well? If it's the former, my previous method will handle it.
> If it's the later, I'd be tempted to shut down the middle tier, but
> putting
> the DB into single user mode (and killing any running connections to that
> DB) would do so as well. Check out the BOL for ALTER DATABASE.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
> Ah, before I do that let me confirm that you are correct this is an
> application issue.
> Since this is a boxed application, I do not want to change how it
> handles this table.
> Do you see any other ways to work around the problem?
>
> Tom Moreau wrote:
> > If the table is getting corrupted, then it's possible that the app is
> > not
> > properly designed. We'd have to know more first. You could start a
> > transaction and:
> >
> > 1) begin a transaction
> > 2) do a SELECT on the table with TABLOCKX
> > 3) do the backup
> > 4) rollback the transaction
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > <wfj5444@.gmail.com> wrote in message
> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> > Thanks Tom.
> >
> > The reason is due to a specific table that gets corrupted if users are
> > accessing it during backup.
> >
> > I can not think of more simple solution than going single user, backing
> > it up and then returning to multi user.
> >
> >
> >
> >
> > Tom Moreau wrote:
> > > Basically, check out the following in the BOL:
> > >
> > > ALTER DATABASE
> > > BACKUP DATABASE
> > > sp_add_job
> > > sp_add_jobstep
> > >
> > > Also, I am curious as to why single-user mode is a requirement.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > .
> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> > > Tom,
> > > is there any refrences on your method, in online books or on the web.
> > > Thanks
> > >
> > > "Tom Moreau" wrote:
> > >
> > > > Why is single-user mode a requirement?
> > > >
> > > > That said, you can eschew the maintenance plan and just script out
> > > > the
> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> > > >
> > > > --
> > > > Tom
> > > >
> > > > ----
> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > > SQL Server MVP
> > > > Toronto, ON Canada
> > > > ..
> > > > <wfj5444@.gmail.com> wrote in message
> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > > > I am trying to come up with the best solution for the following
> > > > issue:
> > > >
> > > > I have a specific DB that needs to be backed up in single user mode.
> > > > I
> > > > want to do this with a a Maitenance Plan but I am not sure if that
> > > > is
> > > > possible or makes sense.
> > > >
> > > > What are my options? Do I have to backup with a script? I would
> > > > like
> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> > > >
> > > > Thanks for the help.
> > > >
> > > >|||"Will" <wfj5444@.gmail.com> wrote in message
news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
> Yes it is the first, the table will be corrupted causing an issue for
> one type of data.
Then call the company that wrote this POS and complain.
SQL Server is designed for online backups. Hell, it would be impossible for
many major websites to work w/o that feature.
> When this happens, we have to clean the table and let the app recreate
> an empty version.
> Sorry you refer to your former method, which method? The ALTER DB
> example from above?
> Tom Moreau wrote:
> > Is it that just this one table will be "corrupt" or will other tables
get
> > corrupted as well? If it's the former, my previous method will handle
it.
> > If it's the later, I'd be tempted to shut down the middle tier, but
putting
> > the DB into single user mode (and killing any running connections to
that
> > DB) would do so as well. Check out the BOL for ALTER DATABASE.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "Will" <wfj5444@.gmail.com> wrote in message
> > news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
> > Ah, before I do that let me confirm that you are correct this is an
> > application issue.
> > Since this is a boxed application, I do not want to change how it
> > handles this table.
> >
> > Do you see any other ways to work around the problem?
> >
> >
> > Tom Moreau wrote:
> > > If the table is getting corrupted, then it's possible that the app is
not
> > > properly designed. We'd have to know more first. You could start a
> > > transaction and:
> > >
> > > 1) begin a transaction
> > > 2) do a SELECT on the table with TABLOCKX
> > > 3) do the backup
> > > 4) rollback the transaction
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > .
> > > <wfj5444@.gmail.com> wrote in message
> > > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> > > Thanks Tom.
> > >
> > > The reason is due to a specific table that gets corrupted if users are
> > > accessing it during backup.
> > >
> > > I can not think of more simple solution than going single user,
backing
> > > it up and then returning to multi user.
> > >
> > >
> > >
> > >
> > > Tom Moreau wrote:
> > > > Basically, check out the following in the BOL:
> > > >
> > > > ALTER DATABASE
> > > > BACKUP DATABASE
> > > > sp_add_job
> > > > sp_add_jobstep
> > > >
> > > > Also, I am curious as to why single-user mode is a requirement.
> > > >
> > > > --
> > > > Tom
> > > >
> > > > ----
> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > > SQL Server MVP
> > > > Toronto, ON Canada
> > > > .
> > > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> > > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> > > > Tom,
> > > > is there any refrences on your method, in online books or on the
web.
> > > > Thanks
> > > >
> > > > "Tom Moreau" wrote:
> > > >
> > > > > Why is single-user mode a requirement?
> > > > >
> > > > > That said, you can eschew the maintenance plan and just script out
the
> > > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> > > > >
> > > > > --
> > > > > Tom
> > > > >
> > > > > ----
> > > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > > > SQL Server MVP
> > > > > Toronto, ON Canada
> > > > > ..
> > > > > <wfj5444@.gmail.com> wrote in message
> > > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> > > > > I am trying to come up with the best solution for the following
issue:
> > > > >
> > > > > I have a specific DB that needs to be backed up in single user
mode.
> > > > > I
> > > > > want to do this with a a Maitenance Plan but I am not sure if that
is
> > > > > possible or makes sense.
> > > > >
> > > > > What are my options? Do I have to backup with a script? I would
like
> > > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> > > > >
> > > > > Thanks for the help.
> > > > >
> > > > >
>|||> That kicks out the users and makes the DB available only to admins - only
> members of the db_owner, dbcreator, or sysadmin roles.
But wouldn't end result be the same as if you just did a backup with the users connected? Here's my
reasoning:
The app "doesn't support online backup". Now, why is that? Probably because they don't have correct
transaction handling. The backup can grab some data for an operation which isn't done yet, just
because the developers didn't wrap that operation in a transaction.
Now, kicking out the users will do the very same thing. Remember that backup gives us the database
as a snapshot in time (I know you know this, Tom...).
It sounds like the software vendor has produced and sell an application which doesn't allow you to
perform backup of the data. Indeed worrying! I'd have a very serious talk to the app vendor and ask
them for the technical reason why a normal backup operation won't do and also ask them in what way
kicking out the users would make a difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Yes, use:
> ALTER DATABASE MyDB
> SET RESTRICTED_USER
> WITH ROLLBACK IMMEDIATE
> That kicks out the users and makes the DB available only to admins - only
> members of the db_owner, dbcreator, or sysadmin roles.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
> Yes it is the first, the table will be corrupted causing an issue for
> one type of data.
> When this happens, we have to clean the table and let the app recreate
> an empty version.
> Sorry you refer to your former method, which method? The ALTER DB
> example from above?
> Tom Moreau wrote:
>> Is it that just this one table will be "corrupt" or will other tables get
>> corrupted as well? If it's the former, my previous method will handle it.
>> If it's the later, I'd be tempted to shut down the middle tier, but
>> putting
>> the DB into single user mode (and killing any running connections to that
>> DB) would do so as well. Check out the BOL for ALTER DATABASE.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Will" <wfj5444@.gmail.com> wrote in message
>> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
>> Ah, before I do that let me confirm that you are correct this is an
>> application issue.
>> Since this is a boxed application, I do not want to change how it
>> handles this table.
>> Do you see any other ways to work around the problem?
>>
>> Tom Moreau wrote:
>> > If the table is getting corrupted, then it's possible that the app is
>> > not
>> > properly designed. We'd have to know more first. You could start a
>> > transaction and:
>> >
>> > 1) begin a transaction
>> > 2) do a SELECT on the table with TABLOCKX
>> > 3) do the backup
>> > 4) rollback the transaction
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > .
>> > <wfj5444@.gmail.com> wrote in message
>> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
>> > Thanks Tom.
>> >
>> > The reason is due to a specific table that gets corrupted if users are
>> > accessing it during backup.
>> >
>> > I can not think of more simple solution than going single user, backing
>> > it up and then returning to multi user.
>> >
>> >
>> >
>> >
>> > Tom Moreau wrote:
>> > > Basically, check out the following in the BOL:
>> > >
>> > > ALTER DATABASE
>> > > BACKUP DATABASE
>> > > sp_add_job
>> > > sp_add_jobstep
>> > >
>> > > Also, I am curious as to why single-user mode is a requirement.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > .
>> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
>> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
>> > > Tom,
>> > > is there any refrences on your method, in online books or on the web.
>> > > Thanks
>> > >
>> > > "Tom Moreau" wrote:
>> > >
>> > > > Why is single-user mode a requirement?
>> > > >
>> > > > That said, you can eschew the maintenance plan and just script out
>> > > > the
>> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
>> > > >
>> > > > --
>> > > > Tom
>> > > >
>> > > > ----
>> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > > > SQL Server MVP
>> > > > Toronto, ON Canada
>> > > > ..
>> > > > <wfj5444@.gmail.com> wrote in message
>> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
>> > > > I am trying to come up with the best solution for the following
>> > > > issue:
>> > > >
>> > > > I have a specific DB that needs to be backed up in single user mode.
>> > > > I
>> > > > want to do this with a a Maitenance Plan but I am not sure if that
>> > > > is
>> > > > possible or makes sense.
>> > > >
>> > > > What are my options? Do I have to backup with a script? I would
>> > > > like
>> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
>> > > >
>> > > > Thanks for the help.
>> > > >
>> > > >
>|||Yes, this does sound like a very strange app.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ewInsYQlGHA.1664@.TK2MSFTNGP03.phx.gbl...
> That kicks out the users and makes the DB available only to admins - only
> members of the db_owner, dbcreator, or sysadmin roles.
But wouldn't end result be the same as if you just did a backup with the
users connected? Here's my
reasoning:
The app "doesn't support online backup". Now, why is that? Probably because
they don't have correct
transaction handling. The backup can grab some data for an operation which
isn't done yet, just
because the developers didn't wrap that operation in a transaction.
Now, kicking out the users will do the very same thing. Remember that backup
gives us the database
as a snapshot in time (I know you know this, Tom...).
It sounds like the software vendor has produced and sell an application
which doesn't allow you to
perform backup of the data. Indeed worrying! I'd have a very serious talk to
the app vendor and ask
them for the technical reason why a normal backup operation won't do and
also ask them in what way
kicking out the users would make a difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Yes, use:
> ALTER DATABASE MyDB
> SET RESTRICTED_USER
> WITH ROLLBACK IMMEDIATE
> That kicks out the users and makes the DB available only to admins - only
> members of the db_owner, dbcreator, or sysadmin roles.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
> Yes it is the first, the table will be corrupted causing an issue for
> one type of data.
> When this happens, we have to clean the table and let the app recreate
> an empty version.
> Sorry you refer to your former method, which method? The ALTER DB
> example from above?
> Tom Moreau wrote:
>> Is it that just this one table will be "corrupt" or will other tables get
>> corrupted as well? If it's the former, my previous method will handle
>> it.
>> If it's the later, I'd be tempted to shut down the middle tier, but
>> putting
>> the DB into single user mode (and killing any running connections to that
>> DB) would do so as well. Check out the BOL for ALTER DATABASE.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Will" <wfj5444@.gmail.com> wrote in message
>> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
>> Ah, before I do that let me confirm that you are correct this is an
>> application issue.
>> Since this is a boxed application, I do not want to change how it
>> handles this table.
>> Do you see any other ways to work around the problem?
>>
>> Tom Moreau wrote:
>> > If the table is getting corrupted, then it's possible that the app is
>> > not
>> > properly designed. We'd have to know more first. You could start a
>> > transaction and:
>> >
>> > 1) begin a transaction
>> > 2) do a SELECT on the table with TABLOCKX
>> > 3) do the backup
>> > 4) rollback the transaction
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > .
>> > <wfj5444@.gmail.com> wrote in message
>> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
>> > Thanks Tom.
>> >
>> > The reason is due to a specific table that gets corrupted if users are
>> > accessing it during backup.
>> >
>> > I can not think of more simple solution than going single user, backing
>> > it up and then returning to multi user.
>> >
>> >
>> >
>> >
>> > Tom Moreau wrote:
>> > > Basically, check out the following in the BOL:
>> > >
>> > > ALTER DATABASE
>> > > BACKUP DATABASE
>> > > sp_add_job
>> > > sp_add_jobstep
>> > >
>> > > Also, I am curious as to why single-user mode is a requirement.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > .
>> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
>> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
>> > > Tom,
>> > > is there any refrences on your method, in online books or on the web.
>> > > Thanks
>> > >
>> > > "Tom Moreau" wrote:
>> > >
>> > > > Why is single-user mode a requirement?
>> > > >
>> > > > That said, you can eschew the maintenance plan and just script out
>> > > > the
>> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
>> > > >
>> > > > --
>> > > > Tom
>> > > >
>> > > > ----
>> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > > > SQL Server MVP
>> > > > Toronto, ON Canada
>> > > > ..
>> > > > <wfj5444@.gmail.com> wrote in message
>> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
>> > > > I am trying to come up with the best solution for the following
>> > > > issue:
>> > > >
>> > > > I have a specific DB that needs to be backed up in single user
>> > > > mode.
>> > > > I
>> > > > want to do this with a a Maitenance Plan but I am not sure if that
>> > > > is
>> > > > possible or makes sense.
>> > > >
>> > > > What are my options? Do I have to backup with a script? I would
>> > > > like
>> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
>> > > >
>> > > > Thanks for the help.
>> > > >
>> > > >
>|||Sassan,
It is part of the maintenance plan. We do optimize (remove unused
space) but that is all no other fixing.
Corruption is determined by certain data is missing.
With a checktable there are no errors
We are at build 2187
Sassan Karai wrote:
> I noticed you mention corruption in a particular table - after taking the
> backup
> - is the backup part of a maintenance plan?
> If so does the maintenance plan perform integrity check/repair and or
> optimization before backup.
> - How do you determine corruption of your table afterwards.
> - What errors do you get when you run dbcc checkdb or checktable?
> When you run select @.@.version what SQL build number are you on?
>
> "wfj5444@.gmail.com" wrote:
> > I am trying to come up with the best solution for the following issue:
> >
> > I have a specific DB that needs to be backed up in single user mode. I
> > want to do this with a a Maitenance Plan but I am not sure if that is
> > possible or makes sense.
> >
> > What are my options? Do I have to backup with a script? I would like
> > the Maint. Plan to kick off the ALTER DB script to kick out users.
> >
> > Thanks for the help.
> >
> >|||Greg and Tibor
Greg - We have and they know it is an issue. It is only this specific
table that has the issue.
Tibor - I actually want to make it single user rather than restricted
user. Would that not solve the issue?
The app does support online backup. This is a bug and something they
are going to fix.
Tibor Karaszi wrote:
> > That kicks out the users and makes the DB available only to admins - only
> > members of the db_owner, dbcreator, or sysadmin roles.
> But wouldn't end result be the same as if you just did a backup with the users connected? Here's my
> reasoning:
> The app "doesn't support online backup". Now, why is that? Probably because they don't have correct
> transaction handling. The backup can grab some data for an operation which isn't done yet, just
> because the developers didn't wrap that operation in a transaction.
> Now, kicking out the users will do the very same thing. Remember that backup gives us the database
> as a snapshot in time (I know you know this, Tom...).
> It sounds like the software vendor has produced and sell an application which doesn't allow you to
> perform backup of the data. Indeed worrying! I'd have a very serious talk to the app vendor and ask
> them for the technical reason why a normal backup operation won't do and also ask them in what way
> kicking out the users would make a difference.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
> > Yes, use:
> >
> > ALTER DATABASE MyDB
> > SET RESTRICTED_USER
> > WITH ROLLBACK IMMEDIATE
> >
> > That kicks out the users and makes the DB available only to admins - only
> > members of the db_owner, dbcreator, or sysadmin roles.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "Will" <wfj5444@.gmail.com> wrote in message
> > news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
> > Yes it is the first, the table will be corrupted causing an issue for
> > one type of data.
> >
> > When this happens, we have to clean the table and let the app recreate
> > an empty version.
> >
> > Sorry you refer to your former method, which method? The ALTER DB
> > example from above?
> >
> > Tom Moreau wrote:
> >> Is it that just this one table will be "corrupt" or will other tables get
> >> corrupted as well? If it's the former, my previous method will handle it.
> >> If it's the later, I'd be tempted to shut down the middle tier, but
> >> putting
> >> the DB into single user mode (and killing any running connections to that
> >> DB) would do so as well. Check out the BOL for ALTER DATABASE.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> .
> >> "Will" <wfj5444@.gmail.com> wrote in message
> >> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
> >> Ah, before I do that let me confirm that you are correct this is an
> >> application issue.
> >> Since this is a boxed application, I do not want to change how it
> >> handles this table.
> >>
> >> Do you see any other ways to work around the problem?
> >>
> >>
> >> Tom Moreau wrote:
> >> > If the table is getting corrupted, then it's possible that the app is
> >> > not
> >> > properly designed. We'd have to know more first. You could start a
> >> > transaction and:
> >> >
> >> > 1) begin a transaction
> >> > 2) do a SELECT on the table with TABLOCKX
> >> > 3) do the backup
> >> > 4) rollback the transaction
> >> >
> >> > --
> >> > Tom
> >> >
> >> > ----
> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> > SQL Server MVP
> >> > Toronto, ON Canada
> >> > .
> >> > <wfj5444@.gmail.com> wrote in message
> >> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> >> > Thanks Tom.
> >> >
> >> > The reason is due to a specific table that gets corrupted if users are
> >> > accessing it during backup.
> >> >
> >> > I can not think of more simple solution than going single user, backing
> >> > it up and then returning to multi user.
> >> >
> >> >
> >> >
> >> >
> >> > Tom Moreau wrote:
> >> > > Basically, check out the following in the BOL:
> >> > >
> >> > > ALTER DATABASE
> >> > > BACKUP DATABASE
> >> > > sp_add_job
> >> > > sp_add_jobstep
> >> > >
> >> > > Also, I am curious as to why single-user mode is a requirement.
> >> > >
> >> > > --
> >> > > Tom
> >> > >
> >> > > ----
> >> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> > > SQL Server MVP
> >> > > Toronto, ON Canada
> >> > > .
> >> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> >> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> >> > > Tom,
> >> > > is there any refrences on your method, in online books or on the web.
> >> > > Thanks
> >> > >
> >> > > "Tom Moreau" wrote:
> >> > >
> >> > > > Why is single-user mode a requirement?
> >> > > >
> >> > > > That said, you can eschew the maintenance plan and just script out
> >> > > > the
> >> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> >> > > >
> >> > > > --
> >> > > > Tom
> >> > > >
> >> > > > ----
> >> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> > > > SQL Server MVP
> >> > > > Toronto, ON Canada
> >> > > > ..
> >> > > > <wfj5444@.gmail.com> wrote in message
> >> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> >> > > > I am trying to come up with the best solution for the following
> >> > > > issue:
> >> > > >
> >> > > > I have a specific DB that needs to be backed up in single user mode.
> >> > > > I
> >> > > > want to do this with a a Maitenance Plan but I am not sure if that
> >> > > > is
> >> > > > possible or makes sense.
> >> > > >
> >> > > > What are my options? Do I have to backup with a script? I would
> >> > > > like
> >> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> >> > > >
> >> > > > Thanks for the help.
> >> > > >
> >> > > >
> >|||> Tibor - I actually want to make it single user rather than restricted
> user. Would that not solve the issue?
Not the one I was getting at. If the problem is as I described, the application does several things
that should be all or nothing, but not transaction protected, so you go in and grab the data while
this is running, then kicking out users will be no different from just doing a database backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Will" <wfj5444@.gmail.com> wrote in message
news:1150891303.954334.46680@.y41g2000cwy.googlegroups.com...
> Greg and Tibor
> Greg - We have and they know it is an issue. It is only this specific
> table that has the issue.
> Tibor - I actually want to make it single user rather than restricted
> user. Would that not solve the issue?
> The app does support online backup. This is a bug and something they
> are going to fix.
>
> Tibor Karaszi wrote:
>> > That kicks out the users and makes the DB available only to admins - only
>> > members of the db_owner, dbcreator, or sysadmin roles.
>> But wouldn't end result be the same as if you just did a backup with the users connected? Here's
>> my
>> reasoning:
>> The app "doesn't support online backup". Now, why is that? Probably because they don't have
>> correct
>> transaction handling. The backup can grab some data for an operation which isn't done yet, just
>> because the developers didn't wrap that operation in a transaction.
>> Now, kicking out the users will do the very same thing. Remember that backup gives us the
>> database
>> as a snapshot in time (I know you know this, Tom...).
>> It sounds like the software vendor has produced and sell an application which doesn't allow you
>> to
>> perform backup of the data. Indeed worrying! I'd have a very serious talk to the app vendor and
>> ask
>> them for the technical reason why a normal backup operation won't do and also ask them in what
>> way
>> kicking out the users would make a difference.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
>> > Yes, use:
>> >
>> > ALTER DATABASE MyDB
>> > SET RESTRICTED_USER
>> > WITH ROLLBACK IMMEDIATE
>> >
>> > That kicks out the users and makes the DB available only to admins - only
>> > members of the db_owner, dbcreator, or sysadmin roles.
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > .
>> > "Will" <wfj5444@.gmail.com> wrote in message
>> > news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
>> > Yes it is the first, the table will be corrupted causing an issue for
>> > one type of data.
>> >
>> > When this happens, we have to clean the table and let the app recreate
>> > an empty version.
>> >
>> > Sorry you refer to your former method, which method? The ALTER DB
>> > example from above?
>> >
>> > Tom Moreau wrote:
>> >> Is it that just this one table will be "corrupt" or will other tables get
>> >> corrupted as well? If it's the former, my previous method will handle it.
>> >> If it's the later, I'd be tempted to shut down the middle tier, but
>> >> putting
>> >> the DB into single user mode (and killing any running connections to that
>> >> DB) would do so as well. Check out the BOL for ALTER DATABASE.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Toronto, ON Canada
>> >> .
>> >> "Will" <wfj5444@.gmail.com> wrote in message
>> >> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
>> >> Ah, before I do that let me confirm that you are correct this is an
>> >> application issue.
>> >> Since this is a boxed application, I do not want to change how it
>> >> handles this table.
>> >>
>> >> Do you see any other ways to work around the problem?
>> >>
>> >>
>> >> Tom Moreau wrote:
>> >> > If the table is getting corrupted, then it's possible that the app is
>> >> > not
>> >> > properly designed. We'd have to know more first. You could start a
>> >> > transaction and:
>> >> >
>> >> > 1) begin a transaction
>> >> > 2) do a SELECT on the table with TABLOCKX
>> >> > 3) do the backup
>> >> > 4) rollback the transaction
>> >> >
>> >> > --
>> >> > Tom
>> >> >
>> >> > ----
>> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> > SQL Server MVP
>> >> > Toronto, ON Canada
>> >> > .
>> >> > <wfj5444@.gmail.com> wrote in message
>> >> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
>> >> > Thanks Tom.
>> >> >
>> >> > The reason is due to a specific table that gets corrupted if users are
>> >> > accessing it during backup.
>> >> >
>> >> > I can not think of more simple solution than going single user, backing
>> >> > it up and then returning to multi user.
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Tom Moreau wrote:
>> >> > > Basically, check out the following in the BOL:
>> >> > >
>> >> > > ALTER DATABASE
>> >> > > BACKUP DATABASE
>> >> > > sp_add_job
>> >> > > sp_add_jobstep
>> >> > >
>> >> > > Also, I am curious as to why single-user mode is a requirement.
>> >> > >
>> >> > > --
>> >> > > Tom
>> >> > >
>> >> > > ----
>> >> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> > > SQL Server MVP
>> >> > > Toronto, ON Canada
>> >> > > .
>> >> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
>> >> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
>> >> > > Tom,
>> >> > > is there any refrences on your method, in online books or on the web.
>> >> > > Thanks
>> >> > >
>> >> > > "Tom Moreau" wrote:
>> >> > >
>> >> > > > Why is single-user mode a requirement?
>> >> > > >
>> >> > > > That said, you can eschew the maintenance plan and just script out
>> >> > > > the
>> >> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
>> >> > > >
>> >> > > > --
>> >> > > > Tom
>> >> > > >
>> >> > > > ----
>> >> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> > > > SQL Server MVP
>> >> > > > Toronto, ON Canada
>> >> > > > ..
>> >> > > > <wfj5444@.gmail.com> wrote in message
>> >> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
>> >> > > > I am trying to come up with the best solution for the following
>> >> > > > issue:
>> >> > > >
>> >> > > > I have a specific DB that needs to be backed up in single user mode.
>> >> > > > I
>> >> > > > want to do this with a a Maitenance Plan but I am not sure if that
>> >> > > > is
>> >> > > > possible or makes sense.
>> >> > > >
>> >> > > > What are my options? Do I have to backup with a script? I would
>> >> > > > like
>> >> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
>> >> > > >
>> >> > > > Thanks for the help.
>> >> > > >
>> >> > > >
>> >
>|||That confuses me somewhat because the maker's work around is to close
all open instances of this application then back up that one table.
Wouldn't going to single user mode achieve the same thing?
Tibor Karaszi wrote:
> > Tibor - I actually want to make it single user rather than restricted
> > user. Would that not solve the issue?
> Not the one I was getting at. If the problem is as I described, the application does several things
> that should be all or nothing, but not transaction protected, so you go in and grab the data while
> this is running, then kicking out users will be no different from just doing a database backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1150891303.954334.46680@.y41g2000cwy.googlegroups.com...
> > Greg and Tibor
> >
> > Greg - We have and they know it is an issue. It is only this specific
> > table that has the issue.
> >
> > Tibor - I actually want to make it single user rather than restricted
> > user. Would that not solve the issue?
> >
> > The app does support online backup. This is a bug and something they
> > are going to fix.
> >
> >
> >
> > Tibor Karaszi wrote:
> >> > That kicks out the users and makes the DB available only to admins - only
> >> > members of the db_owner, dbcreator, or sysadmin roles.
> >>
> >> But wouldn't end result be the same as if you just did a backup with the users connected? Here's
> >> my
> >> reasoning:
> >>
> >> The app "doesn't support online backup". Now, why is that? Probably because they don't have
> >> correct
> >> transaction handling. The backup can grab some data for an operation which isn't done yet, just
> >> because the developers didn't wrap that operation in a transaction.
> >>
> >> Now, kicking out the users will do the very same thing. Remember that backup gives us the
> >> database
> >> as a snapshot in time (I know you know this, Tom...).
> >>
> >> It sounds like the software vendor has produced and sell an application which doesn't allow you
> >> to
> >> perform backup of the data. Indeed worrying! I'd have a very serious talk to the app vendor and
> >> ask
> >> them for the technical reason why a normal backup operation won't do and also ask them in what
> >> way
> >> kicking out the users would make a difference.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> >> news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
> >> > Yes, use:
> >> >
> >> > ALTER DATABASE MyDB
> >> > SET RESTRICTED_USER
> >> > WITH ROLLBACK IMMEDIATE
> >> >
> >> > That kicks out the users and makes the DB available only to admins - only
> >> > members of the db_owner, dbcreator, or sysadmin roles.
> >> >
> >> > --
> >> > Tom
> >> >
> >> > ----
> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> > SQL Server MVP
> >> > Toronto, ON Canada
> >> > .
> >> > "Will" <wfj5444@.gmail.com> wrote in message
> >> > news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
> >> > Yes it is the first, the table will be corrupted causing an issue for
> >> > one type of data.
> >> >
> >> > When this happens, we have to clean the table and let the app recreate
> >> > an empty version.
> >> >
> >> > Sorry you refer to your former method, which method? The ALTER DB
> >> > example from above?
> >> >
> >> > Tom Moreau wrote:
> >> >> Is it that just this one table will be "corrupt" or will other tables get
> >> >> corrupted as well? If it's the former, my previous method will handle it.
> >> >> If it's the later, I'd be tempted to shut down the middle tier, but
> >> >> putting
> >> >> the DB into single user mode (and killing any running connections to that
> >> >> DB) would do so as well. Check out the BOL for ALTER DATABASE.
> >> >>
> >> >> --
> >> >> Tom
> >> >>
> >> >> ----
> >> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> SQL Server MVP
> >> >> Toronto, ON Canada
> >> >> .
> >> >> "Will" <wfj5444@.gmail.com> wrote in message
> >> >> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
> >> >> Ah, before I do that let me confirm that you are correct this is an
> >> >> application issue.
> >> >> Since this is a boxed application, I do not want to change how it
> >> >> handles this table.
> >> >>
> >> >> Do you see any other ways to work around the problem?
> >> >>
> >> >>
> >> >> Tom Moreau wrote:
> >> >> > If the table is getting corrupted, then it's possible that the app is
> >> >> > not
> >> >> > properly designed. We'd have to know more first. You could start a
> >> >> > transaction and:
> >> >> >
> >> >> > 1) begin a transaction
> >> >> > 2) do a SELECT on the table with TABLOCKX
> >> >> > 3) do the backup
> >> >> > 4) rollback the transaction
> >> >> >
> >> >> > --
> >> >> > Tom
> >> >> >
> >> >> > ----
> >> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> > SQL Server MVP
> >> >> > Toronto, ON Canada
> >> >> > .
> >> >> > <wfj5444@.gmail.com> wrote in message
> >> >> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> >> >> > Thanks Tom.
> >> >> >
> >> >> > The reason is due to a specific table that gets corrupted if users are
> >> >> > accessing it during backup.
> >> >> >
> >> >> > I can not think of more simple solution than going single user, backing
> >> >> > it up and then returning to multi user.
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > Tom Moreau wrote:
> >> >> > > Basically, check out the following in the BOL:
> >> >> > >
> >> >> > > ALTER DATABASE
> >> >> > > BACKUP DATABASE
> >> >> > > sp_add_job
> >> >> > > sp_add_jobstep
> >> >> > >
> >> >> > > Also, I am curious as to why single-user mode is a requirement.
> >> >> > >
> >> >> > > --
> >> >> > > Tom
> >> >> > >
> >> >> > > ----
> >> >> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> > > SQL Server MVP
> >> >> > > Toronto, ON Canada
> >> >> > > .
> >> >> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> >> >> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> >> >> > > Tom,
> >> >> > > is there any refrences on your method, in online books or on the web.
> >> >> > > Thanks
> >> >> > >
> >> >> > > "Tom Moreau" wrote:
> >> >> > >
> >> >> > > > Why is single-user mode a requirement?
> >> >> > > >
> >> >> > > > That said, you can eschew the maintenance plan and just script out
> >> >> > > > the
> >> >> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> >> >> > > >
> >> >> > > > --
> >> >> > > > Tom
> >> >> > > >
> >> >> > > > ----
> >> >> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> > > > SQL Server MVP
> >> >> > > > Toronto, ON Canada
> >> >> > > > ..
> >> >> > > > <wfj5444@.gmail.com> wrote in message
> >> >> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> >> >> > > > I am trying to come up with the best solution for the following
> >> >> > > > issue:
> >> >> > > >
> >> >> > > > I have a specific DB that needs to be backed up in single user mode.
> >> >> > > > I
> >> >> > > > want to do this with a a Maitenance Plan but I am not sure if that
> >> >> > > > is
> >> >> > > > possible or makes sense.
> >> >> > > >
> >> >> > > > What are my options? Do I have to backup with a script? I would
> >> >> > > > like
> >> >> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> >> >> > > >
> >> >> > > > Thanks for the help.
> >> >> > > >
> >> >> > > >
> >> >
> >|||Closing the application in a controlled manner is different from kicking out the users from the
database. This is a very important difference. Kicking out the users will leave you with same effect
as just doing a backup. Do what the vendor say and push the vendor for writing a proper application
that support doing backup the same way as the rest of us do :-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Will" <wfj5444@.gmail.com> wrote in message
news:1150914720.910034.267610@.c74g2000cwc.googlegroups.com...
> That confuses me somewhat because the maker's work around is to close
> all open instances of this application then back up that one table.
> Wouldn't going to single user mode achieve the same thing?
> Tibor Karaszi wrote:
>> > Tibor - I actually want to make it single user rather than restricted
>> > user. Would that not solve the issue?
>> Not the one I was getting at. If the problem is as I described, the application does several
>> things
>> that should be all or nothing, but not transaction protected, so you go in and grab the data
>> while
>> this is running, then kicking out users will be no different from just doing a database backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Will" <wfj5444@.gmail.com> wrote in message
>> news:1150891303.954334.46680@.y41g2000cwy.googlegroups.com...
>> > Greg and Tibor
>> >
>> > Greg - We have and they know it is an issue. It is only this specific
>> > table that has the issue.
>> >
>> > Tibor - I actually want to make it single user rather than restricted
>> > user. Would that not solve the issue?
>> >
>> > The app does support online backup. This is a bug and something they
>> > are going to fix.
>> >
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> > That kicks out the users and makes the DB available only to admins - only
>> >> > members of the db_owner, dbcreator, or sysadmin roles.
>> >>
>> >> But wouldn't end result be the same as if you just did a backup with the users connected?
>> >> Here's
>> >> my
>> >> reasoning:
>> >>
>> >> The app "doesn't support online backup". Now, why is that? Probably because they don't have
>> >> correct
>> >> transaction handling. The backup can grab some data for an operation which isn't done yet,
>> >> just
>> >> because the developers didn't wrap that operation in a transaction.
>> >>
>> >> Now, kicking out the users will do the very same thing. Remember that backup gives us the
>> >> database
>> >> as a snapshot in time (I know you know this, Tom...).
>> >>
>> >> It sounds like the software vendor has produced and sell an application which doesn't allow
>> >> you
>> >> to
>> >> perform backup of the data. Indeed worrying! I'd have a very serious talk to the app vendor
>> >> and
>> >> ask
>> >> them for the technical reason why a normal backup operation won't do and also ask them in what
>> >> way
>> >> kicking out the users would make a difference.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> >> news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
>> >> > Yes, use:
>> >> >
>> >> > ALTER DATABASE MyDB
>> >> > SET RESTRICTED_USER
>> >> > WITH ROLLBACK IMMEDIATE
>> >> >
>> >> > That kicks out the users and makes the DB available only to admins - only
>> >> > members of the db_owner, dbcreator, or sysadmin roles.
>> >> >
>> >> > --
>> >> > Tom
>> >> >
>> >> > ----
>> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> > SQL Server MVP
>> >> > Toronto, ON Canada
>> >> > .
>> >> > "Will" <wfj5444@.gmail.com> wrote in message
>> >> > news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
>> >> > Yes it is the first, the table will be corrupted causing an issue for
>> >> > one type of data.
>> >> >
>> >> > When this happens, we have to clean the table and let the app recreate
>> >> > an empty version.
>> >> >
>> >> > Sorry you refer to your former method, which method? The ALTER DB
>> >> > example from above?
>> >> >
>> >> > Tom Moreau wrote:
>> >> >> Is it that just this one table will be "corrupt" or will other tables get
>> >> >> corrupted as well? If it's the former, my previous method will handle it.
>> >> >> If it's the later, I'd be tempted to shut down the middle tier, but
>> >> >> putting
>> >> >> the DB into single user mode (and killing any running connections to that
>> >> >> DB) would do so as well. Check out the BOL for ALTER DATABASE.
>> >> >>
>> >> >> --
>> >> >> Tom
>> >> >>
>> >> >> ----
>> >> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> >> SQL Server MVP
>> >> >> Toronto, ON Canada
>> >> >> .
>> >> >> "Will" <wfj5444@.gmail.com> wrote in message
>> >> >> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
>> >> >> Ah, before I do that let me confirm that you are correct this is an
>> >> >> application issue.
>> >> >> Since this is a boxed application, I do not want to change how it
>> >> >> handles this table.
>> >> >>
>> >> >> Do you see any other ways to work around the problem?
>> >> >>
>> >> >>
>> >> >> Tom Moreau wrote:
>> >> >> > If the table is getting corrupted, then it's possible that the app is
>> >> >> > not
>> >> >> > properly designed. We'd have to know more first. You could start a
>> >> >> > transaction and:
>> >> >> >
>> >> >> > 1) begin a transaction
>> >> >> > 2) do a SELECT on the table with TABLOCKX
>> >> >> > 3) do the backup
>> >> >> > 4) rollback the transaction
>> >> >> >
>> >> >> > --
>> >> >> > Tom
>> >> >> >
>> >> >> > ----
>> >> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> >> > SQL Server MVP
>> >> >> > Toronto, ON Canada
>> >> >> > .
>> >> >> > <wfj5444@.gmail.com> wrote in message
>> >> >> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
>> >> >> > Thanks Tom.
>> >> >> >
>> >> >> > The reason is due to a specific table that gets corrupted if users are
>> >> >> > accessing it during backup.
>> >> >> >
>> >> >> > I can not think of more simple solution than going single user, backing
>> >> >> > it up and then returning to multi user.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > Tom Moreau wrote:
>> >> >> > > Basically, check out the following in the BOL:
>> >> >> > >
>> >> >> > > ALTER DATABASE
>> >> >> > > BACKUP DATABASE
>> >> >> > > sp_add_job
>> >> >> > > sp_add_jobstep
>> >> >> > >
>> >> >> > > Also, I am curious as to why single-user mode is a requirement.
>> >> >> > >
>> >> >> > > --
>> >> >> > > Tom
>> >> >> > >
>> >> >> > > ----
>> >> >> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> >> > > SQL Server MVP
>> >> >> > > Toronto, ON Canada
>> >> >> > > .
>> >> >> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
>> >> >> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
>> >> >> > > Tom,
>> >> >> > > is there any refrences on your method, in online books or on the web.
>> >> >> > > Thanks
>> >> >> > >
>> >> >> > > "Tom Moreau" wrote:
>> >> >> > >
>> >> >> > > > Why is single-user mode a requirement?
>> >> >> > > >
>> >> >> > > > That said, you can eschew the maintenance plan and just script out
>> >> >> > > > the
>> >> >> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
>> >> >> > > >
>> >> >> > > > --
>> >> >> > > > Tom
>> >> >> > > >
>> >> >> > > > ----
>> >> >> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> >> > > > SQL Server MVP
>> >> >> > > > Toronto, ON Canada
>> >> >> > > > ..
>> >> >> > > > <wfj5444@.gmail.com> wrote in message
>> >> >> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
>> >> >> > > > I am trying to come up with the best solution for the following
>> >> >> > > > issue:
>> >> >> > > >
>> >> >> > > > I have a specific DB that needs to be backed up in single user mode.
>> >> >> > > > I
>> >> >> > > > want to do this with a a Maitenance Plan but I am not sure if that
>> >> >> > > > is
>> >> >> > > > possible or makes sense.
>> >> >> > > >
>> >> >> > > > What are my options? Do I have to backup with a script? I would
>> >> >> > > > like
>> >> >> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
>> >> >> > > >
>> >> >> > > > Thanks for the help.
>> >> >> > > >
>> >> >> > > >
>> >> >
>> >
>|||Well dang. I was hoping that wasn't the case. I was afraid of where
that was headed.
Thanks,
Will
Tibor Karaszi wrote:
> Closing the application in a controlled manner is different from kicking out the users from the
> database. This is a very important difference. Kicking out the users will leave you with same effect
> as just doing a backup. Do what the vendor say and push the vendor for writing a proper application
> that support doing backup the same way as the rest of us do :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1150914720.910034.267610@.c74g2000cwc.googlegroups.com...
> > That confuses me somewhat because the maker's work around is to close
> > all open instances of this application then back up that one table.
> >
> > Wouldn't going to single user mode achieve the same thing?
> >
> > Tibor Karaszi wrote:
> >> > Tibor - I actually want to make it single user rather than restricted
> >> > user. Would that not solve the issue?
> >>
> >> Not the one I was getting at. If the problem is as I described, the application does several
> >> things
> >> that should be all or nothing, but not transaction protected, so you go in and grab the data
> >> while
> >> this is running, then kicking out users will be no different from just doing a database backup.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Will" <wfj5444@.gmail.com> wrote in message
> >> news:1150891303.954334.46680@.y41g2000cwy.googlegroups.com...
> >> > Greg and Tibor
> >> >
> >> > Greg - We have and they know it is an issue. It is only this specific
> >> > table that has the issue.
> >> >
> >> > Tibor - I actually want to make it single user rather than restricted
> >> > user. Would that not solve the issue?
> >> >
> >> > The app does support online backup. This is a bug and something they
> >> > are going to fix.
> >> >
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> > That kicks out the users and makes the DB available only to admins - only
> >> >> > members of the db_owner, dbcreator, or sysadmin roles.
> >> >>
> >> >> But wouldn't end result be the same as if you just did a backup with the users connected?
> >> >> Here's
> >> >> my
> >> >> reasoning:
> >> >>
> >> >> The app "doesn't support online backup". Now, why is that? Probably because they don't have
> >> >> correct
> >> >> transaction handling. The backup can grab some data for an operation which isn't done yet,
> >> >> just
> >> >> because the developers didn't wrap that operation in a transaction.
> >> >>
> >> >> Now, kicking out the users will do the very same thing. Remember that backup gives us the
> >> >> database
> >> >> as a snapshot in time (I know you know this, Tom...).
> >> >>
> >> >> It sounds like the software vendor has produced and sell an application which doesn't allow
> >> >> you
> >> >> to
> >> >> perform backup of the data. Indeed worrying! I'd have a very serious talk to the app vendor
> >> >> and
> >> >> ask
> >> >> them for the technical reason why a normal backup operation won't do and also ask them in what
> >> >> way
> >> >> kicking out the users would make a difference.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> >> >> news:uVA8pBMlGHA.1640@.TK2MSFTNGP02.phx.gbl...
> >> >> > Yes, use:
> >> >> >
> >> >> > ALTER DATABASE MyDB
> >> >> > SET RESTRICTED_USER
> >> >> > WITH ROLLBACK IMMEDIATE
> >> >> >
> >> >> > That kicks out the users and makes the DB available only to admins - only
> >> >> > members of the db_owner, dbcreator, or sysadmin roles.
> >> >> >
> >> >> > --
> >> >> > Tom
> >> >> >
> >> >> > ----
> >> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> > SQL Server MVP
> >> >> > Toronto, ON Canada
> >> >> > .
> >> >> > "Will" <wfj5444@.gmail.com> wrote in message
> >> >> > news:1150833647.087347.294880@.u72g2000cwu.googlegroups.com...
> >> >> > Yes it is the first, the table will be corrupted causing an issue for
> >> >> > one type of data.
> >> >> >
> >> >> > When this happens, we have to clean the table and let the app recreate
> >> >> > an empty version.
> >> >> >
> >> >> > Sorry you refer to your former method, which method? The ALTER DB
> >> >> > example from above?
> >> >> >
> >> >> > Tom Moreau wrote:
> >> >> >> Is it that just this one table will be "corrupt" or will other tables get
> >> >> >> corrupted as well? If it's the former, my previous method will handle it.
> >> >> >> If it's the later, I'd be tempted to shut down the middle tier, but
> >> >> >> putting
> >> >> >> the DB into single user mode (and killing any running connections to that
> >> >> >> DB) would do so as well. Check out the BOL for ALTER DATABASE.
> >> >> >>
> >> >> >> --
> >> >> >> Tom
> >> >> >>
> >> >> >> ----
> >> >> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> >> SQL Server MVP
> >> >> >> Toronto, ON Canada
> >> >> >> .
> >> >> >> "Will" <wfj5444@.gmail.com> wrote in message
> >> >> >> news:1150831918.413415.186750@.h76g2000cwa.googlegroups.com...
> >> >> >> Ah, before I do that let me confirm that you are correct this is an
> >> >> >> application issue.
> >> >> >> Since this is a boxed application, I do not want to change how it
> >> >> >> handles this table.
> >> >> >>
> >> >> >> Do you see any other ways to work around the problem?
> >> >> >>
> >> >> >>
> >> >> >> Tom Moreau wrote:
> >> >> >> > If the table is getting corrupted, then it's possible that the app is
> >> >> >> > not
> >> >> >> > properly designed. We'd have to know more first. You could start a
> >> >> >> > transaction and:
> >> >> >> >
> >> >> >> > 1) begin a transaction
> >> >> >> > 2) do a SELECT on the table with TABLOCKX
> >> >> >> > 3) do the backup
> >> >> >> > 4) rollback the transaction
> >> >> >> >
> >> >> >> > --
> >> >> >> > Tom
> >> >> >> >
> >> >> >> > ----
> >> >> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> >> > SQL Server MVP
> >> >> >> > Toronto, ON Canada
> >> >> >> > .
> >> >> >> > <wfj5444@.gmail.com> wrote in message
> >> >> >> > news:1150830179.091800.37840@.y41g2000cwy.googlegroups.com...
> >> >> >> > Thanks Tom.
> >> >> >> >
> >> >> >> > The reason is due to a specific table that gets corrupted if users are
> >> >> >> > accessing it during backup.
> >> >> >> >
> >> >> >> > I can not think of more simple solution than going single user, backing
> >> >> >> > it up and then returning to multi user.
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> > Tom Moreau wrote:
> >> >> >> > > Basically, check out the following in the BOL:
> >> >> >> > >
> >> >> >> > > ALTER DATABASE
> >> >> >> > > BACKUP DATABASE
> >> >> >> > > sp_add_job
> >> >> >> > > sp_add_jobstep
> >> >> >> > >
> >> >> >> > > Also, I am curious as to why single-user mode is a requirement.
> >> >> >> > >
> >> >> >> > > --
> >> >> >> > > Tom
> >> >> >> > >
> >> >> >> > > ----
> >> >> >> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> >> > > SQL Server MVP
> >> >> >> > > Toronto, ON Canada
> >> >> >> > > .
> >> >> >> > > "SQLboston" <SQLboston@.discussions.microsoft.com> wrote in message
> >> >> >> > > news:BF33532D-DDDF-486F-8586-2E01F1753780@.microsoft.com...
> >> >> >> > > Tom,
> >> >> >> > > is there any refrences on your method, in online books or on the web.
> >> >> >> > > Thanks
> >> >> >> > >
> >> >> >> > > "Tom Moreau" wrote:
> >> >> >> > >
> >> >> >> > > > Why is single-user mode a requirement?
> >> >> >> > > >
> >> >> >> > > > That said, you can eschew the maintenance plan and just script out
> >> >> >> > > > the
> >> >> >> > > > ALTER, BACKUP and ALTER steps in a regular SQL Agent job.
> >> >> >> > > >
> >> >> >> > > > --
> >> >> >> > > > Tom
> >> >> >> > > >
> >> >> >> > > > ----
> >> >> >> > > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> >> >> > > > SQL Server MVP
> >> >> >> > > > Toronto, ON Canada
> >> >> >> > > > ..
> >> >> >> > > > <wfj5444@.gmail.com> wrote in message
> >> >> >> > > > news:1150811487.949518.264330@.c74g2000cwc.googlegroups.com...
> >> >> >> > > > I am trying to come up with the best solution for the following
> >> >> >> > > > issue:
> >> >> >> > > >
> >> >> >> > > > I have a specific DB that needs to be backed up in single user mode.
> >> >> >> > > > I
> >> >> >> > > > want to do this with a a Maitenance Plan but I am not sure if that
> >> >> >> > > > is
> >> >> >> > > > possible or makes sense.
> >> >> >> > > >
> >> >> >> > > > What are my options? Do I have to backup with a script? I would
> >> >> >> > > > like
> >> >> >> > > > the Maint. Plan to kick off the ALTER DB script to kick out users.
> >> >> >> > > >
> >> >> >> > > > Thanks for the help.
> >> >> >> > > >
> >> >> >> > > >
> >> >> >
> >> >
> >
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment