Is Restore Or Detach/Attach Better?

Nov 5, 2007

Hello, everyone:

I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?

Thanks

ZYT

View 5 Replies


ADVERTISEMENT

SQL 2012 :: Attach / Detach Versus Backup And Restore

Mar 20, 2014

We have 5 databases which will sum up to 8+TB, housed in our EDW server. We are planning to migrate to another storage system for better performance.

I am planning Full backup and Differential backup and detach the DB and restore the DB in the new storage system.

Or should I detach,copy the data and log file and attach the files, will this approach have any advantages ?

View 5 Replies View Related

Backup/Restore Vs Attach/Detach 2000 Database To 2005

Jul 15, 2006

I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.

The differences I noticed are:

1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.

I don't understand why #1 happens.

Are there any other differences between Backup/Restore and Attach/Detach?



Thanks,

Peter

View 2 Replies View Related

Attach And Detach DB

Aug 11, 2004

Hello,

MSSQLSERVER VER : 2000
Using - Attach and Detach Database to move the database from one Server to another server.
I did detached the database from Production and attached my test server, everything OK

Questions about - syslogins & sysdevices data dictionary tables
1. When I select * from sysdevices on production it shows all the database name size,phyname etc. After attaching to my test server I cannot see these things.. I know because it different master database but, how do I fix this.
2. syslogins - is there way to move all the syslogins after attaching to test server.

Please let me know if you guys have done anything on this.

Thanks.

Regards,
K.

View 2 Replies View Related

Detach And Attach DB To SQL

May 23, 2006

How I can detach from DB and validate it?
(I mean check if it is detached or not)
Is there any why to return a value from sp_detach_db in C++?


(may a cross posting, please check under developer posts)

View 1 Replies View Related

Detach && Attach Database

Oct 10, 2006

I've just created ASPNETDB database with ASP.NET Security. Now, I want to send this db to orther computer.First, I  detached this db, then when I used attach database in that computer, there is an error :Error 602: Could not find row in sysindexes for database ID 8, object ID 1, indext ID 1. RUN DBCC CHECKTABLE on sysindexes. Please help me .Thank.

View 3 Replies View Related

Attach And Detach Db Woes

Jan 4, 2002

I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt"). The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.

I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:

EXEC sp_detach_db 'SNB01', 'true'

And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = 'labntewmssqldataSNB01_Data.mdf',
@filename2 = 'labntewmssqldataSNB01_log.ldf'

The above path was given me by our network admin guy.


QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'labntewmssqldataSNB01_Data.mdf' may be incorrect.


Please help. I am thrashing about. D. Lewis

View 7 Replies View Related

Attach/Detach Database

Nov 11, 2001

In SQL when I right click on Databases the option to use Attach/Detach is greyed out. I don't understand why. I'm running SQL 7 on Win2000 server and I have Win2k at sp2 and SQL at sp2.

Help please!

Chris

View 1 Replies View Related

Detach On SQL 2000, Attach On SQL 7

Sep 23, 2004

Hi folks,

Is it possible to detach a database on SQL Server 2000 and attach it to SQL Server 7 ? I know it's fine to do the other way around. Also, any adverse effects I should be aware of ?

Thanks

View 3 Replies View Related

Database Detach/attach

Jan 2, 2004

An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.

A.mdf - 123000KB 25/12/2003
A_log.ldf - 14000KB 25/12/2003
B.mdf - 67000KB 25/12/2003
B_log.ldf - 1024KB 25/12/2003

In this case, which mdf & ldf should be the correct database?
If I I remove A.mdf database by "detach", will B.mdf database work?

SQL server 2000 and SP3 installed.

Assistance is appreciated

View 9 Replies View Related

How Can I Attach And Detach My MDf File

Jun 9, 2008

Hello Experts, How Can i Attach and Detach my MDf file to an instance of SQL server 2005 Programatically or by T-SQL :)

ElmasryA1

View 1 Replies View Related

Detach, Move, Attach Again

Mar 23, 2006

I created a db in ssms; I detached it and moved it into a subdirectory of a vb app. Now sql server says the database does not exist. If I recreate the database in smss, I can run an INSERT INTO using that db as the destination. However, this only works if I keep it in the mssql data directory. How can I move it to the apps subdirectory so that it can be deployed with the app and still work? BTW, I also tried creating the databases in VS2005 with same results.

This is the connection string (tried this and non-express version with same results):

  strDbConn = "Provider=SQLNCLI;Data  Source=.SQLEXPRESS; AttachDbFilename=" & DbPath & ";User Instance=True;Trusted_Connection=Yes"

This is the sql statement:

  sqlCopyTable = "SELECT * INTO "
  sqlCopyTable = sqlCopyTable & CleanDbName & ".dbo." & CleanTableName
  sqlCopyTable = sqlCopyTable & " FROM " & RawTableName & ";"
  ConnectSQLExpress RawDbPath, "Source"
  ConnectSQLExpress CleanDbPath, "Destination"
  SourceDbConn.Execute sqlCopyTable

View 1 Replies View Related

Detach/Attach DB VS Stopping SQL Server

Mar 21, 2002

What problems could be invoked by stopping SQL Server and copying a user databases data and log files and pasting these files over existing files on another machine.

Ex:
MyDB on Server1
MyDB on Server2
Stop SQL Server Server1 and Server2
Copy Server1.MyDB files
Over write Server2.MyDB files
Start SQL Server Server1 and Server2

Make sense. This scenerio seems to work but wonder what side affects could result.

Any comments would be helpful!!

View 1 Replies View Related

Attach And Detach My Mdf File Programatically T.SQ

Jun 9, 2008

Hello Professionals, How can i attach and detach my mdf file to an instanse of sql 2005 programatically or by T-SQL

ElmasryA1

View 1 Replies View Related

Attach/Detach On SqlExpress 2005

Sep 8, 2005

Alan writes "Is it possible to create scripts which can be started remotely
to attach and detach databases into SQLExpress 2005.
If so how."

View 1 Replies View Related

Question On Detach/attach Database

May 21, 2007

Hi, all experts here,

Thank you for your kind attention.

I am having a question on detach/attach database. As when we detach/attach database, we have to copy both of .mdf and .ldf files to the targeted filepaths, in this case, when the database is large, then it takes a pretty while to copy the physical files though. In this case, I dont really see the point to detach/attach the database? Instead, backup/restore may be faster? (also, as detach the database, the files has to be taken offline)

Just confused about the way we trasfer database files with detach/attach. I am looking forward to hearing from you shortly for any advices for that.

With best regards,

Yours sincerely,

View 3 Replies View Related

How To Attach And Detach Mirrored Databases

Apr 15, 2008

Hello all,

We are planning to change the location of the log files from my production server. We have setup the mirroring also for those databases. I know for the databases without mirroring job i could detach the database, copy the log file to the target location and attach the database with the new logfile location. But for the mirroring databases I don't have any idea how to do that. Could some body guide me thorugh the process on how to do this task? thank you very much for all your help!

View 16 Replies View Related

Detach And Attach Functions In SQL Server 2005

Apr 24, 2006

Hi,

View 5 Replies View Related

Can Detach/Attach MSDB To Retain My DTS Info?

Jun 18, 2002

We have a hardware problem and will be getting a new RAID HDD system.

I've asked the tech onsite to backup and detach the MSDB and then reattach.

Can this be done, I expect to have him detach the new MSDB when MSSQL is re-installed. Then replace that file with the previously detached version and run reattach from QA(?)

Will this work.

TIA

JeffP...

View 3 Replies View Related

Using Detach/Attach Database To Copy/move

May 9, 2001

This might sound dumb, but BOL says you can use detach/attach to "MOVE" a database but can you detach and copy the files to a new server, leaving the files on the original and running the attach on the new and the old? (effectually a way to copy)? I know how to use data transformations/import,export was curious?

Help appreciated!!

View 4 Replies View Related

Detach,attach Db And Login,user And Master Database

Jan 30, 2000

I have similar question like Tim asked but from a different angle. I wish I pose my question here too anybody can help answer this.

I tried to use detach and attach method to do backup of the user database (not master, maybe we can do master too) on another server, but it looks the login and permission credential would not go together. I come to see different kind of message I tried to login as non-sa user after I attatch the database on different machine. I would be denied for SQL timeout. It looks like loginand user dose not exist in new server because I did not move master database too. But even if I recreate the login when I tried to associate user with login I will see a message saying user or role already exist in database. If I go back to check the user it did not show up on enterprize manager on that database.

So my question would be
1). how to move login and user together with user database.
2) do we have to move master database where the login infor. reside
3). if so how to move master database from one server to another when other server already have master database exist?

Hope somebody can answer this.


Wislon

View 2 Replies View Related

Detach/attach Move Fails To Bring Over Roles & Logins Correctly

May 23, 2001

A database move was accomplished by detaching the database from the legacy server and attaching it to the new server. The servers ran NT 4, SP6a, SQL 7 SP2. The new server had the same name and IP as the old server.

However that process (detach attach) does not bring over the users (who had access to the database on the legacy systems) correctly.

Under databases, roles the users existed. Under security, logins they did not.

We were obliged to drop the roles and recreate the logins and assign new roles. In the case of an NT user group we were unable to drop it. We had to create a new one and add the users to that group under User Manager.


So far I have not found anything that deals with the user account/role problem.

We have to move a similar database tonight. Do any of you know of an alternative "move" that brings the user roles/logins across correctly?

Thanks in advance.

View 4 Replies View Related

Detach-copy-attach: Can Not Copy Files - Getting Access Denied

Feb 28, 2008



Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';

then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<

I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.

How to find out, which process locks the files?
Best regards

View 7 Replies View Related

Detach Database, Attach Database Without Log File

Jul 20, 2005

If I detach a database on a server, then attach the database with asingle file only using the data file...will I lose any transactionsthat were in the original log file? There is a debate going on here atwork where some people think that during the detach, transactions inthe log file are saved to the data file.Thanks!

View 4 Replies View Related

How To Attach Or Restore Db From .mdf And Ldf Files After Db Deleted

Aug 19, 2002

First, let me say that I have already reviewed information posted by experts within the chat area.

Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.


Here is what I have tried in the Query Analyzer, but to no avail:


For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:

use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett_log.ldf'

Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]


use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett.mdf'

Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]


I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.

use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2_log.ldf'

Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.

If I use a different db name I get the following error:
Server: Msg 5105, Level 16, State 13, Line 1
Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.


use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2.mdf'

Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.

I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.

Any help would greatly be appreciated.

Thanks,

-Brett

View 2 Replies View Related

Database Restore && Attach Error

Apr 13, 2003

Database Restore & Attach Error
I have a problem with restoring or attaching DB files from different version of the engine. I Have a existing SQL server 7.x and another one is SQL 2000 but I try to backup my database and restore it into the 2000 engine This is the following Problem which occurs :-

1st Problem : During Restoration, The restoration process was runing fine until it reach the last inficatior tab and it fail and say Database terminated abnormal. I been try to restore into other machine also but it give me the same problem.

2nd Problem : Say I can't restore but I stop the engine and manual copy the *.mdf and *.ldf to another machine. I try to restore it but it "is on a network device not supported for database files." So what shall I Do with it.

Pls Advise If anything need to be done before I do the restoring and anything need to be done in both 7.x and 2000 Engine.

:confused:

View 1 Replies View Related

Urgent - Unable To Restore/attach A MDF File

Jul 7, 2004

Hi,

I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).

I have tried restoring and attaching options but nothing works :(

I also get the error saying

"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".

Please this is urgent. WIll be VERY grateful for your urgent reply.

View 7 Replies View Related

Urgent - Unable To Restore/attach A MDF File

Jul 7, 2004

Hi,

I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).

I have tried restoring and attaching options but nothing works :(

I also get the error saying

"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".

Please this is really urgent. Will be VERY grateful for your urgent reply.

View 13 Replies View Related

Attach/restore Databases In SQL Server 2000

Jun 19, 2007

Hi,

I've got a question regarding attaching/restoring of DBs and wonder if anyone could help me out.



Does SQL Server 2000 provide a functionality to attach/restore DB automatically? ie, some kind of polling service to attach/restore DB that were detached previously?





Thanks

Danny.

View 6 Replies View Related

Automatically Enable Broker After Restore Or Attach

Aug 22, 2007

Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.

View 3 Replies View Related

SQL Server Express: Restore, Backup And Attach Do Not Work Anymore &&amp; Database Locations Fault

May 7, 2008

Hello there,

I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.

Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.

And there are more problems see here:

Restoring does not work=======================
I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:

"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"

But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.

Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????


Attaching does not work=======================
Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press

Attach Databases -> Add

The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData".
Why can't Express see the directory on the D: partition???


What is wrong? Here? Can anybody help me?

View 5 Replies View Related

Attach Aspnetdb.mdf Failed, But I Can't Find Attach Statement In The Web.config

Nov 21, 2007

An attempt to attach an auto-named database for file C:WebApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.  
My web.config file, the connection strings
<connectionStrings>
  <add name="POAdatabaseConnectionString1" connectionString="Data Source=sss;Persist Security Info=True;Initial Catalog=POAdatabase.mdf;Integrated Security=SSPI"   providerName="System.Data.SqlClient" />
 </connectionStrings>
I cann't really find the statement for attaching aspnetdb.mdf. I don't where to find it. Is there any other web.config file in the C drive? Thanks for help!
 

View 3 Replies View Related

Cannot Detach Db

Aug 21, 2002

I am trying to detach an SQL7 db and reattach it to an SQL2K server. When I run sp_who I get a list of 10-15 processes that are running, most under my sa login. If I kill all the others and then try to run sp_detach_db I get the following:

Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'snb01' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I have detached and reattached before, but I don't recall what I did differently. Pls help. TIA D. Lewis

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved