I am trying to restore a .bak to a test server in order to test the integrity
of my production DB backups but the test server rejects the .bak file because
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.
What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>
|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
.....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>
|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||It is a totally different server. Maybe I am doing something wrong. How would
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>
|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
>
>
|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the test
> server and execute the backup statement. By log.bak did you mean .trn files?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label order. Show all posts
Showing posts with label order. Show all posts
Friday, March 30, 2012
Restore?
I am trying to restore a .bak to a test server in order to test the integrit
y
of my production DB backups but the test server rejects the .bak file becaus
e
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It is a totally different server. Maybe I am doing something wrong. How woul
d
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
>|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
>
>|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
>|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the tes
t
> server and execute the backup statement. By log.bak did you mean .trn file
s?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
y
of my production DB backups but the test server rejects the .bak file becaus
e
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It is a totally different server. Maybe I am doing something wrong. How woul
d
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:
> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
>|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:
> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
>
>|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
>|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the tes
t
> server and execute the backup statement. By log.bak did you mean .trn file
s?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Monday, March 12, 2012
restore problem - help
Hello,
I have a problem with restoring, so I am doing several test in order to
properly to backup my database. So I have first of all, restore all
data(from database 1) into a new database (database 2) and and it is
works fine (no problem and no error). Now I would like to restore my
really database but I have an error...
Error database in use. Exclusive access could not be obtained because
the database is in use. Restore database is termanatting abnornally
The others issue is that someone is connecto tosqlserver using but to
another database. (this person is connecting since this morning; and
during the first restore I had no problem database 1 --> database 2)
I do not understand please could someone help me on that?
InaHi
You can not restore when someone is accessing the database, check on the
activity screens in Enterprise Manager (Management/Current Activity/Process
Info) for people connected to the database or you can use sp_who2 to see who
is using the database
e.g
USE MASTER
EXEC sp_who2
To kill a process connected to the database in Enterprise Manager right
click on the process and choose the Kill Process option or use the KILL
command in T-SQL specifying the SPID to kill e.g. KILL 53
HTH
John
"ina" wrote:
> Hello,
> I have a problem with restoring, so I am doing several test in order to
> properly to backup my database. So I have first of all, restore all
> data(from database 1) into a new database (database 2) and and it is
> works fine (no problem and no error). Now I would like to restore my
> really database but I have an error...
>
> Error database in use. Exclusive access could not be obtained because
> the database is in use. Restore database is termanatting abnornally
> The others issue is that someone is connecto tosqlserver using but to
> another database. (this person is connecting since this morning; and
> during the first restore I had no problem database 1 --> database 2)
> I do not understand please could someone help me on that?
> Ina
>|||thanks john but access the database means connect to the software that
use this database or sql server , I am newbie on that. because nobody
is connect to the software
ina|||I have this process
I have a program that are in two version, demo version and true version
the TEST_PROD is the database of the DEMO version
TEST the database of the TRUE version
This is my current activies ( few lines); tester is the user for both
database
54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
00:0054 ?
55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
55 people working in test database
56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
57 ?
58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
00:00 58
59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
Can I kill process or do I need to do something else?
Ina|||Hi Ina
It is better to get you users to log off the application rather than kill
the processes. Books online states
Use KILL very carefully, especially when critical processes are running. You
cannot kill your own process. Other processes not to kill are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
It seems several processes are connected to both the TEST_PROD and TEST
databases, Processes 54, 57 and 58 on TEST_PROD. You could use DBCC
INPUTBUFFER to see information about the last command executed for each of
these SPIDs.
Another way to KILL off connections would be to set the database into single
user mode after setting it to be the current database.
John
"ina" wrote:
> I have this process
> I have a program that are in two version, demo version and true version
> the TEST_PROD is the database of the DEMO version
> TEST the database of the TRUE version
> This is my current activies ( few lines); tester is the user for both
> database
>
> 54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
> 00:0054 ?
> 55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
> 55 people working in test database
> 56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
> 57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
> 57 ?
> 58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
> 00:00 58
> 59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
> Can I kill process or do I need to do something else?
> Ina
>|||Thanks John, the trick is that nobody is using the softwares. So I do
not know why it is like this?
Ina|||Hi Ina
Use DBCC INPUTBUFFER with the SPID from sp_who2 to see what commands last
command was executed on that connection. This may help you find out what has
caused this.
John
"ina" wrote:
> Thanks John, the trick is that nobody is using the softwares. So I do
> not know why it is like this?
> Ina
>|||Thanks John
I have a problem with restoring, so I am doing several test in order to
properly to backup my database. So I have first of all, restore all
data(from database 1) into a new database (database 2) and and it is
works fine (no problem and no error). Now I would like to restore my
really database but I have an error...
Error database in use. Exclusive access could not be obtained because
the database is in use. Restore database is termanatting abnornally
The others issue is that someone is connecto tosqlserver using but to
another database. (this person is connecting since this morning; and
during the first restore I had no problem database 1 --> database 2)
I do not understand please could someone help me on that?
InaHi
You can not restore when someone is accessing the database, check on the
activity screens in Enterprise Manager (Management/Current Activity/Process
Info) for people connected to the database or you can use sp_who2 to see who
is using the database
e.g
USE MASTER
EXEC sp_who2
To kill a process connected to the database in Enterprise Manager right
click on the process and choose the Kill Process option or use the KILL
command in T-SQL specifying the SPID to kill e.g. KILL 53
HTH
John
"ina" wrote:
> Hello,
> I have a problem with restoring, so I am doing several test in order to
> properly to backup my database. So I have first of all, restore all
> data(from database 1) into a new database (database 2) and and it is
> works fine (no problem and no error). Now I would like to restore my
> really database but I have an error...
>
> Error database in use. Exclusive access could not be obtained because
> the database is in use. Restore database is termanatting abnornally
> The others issue is that someone is connecto tosqlserver using but to
> another database. (this person is connecting since this morning; and
> during the first restore I had no problem database 1 --> database 2)
> I do not understand please could someone help me on that?
> Ina
>|||thanks john but access the database means connect to the software that
use this database or sql server , I am newbie on that. because nobody
is connect to the software
ina|||I have this processI have a program that are in two version, demo version and true version
the TEST_PROD is the database of the DEMO version
TEST the database of the TRUE version
This is my current activies ( few lines); tester is the user for both
database
54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
00:0054 ?
55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
55 people working in test database
56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
57 ?
58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
00:00 58
59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
Can I kill process or do I need to do something else?
Ina|||Hi Ina
It is better to get you users to log off the application rather than kill
the processes. Books online states
Use KILL very carefully, especially when critical processes are running. You
cannot kill your own process. Other processes not to kill are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
It seems several processes are connected to both the TEST_PROD and TEST
databases, Processes 54, 57 and 58 on TEST_PROD. You could use DBCC
INPUTBUFFER to see information about the last command executed for each of
these SPIDs.
Another way to KILL off connections would be to set the database into single
user mode after setting it to be the current database.
John
"ina" wrote:
> I have this process
> I have a program that are in two version, demo version and true version
> the TEST_PROD is the database of the DEMO version
> TEST the database of the TRUE version
> This is my current activies ( few lines); tester is the user for both
> database
>
> 54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
> 00:0054 ?
> 55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
> 55 people working in test database
> 56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
> 57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
> 57 ?
> 58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
> 00:00 58
> 59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
> Can I kill process or do I need to do something else?
> Ina
>|||Thanks John, the trick is that nobody is using the softwares. So I do
not know why it is like this?
Ina|||Hi Ina
Use DBCC INPUTBUFFER with the SPID from sp_who2 to see what commands last
command was executed on that connection. This may help you find out what has
caused this.
John
"ina" wrote:
> Thanks John, the trick is that nobody is using the softwares. So I do
> not know why it is like this?
> Ina
>|||Thanks John
restore problem - help
Hello,
I have a problem with restoring, so I am doing several test in order to
properly to backup my database. So I have first of all, restore all
data(from database 1) into a new database (database 2) and and it is
works fine (no problem and no error). Now I would like to restore my
really database but I have an error...
Error database in use. Exclusive access could not be obtained because
the database is in use. Restore database is termanatting abnornally
The others issue is that someone is connecto tosqlserver using but to
another database. (this person is connecting since this morning; and
during the first restore I had no problem database 1 --> database 2)
I do not understand please could someone help me on that?
InaHi
You can not restore when someone is accessing the database, check on the
activity screens in Enterprise Manager (Management/Current Activity/Process
Info) for people connected to the database or you can use sp_who2 to see who
is using the database
e.g
USE MASTER
EXEC sp_who2
To kill a process connected to the database in Enterprise Manager right
click on the process and choose the Kill Process option or use the KILL
command in T-SQL specifying the SPID to kill e.g. KILL 53
HTH
John
"ina" wrote:
> Hello,
> I have a problem with restoring, so I am doing several test in order to
> properly to backup my database. So I have first of all, restore all
> data(from database 1) into a new database (database 2) and and it is
> works fine (no problem and no error). Now I would like to restore my
> really database but I have an error...
>
> Error database in use. Exclusive access could not be obtained because
> the database is in use. Restore database is termanatting abnornally
> The others issue is that someone is connecto tosqlserver using but to
> another database. (this person is connecting since this morning; and
> during the first restore I had no problem database 1 --> database 2)
> I do not understand please could someone help me on that?
> Ina
>|||thanks john but access the database means connect to the software that
use this database or sql server , I am newbie on that. because nobody
is connect to the software
:) ina|||I have this process
I have a program that are in two version, demo version and true version
the TEST_PROD is the database of the DEMO version
TEST the database of the TRUE version
This is my current activies ( few lines); tester is the user for both
database
54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
00:0054 ?
55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
55 people working in test database
56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
57 ?
58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
00:00 58
59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
Can I kill process or do I need to do something else?
Ina|||Hi Ina
It is better to get you users to log off the application rather than kill
the processes. Books online states
Use KILL very carefully, especially when critical processes are running. You
cannot kill your own process. Other processes not to kill are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
It seems several processes are connected to both the TEST_PROD and TEST
databases, Processes 54, 57 and 58 on TEST_PROD. You could use DBCC
INPUTBUFFER to see information about the last command executed for each of
these SPIDs.
Another way to KILL off connections would be to set the database into single
user mode after setting it to be the current database.
John
"ina" wrote:
> I have this process
> I have a program that are in two version, demo version and true version
> the TEST_PROD is the database of the DEMO version
> TEST the database of the TRUE version
> This is my current activies ( few lines); tester is the user for both
> database
>
> 54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
> 00:0054 ?
> 55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
> 55 people working in test database
> 56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
> 57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
> 57 ?
> 58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
> 00:00 58
> 59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
> Can I kill process or do I need to do something else?
> Ina
>|||Thanks John, the trick is that nobody is using the softwares. So I do
not know why it is like this?
Ina|||Hi Ina
Use DBCC INPUTBUFFER with the SPID from sp_who2 to see what commands last
command was executed on that connection. This may help you find out what has
caused this.
John
"ina" wrote:
> Thanks John, the trick is that nobody is using the softwares. So I do
> not know why it is like this?
> Ina
>|||Thanks John :):)
I have a problem with restoring, so I am doing several test in order to
properly to backup my database. So I have first of all, restore all
data(from database 1) into a new database (database 2) and and it is
works fine (no problem and no error). Now I would like to restore my
really database but I have an error...
Error database in use. Exclusive access could not be obtained because
the database is in use. Restore database is termanatting abnornally
The others issue is that someone is connecto tosqlserver using but to
another database. (this person is connecting since this morning; and
during the first restore I had no problem database 1 --> database 2)
I do not understand please could someone help me on that?
InaHi
You can not restore when someone is accessing the database, check on the
activity screens in Enterprise Manager (Management/Current Activity/Process
Info) for people connected to the database or you can use sp_who2 to see who
is using the database
e.g
USE MASTER
EXEC sp_who2
To kill a process connected to the database in Enterprise Manager right
click on the process and choose the Kill Process option or use the KILL
command in T-SQL specifying the SPID to kill e.g. KILL 53
HTH
John
"ina" wrote:
> Hello,
> I have a problem with restoring, so I am doing several test in order to
> properly to backup my database. So I have first of all, restore all
> data(from database 1) into a new database (database 2) and and it is
> works fine (no problem and no error). Now I would like to restore my
> really database but I have an error...
>
> Error database in use. Exclusive access could not be obtained because
> the database is in use. Restore database is termanatting abnornally
> The others issue is that someone is connecto tosqlserver using but to
> another database. (this person is connecting since this morning; and
> during the first restore I had no problem database 1 --> database 2)
> I do not understand please could someone help me on that?
> Ina
>|||thanks john but access the database means connect to the software that
use this database or sql server , I am newbie on that. because nobody
is connect to the software
:) ina|||I have this process
I have a program that are in two version, demo version and true version
the TEST_PROD is the database of the DEMO version
TEST the database of the TRUE version
This is my current activies ( few lines); tester is the user for both
database
54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
00:0054 ?
55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
55 people working in test database
56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
57 ?
58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
00:00 58
59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
Can I kill process or do I need to do something else?
Ina|||Hi Ina
It is better to get you users to log off the application rather than kill
the processes. Books online states
Use KILL very carefully, especially when critical processes are running. You
cannot kill your own process. Other processes not to kill are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
It seems several processes are connected to both the TEST_PROD and TEST
databases, Processes 54, 57 and 58 on TEST_PROD. You could use DBCC
INPUTBUFFER to see information about the last command executed for each of
these SPIDs.
Another way to KILL off connections would be to set the database into single
user mode after setting it to be the current database.
John
"ina" wrote:
> I have this process
> I have a program that are in two version, demo version and true version
> the TEST_PROD is the database of the DEMO version
> TEST the database of the TRUE version
> This is my current activies ( few lines); tester is the user for both
> database
>
> 54 sleeping tester server TEST_PROD AWAITING COMMAND 0 0 01/01/2006
> 00:0054 ?
> 55 sleeping tester server TEST AWAITING COMMAND 01 0 01/05/2019 15:38
> 55 people working in test database
> 56 sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 56
> 57sleeping tester serverTEST_PRODAWAITING COMMAND 0 0 01/01/2006 00:00
> 57 ?
> 58 sleeping tester server TEST_PRODAWAITING COMMAND 0 001/01/2006
> 00:00 58
> 59sleeping tester server TEST AWAITING COMMAND 0 001/01/2006 00:00 59
> Can I kill process or do I need to do something else?
> Ina
>|||Thanks John, the trick is that nobody is using the softwares. So I do
not know why it is like this?
Ina|||Hi Ina
Use DBCC INPUTBUFFER with the SPID from sp_who2 to see what commands last
command was executed on that connection. This may help you find out what has
caused this.
John
"ina" wrote:
> Thanks John, the trick is that nobody is using the softwares. So I do
> not know why it is like this?
> Ina
>|||Thanks John :):)
Friday, March 9, 2012
Restore Order
Hi NG,
I have a question regarding the restore order of databases in case of
total desaster on the same server. I use KEEP_REPLICATION.
1. master
2. msdb
--> Stop all jobs in msdb
3. distribution
4. replication database
Is these sequence OK?
thanks in advance
Thomas
Is my question incorrectly?
On Tue, 08 Mar 2005 16:48:12 GMT, tohas@.freenet.de (Thomas Hase)
wrote:
>Hi NG,
>I have a question regarding the restore order of databases in case of
>total desaster on the same server. I use KEEP_REPLICATION.
>1. master
>2. msdb
>--> Stop all jobs in msdb
>3. distribution
>4. replication database
>Is these sequence OK?
>thanks in advance
>Thomas
|||I would tend to restore MSDB last, in case any jobs start
before everything's ready. Also if you are talking about
transactional publications, the 'sync with backup' option
is important. Finally, the servername must be identical
on the newly rebuilt server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have a question regarding the restore order of databases in case of
total desaster on the same server. I use KEEP_REPLICATION.
1. master
2. msdb
--> Stop all jobs in msdb
3. distribution
4. replication database
Is these sequence OK?
thanks in advance
Thomas
Is my question incorrectly?
On Tue, 08 Mar 2005 16:48:12 GMT, tohas@.freenet.de (Thomas Hase)
wrote:
>Hi NG,
>I have a question regarding the restore order of databases in case of
>total desaster on the same server. I use KEEP_REPLICATION.
>1. master
>2. msdb
>--> Stop all jobs in msdb
>3. distribution
>4. replication database
>Is these sequence OK?
>thanks in advance
>Thomas
|||I would tend to restore MSDB last, in case any jobs start
before everything's ready. Also if you are talking about
transactional publications, the 'sync with backup' option
is important. Finally, the servername must be identical
on the newly rebuilt server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 7, 2012
restore mssql 7.0. database
When I try to restore mssql 7.0 database,I got strange error :
The database you are attempting to restore was backed up under a different sort order ID (52) than the one currently runing on the server (106), and at least one of them is a non-binary sort order.
Backup or restore operation terminating abnormally.
Is there a way how i can fix it ?
Thanks a lot !You need to change sort order by using REBUILDM utility to rebuild the master database, refer to books online for more information.
Or rebuild the target server with source server's collation settings.
The database you are attempting to restore was backed up under a different sort order ID (52) than the one currently runing on the server (106), and at least one of them is a non-binary sort order.
Backup or restore operation terminating abnormally.
Is there a way how i can fix it ?
Thanks a lot !You need to change sort order by using REBUILDM utility to rebuild the master database, refer to books online for more information.
Or rebuild the target server with source server's collation settings.
Subscribe to:
Posts (Atom)