November 6, 2001 at 3:21 am
How can I move a database from one server to another accessing the source sql server (7.0) from a remote place? (The destination SQL Server can etihter be 7 or 2000.) I have tried different options:
1. Using sp_detach_db and sp_attach_db. But I only gets an error message saying thsat the database is in use. It is not in use, except maybe for the SQL Server Agent. But it seems that I can't stop this service from a remote place (even if I log in as sa). I just gets error messages talking about either denied access or that an RPC server is missing (or something). What is the problem here?
2. Using Copy Database Wizard. But also generates errors, probably because this guide also uses sp_detach_db and sp_attach_db. Or?
2. Using Backup and Restore. But I can't figure out how I should restore the database (using the backup file) on the destination SQL Server. I think I can't use EM, but can I use T-SQL? I have tried this example (on 2000):
RESTORE FILELISTONLY
FROM DISK = 'c:\data\webbplatser\lmab2000\_db_detached\lmab'
RESTORE DATABASE lmab
FROM DISK = 'c:\data\webbplatser\lmab2000\_db_detached\lmab'
WITH MOVE 'libris_shop_Data' TO 'c:\data\webbplatser\lmab2000\_db\lmab.mdf',
MOVE 'libris_shop_Log' TO 'c:\data\webbplatser\lmab2000\_db\lmab.ldf'
GO
"lmab" is the backup file. But I only gets the an error message from Query Analyser saying:
Server: Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
What does that mean? For the moment the source database is just an empty database which is not in use as far as I know. SQL Server Agent is stopped. Any clues?
What is my best option? How should I solve the problems mentioned above?
November 6, 2001 at 1:45 pm
Everything that you are stating here is a valid method to move a database however, they all point to the same issue, that there is still a connection to that database while you are trying to do functions which require no other connections. First thing I would do would be to set "Single User" access through Enterprise Manager or with
use master
exec sp_dboption 'yourdb', 'single user', 'true'
Books On Line has some good information about Single User mode and hopefully that will help.
I would also be interested what connections you are seeing using an sp_who2. Could there possibly be a backup software agent connection out there as well. That should show up so, I am grasping at ideas.
Hope this helps and post if you still can't get it to work.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 6, 2001 at 3:04 pm
David is right. You have some type of access. Every user who is connected to a database obtains a shared database lock to prevent detaches, restores,e tc.
Steve Jones
November 7, 2001 at 12:47 am
I tried setting single user mode through EM. But it doesn't allow me to do that. I get an error message saying that other users are connected to the database. But when I closed SQL Query Analyzer I could set the database to single user mode. But then I can't detach using SQL Query Analyzer since I can't open an connection to the database with it. Without SQL Query Analyzer, how can I detach? I can't use EM since the remote SQL Server is version 7.
When the database is not in single user mode I get the following results using sp_who2 ("lmab" is the name of the database, "WIN2000SERVER" is my local SQL Server and "SQL1" is the remote SQL Server where "lmab" is to be found; I login as sa):
USE master
EXEC sp_who2
1 sleeping sa . . masterSIGNAL HANDLER 0007/24 13:21:47 1
2 BACKGROUND sa . . masterLOCK MONITOR 0007/24 13:21:47 2
3 sleeping sa . . masterLOG WRITER 0007/24 13:21:47 3
4 BACKGROUND sa . . masterLAZY WRITER 0007/24 13:21:47 4
5 sleeping sa . . masterCHECKPOINT SLEEP0899107/24 13:21:47 5
6 RUNNABLE saWIN2000SERVER . masterSELECT INTO01811/07 06:56:48SQL Query Analyzer 6
7 BACKGROUND sa . . masterAWAITING COMMAND033707/24 13:21:47 7
8 sleeping saWIN2000SERVER . lmabAWAITING COMMAND016811/07 07:08:47MS SQLEM 8
9 sleeping saWIN2000SERVER . masterAWAITING COMMAND0111/07 06:56:35SQL Query Analyzer - Object Br9
10 sleeping johanWEB1 . DagenAWAITING COMMAND02411/07 07:08:40Internet Information Server 10
11 sleeping DMZ\PoloSQL1 . msdbAWAITING COMMAND0111/05 15:50:13SQLAgent - Generic Refresher 11
12 sleeping johanWEB1 . DagenAWAITING COMMAND014111/07 07:08:37Internet Information Server 12
14 sleeping johanWEB1 . DagenAWAITING COMMAND0111/07 07:08:20Internet Information Server 14
16 sleeping DMZ\PoloSQL1 . DagenAWAITING COMMAND017211/06 07:31:04MS SQLEM 16
21 sleeping DMZ\PoloSQL1 . msdbAWAITING COMMAND070244111/07 07:09:14SQLAgent - Alert Engine 21
USE lmab
EXEC sp_who2
1 sleeping sa . . masterSIGNAL HANDLER 0007/24 13:21:47 1
2 BACKGROUND sa . . lmabLOCK MONITOR 0007/24 13:21:47 2
3 sleeping sa . . lmabLOG WRITER 0007/24 13:21:47 3
4 BACKGROUND sa . . lmabLAZY WRITER 0007/24 13:21:47 4
5 sleeping sa . . lmabCHECKPOINT SLEEP0899107/24 13:21:47 5
6 RUNNABLE saWIN2000SERVER . lmabSELECT INTO0411/07 07:12:51SQL Query Analyzer 6
7 BACKGROUND sa . . lmabAWAITING COMMAND033707/24 13:21:47 7
8 sleeping saWIN2000SERVER . lmabAWAITING COMMAND016811/07 07:08:47MS SQLEM 8
9 sleeping saWIN2000SERVER . masterAWAITING COMMAND0011/07 07:12:51SQL Query Analyzer - Object Br9
11 sleeping DMZ\PoloSQL1 . msdbAWAITING COMMAND0111/05 15:50:13SQLAgent - Generic Refresher 11
12 sleeping johanWEB1 . DagenAWAITING COMMAND02511/07 07:12:46Internet Information Server 12
14 sleeping johanWEB1 . DagenAWAITING COMMAND015011/07 07:12:45Internet Information Server 14
16 sleeping DMZ\PoloSQL1 . DagenAWAITING COMMAND017211/06 07:31:04MS SQLEM 16
18 sleeping johanWEB1 . DagenAWAITING COMMAND0011/07 07:12:45Internet Information Server 18
21 sleeping DMZ\PoloSQL1 . msdbAWAITING COMMAND070363711/07 07:13:15SQLAgent - Alert Engine 21
Does this say anything? As you can see SQLAgent is running on msdb and I think SQLAgent primarily handles automated backups. Is SQLAgent "another user" for lmab?As said before, it doesn't seem as if I can stop this service from a remote place (even if I log in as sa). Why? And actually, I just asked a person at the remote place to stop the service, but that didn't help. My database is still in use, by what??
Concerning the Backup and Restore method (remember that is concerns my *local* server): Now I doesn't get the error message "Exclusive access could not be obtained because the database is in use", but instead "The backup set holds a backup of a database other than the existing 'lmab' database." If this error message means what I think, how on earth can I use Backup and Restore as a method of copying a database from one server to another?
So far, the only successful metnod for transporting the remote database to my local server was by using DTS. But is that really a good method? Advantages? Disadvantages?
All for now.
November 7, 2001 at 12:51 am
Just one thing more: could it be that my *remote connection* is the explanation for the "already in use" error message? Is it maybe not possible to do a detach/attach from a remote place at all?
November 7, 2001 at 5:17 am
If the remote connection is Enterprise Manager or Query Analyzer, it should be fine. You just cant have connections to the database you want to detach.
Andy
Andy
November 7, 2001 at 11:18 am
switch to master in Query Analyzer. Your QA connection is a connection (and lock) in the db.
Steve Jones
November 7, 2001 at 11:22 am
Easiest to just use backup and restore.
Backup the database
copy the backup file to the destination server
restore from the local backup file.
You can do all this using tsql if you wish - xp_cmdshell for the copy.
It is better to run the backup from the source server but if you wish you can use osql, sp_executesql or sp_start_job to control it from the destination.
As for the detach problem.
You should connect to master (or something else) to perform the detach).
You can find if anyone is using the database by
select * from master..sysprocesses where dbid = db_id('mydbname')
You can then kill all those connections or abort.
The restore problem is because you are trying to restore over a database which is in use - the restore just uses the backup file and the local server so can't have anything to do with the source. Probably better to drop the database first.
Cursors never.
DTS - only when needed and never to control.
November 7, 2001 at 4:32 pm
As for Steve Jones' comment:
Even if I switch to master in Query Analyzer I get the error message "Cannot drop the database 'lmab' because it is currently in use." Hmmm...
As for nigelrivett's comments:
When I (from Query Analyser) run select * from master..sysprocesses where dbid = db_id('lmab') I get the result:
1640500x00000WRITELOG 14101722242001-11-05 15:46:43.7932001-11-07 08:23:12.11000sleeping [a looong number...]SQL1 MS SQLEM 276 AWAITING COMMANDDMZ Polo 00D0B7A7CE66SSMSSO70.DLLDMZ\Polo 6
Does this say that anyone is using the database lmab? For what?
After deleting the local database lmab, I could successfully restore the database from the backup file made on the remote server. Is the script I use (see below) in Query Analyser the best way to do it or should it be formulated in another way? I presume I can't use EM, since it doesn't "know" about any backup for lmab!?
RESTORE FILELISTONLY
FROM DISK = 'c:\data\webbplatser\lmab2000\_db_detached\lmab'
RESTORE DATABASE lmab
FROM DISK = 'c:\data\webbplatser\lmab2000\_db_detached\lmab'
WITH MOVE 'libris_shop_Data' TO 'c:\data\webbplatser\lmab2000\_db\lmab.mdf',
MOVE 'libris_shop_Log' TO 'c:\data\webbplatser\lmab2000\_db\lmab.ldf'
GO
Bengt
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply