-1

I need to integrate a few lines of code into my application , which allow me to copy a database from one MSSQL server to another MSSQL server, the code header should be like this

procedure MoveDataBase (OldServername : String; newServerName : String ; Databasename : String); begin

end;

Any short code and fast working Idea ? Copy a Database using MS Explorer is no option

user1769184
  • 1,571
  • 1
  • 19
  • 44
  • Connection to MSSQL server needs more than just the server- and databasename. Database copy with Explorer? Well, somehow possible, but **very** unusual – Sir Rufo Mar 15 '13 at 13:34
  • 3
    You're best off doing a backup/restore of the database, or implementing replication. – Jerry Dodge Mar 15 '13 at 13:40
  • Backup/Restore MSSQL Doc http://msdn.microsoft.com/en-us/library/ff848768.aspx – Sir Rufo Mar 15 '13 at 13:43
  • 3
    There is no such "short code" or "few lines of code" to do this task. you can resort to DTS or use backup/restore, or detach the database copy it to new one, and attach both. using ADO with SQL scripting is doable but very far from being "short" and "few lines" :) – kobik Mar 15 '13 at 13:47
  • Keep in mind that when doing backup/restore operations, the backup file must be saved to / loaded from the same physical machine that the SQL server is running. You cannot connect to a remote SQL server and backup/restore using a backup file on your local machine. – Jerry Dodge Mar 15 '13 at 14:46

1 Answers1

2

The easiest (and maybe also the dumbest) way of transfering a database from one server to the other is to:
1. detach the database on the source server using sp_detach_db
2. copy the physical MDF file to the destination server. You may copy the LDF as well, but imho it is not necessary.
3. attach the database on the destination server using sp_attach_db

You might also replace #1 and #3 by a BACKUP/RESTORE as stated earlier. In any case (I am repeating the commments), you'd need a bit more than just server name and database, i.e. login credentials, for example.

alzaimar
  • 4,572
  • 1
  • 16
  • 30
  • can I do sp_detach_db using TADOQuery ? – user1769184 Mar 15 '13 at 16:34
  • 1
    Not sure, but you could try `EXEC sp_detatch_db`. But you can also use a TADOConnection to achieve this. – alzaimar Mar 15 '13 at 16:36
  • Be carefull with Backup/Restore! Most companies do regular Backups of their DBs on regular basis, because they have disaster recovery plans (i.e. restore DBs on disaster). Every backup becomes a part of the backup chain. In case of disaster/corruption they will need the full backup chain. If you "grab" your backup and wipe it (say you don't need it at some time) - they will not be able to restore their DBs!!! Be careful - some companies may have legal claims on this! Instead make "Copy Only Backup" or take a snapshot. And for sure reconcile it with the DB team. – iPath ツ Mar 15 '13 at 19:37
  • Isn't that the difference between incremental and full backup? – alzaimar Mar 15 '13 at 21:29