Am I wrong if I launch a "sp_attach" sql file (from my local machine) to the remote server?
Do I really have to detach local db before?
Please can someone just tell me how he did?
SaoriHi saori,
1. Did you try to post your question in the DBA section of these forums:
http://www.sqlteam.com/Forums/
http://www.sql-server-performance.com/forum/default.asp
2. Hope some DBA will help you.|||Thanks for the answer.
I'll (quickly) give it a try.
(I posted a msg there 'cause it is above all a question of method about sql server tools)
Saori|||Honestly, I attach remote db's by creating a new registration. Unless you mean something else like fiberchanneling or something, but as for having your SQL Enterprise Manager utilize a Remote SQL Server, just create a new registration.
For T-SQL based methods, you're not necessarily attaching. I'd say it's more Linked Server, or Using Remote Servers. check books online for those two in the Index tab.
SP_LinkedServers and the other method would be actually using the enterprise manager to create the registration.|||Saori,
You didn't clearly define your question about what you want to 'attach', but I think I have understood what you were saying. I would redefine your question with several possible solutions.
Assume, you have a local SQL server in your machine, and there is another SQL server in a remote machine. There is a database called pubs1 existing in the remote SQL server. You want it APPEARS ('attach') in your SQL server.
1. Logical APPEAR:
As KraGiE suggested you could make a new Server Registration in your local SQL Enterprise Manager to LINK to that remote SQL server. In this way, you would bring the whole SQL server including the pubs1 into your local SQL Enterprise Manager.
However, to do so, you need to have the access to the database, say: machine name, login name and password. It is possible that you could only access one database in that server, say, pubs1. But that is enough for you. By the way, if the remote server is not within your LAN, and need to be linked through HTTP, you should have the server IP address and also the access to possible firewall.
I called this kind of 'attach' as logical APPEARS, because the database is not physically in your machine, but still at the remote server. You can only access the database through your Enterprise Manager. The database pubs1 appearing in your Enterprise Manager is just a pointer linked to the remote server.
2. Physical APPEAR:
In short, get a physical copy from the remote server and 'attach' it to your local server.
Step 1:
Copy the database file and the log file into your local machine. In this case, pubs1 files are located by default at:
C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs1.MDF
C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs1_log.LDF
You need shut down the remote database for copy/past, otherwise, it wouldn't work.
Step 2:
from Enterprise Manager, right click the Databases object in your local SQL instance, choose all tasks --> attach database ... . From the Attach Database window, choose your database file, pubs1, and then OK.
3. If you didn't have SQL 2000, instead of SQL 7.0, then the Step 2 would not avaiable in Enterprise Manager. You would have to use the command 'sp_attach_db' to do the job. Something like:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs1.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log1.ldf'
4. Finally, if you even didn't have SQL server 7.0, you couldn't do the trick of 'attach'. But, you could do the job with SQL server 6.5 Enterprise Manager 'Database/Object transfer' tool given you have access to the remote database, where there is equivalent tools in both SQL 7.0/2000, called Import Data/Export Data, but not as easy used as the ATTACH method.
Hope this helps.|||Thanks Phuoc, KraGiE and ghan.
This really helped me understand essential issues.
Great analysis , ghan (copied in my notepad)!
Just one little thing:
When I attach db via Enterprise Manager, do I automatically transfer log file as well?|||saori,
I am really happy to hear that. You know what, when I made some analyses to other people, I just summarize what I have done and clear my understanding about this issue myself.
When I attach db via Enterprise Manager, do I automatically transfer log file as well?
You see the example:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs1.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log1.ldf'
There are two files, one is *.mdf, the data file, another is *.ldf, the log file.
When you use enterprise manager to do the job, you will see, (at STEP 2) you only need browse the *.mdf file in, while the *.ldf file will be brought in automatically, assuming you have copied the two files into the same directory (Step 1).
Good luck.|||I have a project lets say project1. I have a copy of it running on my development server and a copy of it on my production server. I have detached db from development server and attached it to my production server. I can work in db using Enterprise manager but when I try to run my applications it doesnt work. I know why it doesnt work its because its looking for user developmentserver and now its on productionserver. Is there a way to set permissions for user productionserver without having to do every table and stored procedure one at a time? I would like to do it quickly and efficiently and doing all by hand one at a time is neither.
Please help.|||There is a security issue. You must use Windows Authentification, instead of SQL Server Authentification. You might change your connection string to use SQL Server authentification. If you want to keep to use windows authentification, you need to setup your security in your production server to make your application assistent with your SQL server.
swy000 wrote: