SQl 2005 to sql 2008

hi ,

we have sql 2005 in 1 physical server running on windows 2003 platform, have new server sql 2008 sp1 running on windows 2008 , can 1 me move windows 2003 server sql 2005 db's windows 2008 sql 2008 sp1 db?

 

hi,

there are many ways move/copy database 1 server server using sql server tools.

i) detach database old server , attach in new server. purely offline operation , moves database instead of copying it.

refer http://msdn.microsoft.com/en-us/library/ms190209.aspx : how use attach/detach database in sql server management studio(ssms)

ii) database in old server , restore in destination server. can performed during online , creates new database in destination server.

refer http://msdn.microsoft.com/en-us/library/ms187048.aspx for more information.

iii) using copy database wizard in sql server management studio. 

select database in source server in ssms
right click ->tasks->copy database wizard launch copy database wizard.
enter source , destination credentials , select either attach/detach or smo type
click next , can schedule or run immediately
click finish execute it

refer http://msdn.microsoft.com/en-us/library/ms188664.aspx for more information.

iv) last type generate create script using generate script wizard (ssms)  , execute in destination server.

select database in source server in ssms
right click ->tasks->generate scripts wizard launch wizard.
select various scripting options needed , select objects needed generate scripts them. make sure script data = true in scripting option generate script data (insert statements)

click next ->next , finish generate script (new query window or clip board or file)
connect destination server , create new database in it.
click new query window , paste script generated using gsw above , execute them destination database context.

refer http://msdn.microsoft.com/en-us/library/ms181421.aspx for more information

v) using transfer object in smo

sample code:
scriptingoptions = new scriptingoptions();
so.scriptdata = true;
transfer t = new transfer(db);
t.copyallobjets = true;
t.options = so;
...................
..................
 t.transferdata();

note: transfer class available in microsoft.sqlserver.smoextendedclass.dll  (sql server 2008)
or microsoft.sqlserver.smo.dll (sql server 2005)

there various member variables configurable. scriptingoptions class object can created , assigned transfer object well.

set scriptdata = true in order transfer data also. copies destination instead of moving database

refer  http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx for more information.

vi) can make use of database publishing wizard to accomplish this. can specify target version sql 2005 or sql 2000 etc per requirement.

SQL Server  >  Getting started with SQL Server