Copy data from one database to another using scrip

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copy data from one database to another using scrip

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2008-08-12 : 10:59:27

Could some one please help me in how I would go about creating a script/s for copying tables and the data in them from one database to another database on a different server both databases are identical.

Database one is on the production server and its call Beamline.
WINCSQL1\PRODUCTION
Database two is on the development server and it’s also call Beamline.
WINCSQL1\DEVELOPMENT

I know there are several different ways of doing the task but my hands have being tie by the IT department and I’m being force to use scrip’s.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 12:56:10

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

chapo
Starting Member

39 Posts

Posted - 2008-08-12 : 13:11:23

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

Thank for the quick responce, I was also wondering if I could also use the bulk copy command and if so how would I do so.

chapo
Starting Member

39 Posts

Posted - 2008-08-13 : 08:24:54

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

I try using the above method but I cannot see my database on the Select Database screen.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:52:36

quote:
Originally posted by chapo

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

I try using the above method but I cannot see my database on the Select Database screen.

Why? are you doing this on right server?

chapo
Starting Member

39 Posts

Posted - 2008-08-13 : 13:09:19

quote:
Originally posted by visakh16

quote:
Originally posted by chapo

quote:
Originally posted by visakh16

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

I try using the above method but I cannot see my database on the Select Database screen.

Why? are you doing this on right server?

I'm doing it on the right server because I can see all my other databases. On another note will I need rights on the server since I'm creating a file.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:12:41

Nope. it generates the script onto query window which you can save localy.cant understand why you cant see this particular db on your server.

chapo
Starting Member

39 Posts

Posted - 2008-08-13 : 13:49:54

quote:
Originally posted by visakh16

Nope. it generates the script onto query window which you can save localy.cant understand why you cant see this particular db on your server.

I try doing it for another database and I get the following errors

Msg 1801, Level 16, State 3, Line 1
Database 'WaltersSIM' already exists.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'WALTERS\JoseR' already exists in the current database.
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'WALTERS\joels' already exists in the current database.
Msg 2714, Level 16, State 3, Procedure dwg_ShopIssueNo, Line 19
There is already an object named 'dwg_ShopIssueNo' in the database.
Msg 2714, Level 16, State 3, Procedure si_PartList, Line 18
There is already an object named 'si_PartList' in the database.
Msg 2714, Level 16, State 3, Procedure si_DrawingList, Line 17
There is already an object named 'si_DrawingList' in the database.
Msg 2714, Level 16, State 3, Procedure si_BoltList, Line 18
There is already an object named 'si_BoltList' in the database.
Msg 2714, Level 16, State 3, Procedure dwg_Create, Line 34
There is already an object named 'dwg_Create' in the database.
Msg 2714, Level 16, State 3, Procedure dwg_Delete, Line 19
There is already an object named 'dwg_Delete' in the database.
Msg 2714, Level 16, State 3, Procedure ass_IncorrectObjectList, Line 18
There is already an object named 'ass_IncorrectObjectList' in the database.
Msg 2714, Level 16, State 3, Procedure si_Delete, Line 17
There is already an object named 'si_Delete' in the database.
Msg 2714, Level 16, State 3, Procedure si_DrawingCount, Line 16
There is already an object named 'si_DrawingCount' in the database.
Msg 2714, Level 16, State 3, Procedure prt_Create, Line 41
There is already an object named 'prt_Create' in the database.
Msg 2714, Level 16, State 3, Procedure si_DrawingCountAssembly, Line 16
There is already an object named 'si_DrawingCountAssembly' in the database.
Msg 2714, Level 16, State 3, Procedure si_MarkCount, Line 16
There is already an object named 'si_MarkCount' in the database.
Msg 2714, Level 16, State 3, Procedure si_MarkCountAssembly, Line 16
There is already an object named 'si_MarkCountAssembly' in the database.
Msg 2714, Level 16, State 3, Procedure si_PieceCount, Line 16
There is already an object named 'si_PieceCount' in the database.
Msg 2714, Level 16, State 3, Procedure si_MarkCountWorkshop, Line 16
There is already an object named 'si_MarkCountWorkshop' in the database.
Msg 2714, Level 16, State 3, Procedure si_Weight, Line 16
There is already an object named 'si_Weight' in the database.
Msg 2714, Level 16, State 3, Procedure si_AssemblyCount, Line 16
There is already an object named 'si_AssemblyCount' in the database.
Msg 2714, Level 16, State 3, Procedure blt_Create, Line 41
There is already an object named 'blt_Create' in the database.
Msg 2714, Level 16, State 3, Procedure si_DrawingCountWorkshop, Line 16
There is already an object named 'si_DrawingCountWorkshop' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblBolts' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblGlobalRules' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblProjects' in the database.
Msg 2714, Level 16, State 3, Procedure si_NextAvailable, Line 16
There is already an object named 'si_NextAvailable' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblAssemblies' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblDrawings' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblAttachments' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblErrors' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblParts' in the database.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tblShopIssues' in the database.
Msg 2714, Level 16, State 3, Procedure blt_CatalogValues, Line 16
There is already an object named 'blt_CatalogValues' in the database.
Msg 2714, Level 16, State 3, Procedure blt_Syncronize, Line 14
There is already an object named 'blt_Syncronize' in the database.
Msg 2714, Level 16, State 3, Procedure si_Create, Line 19
There is already an object named 'si_Create' in the database.
Msg 2714, Level 16, State 3, Procedure si_ShopIssueList, Line 16
There is already an object named 'si_ShopIssueList' in the database.
Msg 2714, Level 16, State 3, Procedure si_UpdateStatus, Line 18
There is already an object named 'si_UpdateStatus' in the database.
Msg 2714, Level 16, State 3, Procedure si_Exists, Line 16
There is already an object named 'si_Exists' in the database.
Msg 2714, Level 16, State 3, Procedure EmptyData, Line 8
There is already an object named 'EmptyData' in the database.
Msg 2714, Level 16, State 3, Procedure ass_Delete, Line 17
There is already an object named 'ass_Delete' in the database.
Msg 2714, Level 16, State 3, Procedure ass_ShopIssueNo, Line 17
There is already an object named 'ass_ShopIssueNo' in the database.
Msg 2714, Level 16, State 3, Procedure ass_Create, Line 23
There is already an object named 'ass_Create' in the database.
Msg 2714, Level 16, State 3, Procedure ass_DeleteAll, Line 17
There is already an object named 'ass_DeleteAll' in the database.
Msg 2714, Level 16, State 3, Procedure exp_AssemblyList, Line 17
There is already an object named 'exp_AssemblyList' in the database.
Msg 2714, Level 16, State 3, Procedure div_List, Line 13
There is already an object named 'div_List' in the database.
Msg 2714, Level 16, State 3, Procedure ass_SyncronizeWithDrawingList, Line 17
There is already an object named 'ass_SyncronizeWithDrawingList' in the database.
Msg 2714, Level 16, State 3, Procedure exp_DrawingList, Line 21
There is already an object named 'exp_DrawingList' in the database.
Msg 2714, Level 16, State 3, Procedure exp_RevisionList, Line 21
There is already an object named 'exp_RevisionList' in the database.
Msg 2714, Level 16, State 3, Procedure dwg_ShopIssueList, Line 13
There is already an object named 'dwg_ShopIssueList' in the database.
Msg 2714, Level 16, State 3, Procedure ass_DrawingExists, Line 8
There is already an object named 'ass_DrawingExists' in the database.
Msg 2714, Level 16, State 3, Procedure si_AssemblyDrawingList, Line 17
There is already an object named 'si_AssemblyDrawingList' in the database.
Msg 2714, Level 16, State 3, Procedure att_Create, Line 18
There is already an object named 'att_Create' in the database.
Msg 2714, Level 16, State 3, Procedure si_AttachmentList, Line 17
There is already an object named 'si_AttachmentList' in the database.
Msg 2714, Level 16, State 3, Procedure err_AssemblyList, Line 13
There is already an object named 'err_AssemblyList' in the database.
Msg 2714, Level 16, State 3, Procedure err_PartList, Line 13
There is already an object named 'err_PartList' in the database.
Msg 2714, Level 16, State 3, Procedure err_DrawingList, Line 13
There is already an object named 'err_DrawingList' in the database.
Msg 2714, Level 16, State 3, Procedure si_ErrDetails, Line 18
There is already an object named 'si_ErrDetails' in the database.
Msg 2714, Level 16, State 3, Procedure err_Create, Line 22
There is already an object named 'err_Create' in the database.
Msg 2714, Level 16, State 3, Procedure prt_IncorrectObjectList, Line 22
There is already an object named 'prt_IncorrectObjectList' in the database.
Msg 2714, Level 16, State 3, Procedure prt_ShopIssueNo, Line 17
There is already an object named 'prt_ShopIssueNo' in the database.
Msg 2714, Level 16, State 3, Procedure prt_Info, Line 17
There is already an object named 'prt_Info' in the database.
Msg 2714, Level 16, State 3, Procedure prt_AssemblyList, Line 13
There is already an object named 'prt_AssemblyList' in the database.
Msg 2714, Level 16, State 3, Procedure exp_PartList, Line 17
There is already an object named 'exp_PartList' in the database.
Msg 2714, Level 16, State 3, Procedure prj_List, Line 13
There is already an object named 'prj_List' in the database.
Msg 2714, Level 16, State 3, Procedure prj_Modify, Line 25
There is already an object named 'prj_Modify' in the database.
Msg 2714, Level 16, State 3, Procedure prj_ProjectList, Line 14
There is already an object named 'prj_ProjectList' in the database.
Msg 2714, Level 16, State 3, Procedure prj_Create, Line 27
There is already an object named 'prj_Create' in the database.
Msg 2714, Level 16, State 3, Procedure prj_Delete, Line 16
There is already an object named 'prj_Delete' in the database.
Msg 2714, Level 16, State 3, Procedure si_ErrList, Line 18
There is already an object named 'si_ErrList' in the database.
Msg 2714, Level 16, State 3, Procedure si_PartListSummary, Line 18
There is already an object named 'si_PartListSummary' in the database.
Msg 2714, Level 16, State 3, Procedure si_BoltListSummary, Line 19
There is already an object named 'si_BoltListSummary' in the database.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_tblAssemblies_tblShopIssues' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_tblDrawings_tblShopIssues' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_tblAttachments_tblShopIssues' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_tblErrors_tblShopIssues' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_tblParts_tblAssemblies1' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_tblShopIssues_tblProjects' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:56:26

it seems like you've use WaltersSIM statement on top. by default when you script from database it puts this as first statement. make sure you remove it before you apply it to another database.

chapo
Starting Member

39 Posts

Posted - 2008-08-13 : 14:19:01

quote:
Originally posted by visakh16

it seems like you've use WaltersSIM statement on top. by default when you script from database it puts this as first statement. make sure you remove it before you apply it to another database.

I just want to say thanks for all your help so far.
and the line you want me to delete is the following
CREATE DATABASE [WaltersSIM] ON PRIMARY

USE [master]
GO
CREATE DATABASE [WaltersSIM] ON PRIMARY
( NAME = N'WaltersSIM', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\WaltersSIM.mdf' , SIZE = 283648KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'WaltersSIM_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\WaltersSIM_log.ldf' , SIZE = 688384KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'WaltersSIM', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [WaltersSIM].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [WaltersSIM] SET ANSI_NULL_DEFAULT OFF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 14:25:29

Nope just replace the name WaltersSIM with name of your new database.isnt WaltersSIM your original db's name?

chapo
Starting Member

39 Posts

Posted - 2008-08-13 : 14:38:40

quote:
Originally posted by visakh16

Nope just replace the name WaltersSIM with name of your new database.isnt WaltersSIM your original db's name?

Okay but I want to keep the same name I'm just creating a copy on another server, now we originaly had a WaltersSIM database on that server so I deleted since it was outdated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 14:44:01

quote:
Originally posted by chapo

quote:
Originally posted by visakh16

Nope just replace the name WaltersSIM with name of your new database.isnt WaltersSIM your original db's name?

Okay but I want to keep the same name I'm just creating a copy on another server, now we originaly had a WaltersSIM database on that server so I deleted since it was outdated.

then that error wont come. the error suggested it found already a db by same name in your server.So i think you've not deleted it properly.

chapo
Starting Member

39 Posts

Posted - 2008-08-13 : 15:19:13

quote:
Originally posted by visakh16

quote:
Originally posted by chapo

quote:
Originally posted by visakh16

Nope just replace the name WaltersSIM with name of your new database.isnt WaltersSIM your original db's name?

Okay but I want to keep the same name I'm just creating a copy on another server, now we originaly had a WaltersSIM database on that server so I deleted since it was outdated.

then that error wont come. the error suggested it found already a db by same name in your server.So i think you've not deleted it properly.

Unfortunately I wont be able to check if there are any traces of the database until tomorrow when the IT guy gets back. Thanks for all your help so far

micoypogi
Starting Member

6 Posts

Posted - 2009-04-30 : 04:34:20

Hello,

would like to ask for some info regarding syntax on exporting excel files (which originated actually from SQL Server) to Firebird 1.5 database.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources