Exclusive use during restore - need answer asap

I'm trying to restore a database back to two nights ago. I
choose the backup I want and the force over existing
database option. It comes back with I need exclusive use
of db. How do I find out who else is on the db? I did
the sp_who active proc and the only one that shows up is
the sa account and me.
HELP!Sp_who should of done it.
Another way of finding out is in EM selecting your server,
then Managment Current Activity, Process Info.
One of the oddities of SQL server is if you have two
connections to the db, then it will not allow you restore.
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain
>--Original Message--
>I'm trying to restore a database back to two nights ago.
I
>choose the backup I want and the force over existing
>database option. It comes back with I need exclusive use
>of db. How do I find out who else is on the db? I did
>the sp_who active proc and the only one that shows up is
>the sa account and me.
>HELP!
>.
>|||Hi
If it is sa and you, that makes it 2. sp_who or sp_who2 are the correct SP's
to run. You need to get rid of the sa connection in able to restore the DB.
Cheers
Mike
"Edie Richardson" wrote:
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
> HELP!
>|||"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:48d301c4a17c$928a2480$a501280a@.phx.gbl...
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
>
Don't do sp_who active as even inactive connections can be a problem.
Just use sp_who
(or select * from sysprocesses where dbid= your database id)
> HELP!

Exclusive use during restore - need answer asap

I'm trying to restore a database back to two nights ago. I
choose the backup I want and the force over existing
database option. It comes back with I need exclusive use
of db. How do I find out who else is on the db? I did
the sp_who active proc and the only one that shows up is
the sa account and me.
HELP!
Hi
If it is sa and you, that makes it 2. sp_who or sp_who2 are the correct SP's
to run. You need to get rid of the sa connection in able to restore the DB.
Cheers
Mike
"Edie Richardson" wrote:

> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
> HELP!
>
|||"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:48d301c4a17c$928a2480$a501280a@.phx.gbl...
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
>
Don't do sp_who active as even inactive connections can be a problem.
Just use sp_who
(or select * from sysprocesses where dbid= your database id)

> HELP!

Exclude a Table from SQL 2005 Backup Maintenance Plan

Hi All,

I'm seeking feedback on a backup issue. I have several databases that I run full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.

Each database has 1 or 2 tables that hold non-critical spam hit data. This data accounts for approximately half the database size and grows daily.

My question: Is there a simple way to skip / exclude 1 or 2 tables during a native SQL backup routine either via SQL maintenance plan of TSQL.

All help is greatly appreciated.

Thanks.

Rick B

Have you considered moving this tables to their own file group. You can then skip backing up this file group. Other choice may to move these tables to another database?

Thanks

|||Supporting Sunil's suggestion to move the table to a different FILEGROUP there is a Books Online topic on this:

Partial Backups (link to it).

Regards,
Boris.|||

Thanks Sunil and Boris. I guess I was leaning towards that way. THanks you for taking the time to reply.

Regards,

Rick Broider

Exclude a Table from SQL 2005 Backup Maintenance Plan

Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
Rick B
What about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/
|||But be aware that such a restore is not trivial, and one has to ensure that one has complete
knowledge of the implications such strategy has on the restore process. I'd move those tables into
another database instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegr oups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>

Exclude a Table from SQL 2005 Backup Maintenance Plan

Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
--
Rick BWhat about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/|||But be aware that such a restore is not trivial, and one has to ensure that
one has complete
knowledge of the implications such strategy has on the restore process. I'd
move those tables into
another database instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegroups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>

Exclude a Table from SQL 2005 Backup Maintenance Plan

Hi All,
I'm seeking feedback on a backup issue. I have several databases that I run
full backups on nightly (simple recovery) using a SQL 2005 maintenance plan.
Each database has 1 or 2 tables that hold non-critical spam hit data. This
data accounts for approximately half the database size and grows daily.
My question: Is there a simple way to skip / exclude 1 or 2 tables during a
native SQL backup routine either via SQL maintenance plan of TSQL.
All help is greatly appreciated.
Thanks.
--
Rick BWhat about creating seperate filegroups for those tables and not backup
up those filegroups?
http://sqlservercode.blogspot.com/|||But be aware that such a restore is not trivial, and one has to ensure that one has complete
knowledge of the implications such strategy has on the restore process. I'd move those tables into
another database instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1134133417.029908.208880@.g49g2000cwa.googlegroups.com...
> What about creating seperate filegroups for those tables and not backup
> up those filegroups?
> http://sqlservercode.blogspot.com/
>

Exchange and SQL backup on SBS (WinNT)

Hi.
I'm looking for and information how to backup Exchange (configuration and
data), SQL (configuration and data) and user accounts (password, SID,
configuration,...) with native tools on WinNT 4 (ntbackup, copy,...)?
I don't have tape drive. I would like to use an network server to store data
(so backup to stream which I will later redirect) or to a 'disk media'. Is
it posible to use ntbackup for those purposes? If not ... can ntbackup fit
my needs in combination with tape drives?
What about restore of those data in case of disaster (I guess that I have to
reinstall windows, but do I have to do any configuration or will it be done
by the backup)? What about restore a past state of data on a 'running'
system and/or testing backups (I realy don't want to realize that my backup
are unusable when I will need to restore data after the disaster)?
Is there any other 'free' tool to backup those data or is there a simple
procedure to use copy and/or native MS API to do mentioned tasks?
Does anyone know of a free tool like tar, that know how to handle ACLs and
posibly alternate data streams?
Regards,
DezoDamir... first off you posted to about 7 different newsgroups when only
one was needed. SBS. okay... I'm not certain what version of SBS you
have? If you have SBS 2000 and especially now with SBS 2003, the
NTbackup included is very adequate.
Do you have SBS 4.5? or a later version of SBS.
Scheduling Backups in Windows NT 4.0:
http://www.techtutorials.com/tutorials/nt/ntbackup.shtml
I know that the new ntbackup in SBS2003 can back up to a mapped drive
location and thus you can use an external usb harddrive or other
alternative location... I honestly cannot remember if the NTbackup back
in those NT4 days did this [I don't think it does]
SBS2k3 gives way more options and alternatives including volume shadow
copy and is a more robust system. Windows NT and 2000 both have issues
with Short file name issues whereas SBS2k3 is much better at handling
this issue.
Damir Dezeljin wrote:
> Hi.
> I'm looking for and information how to backup Exchange (configuration and
> data), SQL (configuration and data) and user accounts (password, SID,
> configuration,...) with native tools on WinNT 4 (ntbackup, copy,...)?
> I don't have tape drive. I would like to use an network server to store data
> (so backup to stream which I will later redirect) or to a 'disk media'. Is
> it posible to use ntbackup for those purposes? If not ... can ntbackup fit
> my needs in combination with tape drives?
> What about restore of those data in case of disaster (I guess that I have to
> reinstall windows, but do I have to do any configuration or will it be done
> by the backup)? What about restore a past state of data on a 'running'
> system and/or testing backups (I realy don't want to realize that my backup
> are unusable when I will need to restore data after the disaster)?
>
> Is there any other 'free' tool to backup those data or is there a simple
> procedure to use copy and/or native MS API to do mentioned tasks?
>
> Does anyone know of a free tool like tar, that know how to handle ACLs and
> posibly alternate data streams?
>
> Regards,
> Dezo
>
>
--
"Don't lose sight of security. Security is a state of being,
not a state of budget. He with the most firewalls still does
not win. Put down that honeypot and keep up to date on your patches.
Demand better security from vendors and hold them responsible.
Use what you have, and make sure you know how to use it properly
and effectively."
~Rain Forest Puppy
http://www.wiretrip.net/rfp/txt/evolution.txt|||> Damir... first off you posted to about 7 different newsgroups when only
> one was needed. SBS.
Sorry for this. I guess that all the posted groups were somehow colegated
with the question, because I'm not looking for all_in_one tool (so not
necesary ntbackup). I'm realy sorry to spaming you ... I will try to avoid
posting to so many groups in the future.
> Do you have SBS 4.5? or a later version of SBS.
Yes ... I'm using SBS 4.5 running on WinNT 4.0
> SBS2k3 and VSS
I also know that SBS 2003 can do snapshots. This solves a problem with e.g.
open files, but doesn't completly resolve the problem of backups
(application should suport VSS and flush all data to HDD so that the backup
is consistent). For older version of windows there are some other snapshot
tools available (e.g. a good and chip solution is VolumeSnapshot from
StorageCraft).
I'm trying to implement the backup in a company with limited budged so there
is no way to upgrade to SBS 2003. I think that there is no problem for
doing SQL backup (just do some sort of databases lock and than dump all
databases to file(s) ). The major problem is backing up user accounts and
Exchange data. As I know user acounts are stored in the registry and
exchange data are stored in more files. So I don't know if is it posible to
make an 'cold' backup (reboot to an completly diferent OS and only copy some
files to another location - how about restore)?
Regards,
Dezo|||Back in the NT4 and even still the 2k days ... we have a huge issue with
SFN [short file name] issues so that if you do a "bare metal restore"
you will have issues with the OS and will need to stick the SBS cdrom
back in and "refresh" the OS.
There are some backup scripts in these links..
Google Search: ntbackup group:*smallbiz:
http://groups.google.com/groups?q=ntbackup+group:*smallbiz&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=E413AF64BB5ED211A62100A02498E40E08D47E%40pdc-know.knoworries.nl&rnum=2
Google Search: ntbackup group:*smallbiz:
http://groups.google.com/groups?q=ntbackup+group:*smallbiz&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=3bbeb201%240%2422652%24afc38c87%40news.optusnet.com.au&rnum=6
And how to dump out sQL so it can be backed up.
Google Search: ntbackup sql group:*smallbiz:
http://groups.google.com/groups?q=ntbackup+sql+group:*smallbiz&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=Ov4PaWec9GA.215%40uppssnewspub04.moswest.msn.net&rnum=1
You know what I would do? Get a used tape backup from e-b-a-y I don't
think you will be able to back up to non tape locations...
Damir Dezeljin wrote:
>>Damir... first off you posted to about 7 different newsgroups when only
>>one was needed. SBS.
> Sorry for this. I guess that all the posted groups were somehow colegated
> with the question, because I'm not looking for all_in_one tool (so not
> necesary ntbackup). I'm realy sorry to spaming you ... I will try to avoid
> posting to so many groups in the future.
>
>>Do you have SBS 4.5? or a later version of SBS.
> Yes ... I'm using SBS 4.5 running on WinNT 4.0
>
>>SBS2k3 and VSS
> I also know that SBS 2003 can do snapshots. This solves a problem with e.g.
> open files, but doesn't completly resolve the problem of backups
> (application should suport VSS and flush all data to HDD so that the backup
> is consistent). For older version of windows there are some other snapshot
> tools available (e.g. a good and chip solution is VolumeSnapshot from
> StorageCraft).
>
> I'm trying to implement the backup in a company with limited budged so there
> is no way to upgrade to SBS 2003. I think that there is no problem for
> doing SQL backup (just do some sort of databases lock and than dump all
> databases to file(s) ). The major problem is backing up user accounts and
> Exchange data. As I know user acounts are stored in the registry and
> exchange data are stored in more files. So I don't know if is it posible to
> make an 'cold' backup (reboot to an completly diferent OS and only copy some
> files to another location - how about restore)?
>
> Regards,
> Dezo
>
--
"Don't lose sight of security. Security is a state of being,
not a state of budget. He with the most firewalls still does
not win. Put down that honeypot and keep up to date on your patches.
Demand better security from vendors and hold them responsible.
Use what you have, and make sure you know how to use it properly
and effectively."
~Rain Forest Puppy
http://www.wiretrip.net/rfp/txt/evolution.txt|||In news:eNx$HjJkDHA.2140@.TK2MSFTNGP09.phx.gbl,
Susan Bradley, CPA aka Ebitz - SBS Rocks [MVP] <sbradcpa@.pacbell.net> wrote:
> Back in the NT4 and even still the 2k days ... we have a huge issue
> with SFN [short file name] issues so that if you do a "bare metal
> restore" you will have issues with the OS and will need to stick the
> SBS cdrom back in and "refresh" the OS.
I did a restore of my server (SBS 2003) as a test. The server has several
non MS tools installed including ISA server. I deleted my Raid 5 array,
created a new one, booted from the first CD, did a full directory restore
including all files, folders and drives. The server was up and running in
about 1 hour.
No issues with SFN at all.
--
Mariette Knap
http://wwww.smallbizserver.net|||When I said the 2k days ...that's SBS 2000. As Jeff has posted... [and
I'm forgetting his write up as it's now off this newsserver] but the
issues are resolved in 2k3
Mariette Knap [SBS MVP] wrote:
> In news:eNx$HjJkDHA.2140@.TK2MSFTNGP09.phx.gbl,
> Susan Bradley, CPA aka Ebitz - SBS Rocks [MVP] <sbradcpa@.pacbell.net> wrote:
>
>>Back in the NT4 and even still the 2k days ... we have a huge issue
>>with SFN [short file name] issues so that if you do a "bare metal
>>restore" you will have issues with the OS and will need to stick the
>>SBS cdrom back in and "refresh" the OS.
>
> I did a restore of my server (SBS 2003) as a test. The server has several
> non MS tools installed including ISA server. I deleted my Raid 5 array,
> created a new one, booted from the first CD, did a full directory restore
> including all files, folders and drives. The server was up and running in
> about 1 hour.
> No issues with SFN at all.
>
--
"Don't lose sight of security. Security is a state of being,
not a state of budget. He with the most firewalls still does
not win. Put down that honeypot and keep up to date on your patches.
Demand better security from vendors and hold them responsible.
Use what you have, and make sure you know how to use it properly
and effectively."
~Rain Forest Puppy
http://www.wiretrip.net/rfp/txt/evolution.txt|||You can backup Exchange from any W2K machine on your network.
XADM: Files Needed to Perform Exchange NT Backup on Workstation
PSS ID Number: 181445
Damir Dezeljin wrote:
> > Damir... first off you posted to about 7 different newsgroups when only
> > one was needed. SBS.
> Sorry for this. I guess that all the posted groups were somehow colegated
> with the question, because I'm not looking for all_in_one tool (so not
> necesary ntbackup). I'm realy sorry to spaming you ... I will try to avoid
> posting to so many groups in the future.
> > Do you have SBS 4.5? or a later version of SBS.
> Yes ... I'm using SBS 4.5 running on WinNT 4.0
> > SBS2k3 and VSS
> I also know that SBS 2003 can do snapshots. This solves a problem with e.g.
> open files, but doesn't completly resolve the problem of backups
> (application should suport VSS and flush all data to HDD so that the backup
> is consistent). For older version of windows there are some other snapshot
> tools available (e.g. a good and chip solution is VolumeSnapshot from
> StorageCraft).
> I'm trying to implement the backup in a company with limited budged so there
> is no way to upgrade to SBS 2003. I think that there is no problem for
> doing SQL backup (just do some sort of databases lock and than dump all
> databases to file(s) ). The major problem is backing up user accounts and
> Exchange data. As I know user acounts are stored in the registry and
> exchange data are stored in more files. So I don't know if is it posible to
> make an 'cold' backup (reboot to an completly diferent OS and only copy some
> files to another location - how about restore)?
> Regards,
> Dezo
--
Corrections are welcome.
Please keep all discussions in NG, so that everybody can participate.
Kirill

Excesive Space Allocated

Hello,
I have a fairly large database that at one time I used to do a lot of processing on. Now it's nothing but backup for some old data. I've truncated my transaction log and it's down to about 25 megs. The problem is the transaction log is allocated for ar
ound 2.5 gigs and I can't get it to allocated any less so I'm waiting a ton of space on my server. Is there anyway to shrink how much size your allocated for your log file?
Thanks
Check out DBCC SHRINKFILE in BOL.
Andrew J. Kelly SQL MVP
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:9DF0AAF1-1776-4C66-BE74-EE649A3F7CFE@.microsoft.com...
> Hello,
> I have a fairly large database that at one time I used to do a lot of
processing on. Now it's nothing but backup for some old data. I've
truncated my transaction log and it's down to about 25 megs. The problem is
the transaction log is allocated for around 2.5 gigs and I can't get it to
allocated any less so I'm waiting a ton of space on my server. Is there
anyway to shrink how much size your allocated for your log file?
> Thanks

Excesive Space Allocated

Hello,
I have a fairly large database that at one time I used to do a lot of proces
sing on. Now it's nothing but backup for some old data. I've truncated my
transaction log and it's down to about 25 megs. The problem is the transact
ion log is allocated for ar
ound 2.5 gigs and I can't get it to allocated any less so I'm waiting a ton
of space on my server. Is there anyway to shrink how much size your allocat
ed for your log file?
ThanksCheck out DBCC SHRINKFILE in BOL.
Andrew J. Kelly SQL MVP
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:9DF0AAF1-1776-4C66-BE74-EE649A3F7CFE@.microsoft.com...
> Hello,
> I have a fairly large database that at one time I used to do a lot of
processing on. Now it's nothing but backup for some old data. I've
truncated my transaction log and it's down to about 25 megs. The problem is
the transaction log is allocated for around 2.5 gigs and I can't get it to
allocated any less so I'm waiting a ton of space on my server. Is there
anyway to shrink how much size your allocated for your log file?
> Thanks