It easier than you think.
1. In the source SQL server you right klick on the database you wich to move or copy. Chose “Tasks”, then “Generate Scripts.." Run this wizart to export all objects in the database. Unde advanced chose to save it for SQL 2005 or whatever version it is.
2. On the destination server run the script to create the database and all objects in it.
3. Now we have an empty database. Let’s then export import the data. For this we use the BCP utility. On the source server run this script in a query window.(thanks for this script Madhivanan)
declare @tables table(table_name varchar(100))
insert into @tables
select name from sysobjects where xtype ='u'
declare @table_name varchar(1000)
select @table_name=min(table_name)from @tables
while @table_name>''
Begin
Declare @str varchar(1000)
set @str='Exec Master..xp_Cmdshell ''bcp '+db_name()+'.dbo.'+@table_name+' out C:\temp\test\'+@table_name+'.txt -T -c'''
Exec(@str)
select @table_name=min(table_name) from @tables where table_name>@table_name
print @str
End
4. Now we export all the data to textfiles. Every table has a textfile each. Let’s import them now.
5. On the target server run same scrip again but change out to ín instead. Be sure to use the correct path to the files also.
That’s all, easy and quite fast way to go.
Joel Sjöö
