in Management Studio
right click on database
Tasks
Import/Export
Main Topics
Browse All TopicsHi I have the folowing isseu.
I have a corupted data base, I didn't succeed to repair it with the
following with the command dbcc checkdb (database name, repair_allow
_data_loss) because de data base is not properly closed.
the problem is that the log file is corupted and the database is not
properly closed
but in the (Emergency state) I'm able to export the data to an empty
database. but it doesn't copy the settings like defaulth valeu's primary key
etc.
it's a database with 450 tables with a lot of this settings.
I created an empty data base with all the tables and settings without the
data, so my idea was to import only the content of the tables.
is there an easy way to do this.
or do you have an other solution.
pleas advice
Thanks
Marcel
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
>> the problem is that the log file is corupted and the database is not properly closed
Kindly let me know how did you confirmed this one..
If this is the case, then this should work
1. Detach your Database.
2. Delete your LDF file in the database directory.
3. Attach your database again without specifying LDF file ( This would create a new one)
--------------------------
TITLE: Microsoft SQL Server Management Studio
--------------------------
Attach database failed for Server 'NH-SQL02-W2K8\EXACTGLOBEK
For help, click: http://go.microsoft.com/fw
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
Unable to open the physical file "D:\SQLExactGlobe\050.ldf"
Could not open new database '050'. CREATE DATABASE is aborted.
File activation failure. The physical file name "D:\SQLExactGlobe\050.ldf"
The log cannot be rebuilt because the database was not cleanly shut down. (Microsoft SQL Server, Error: 5120)
For help, click: http://go.microsoft.com/fw
--------------------------
BUTTONS:
OK
--------------------------
Reza rad
The import export wizzart is part of SSIS, or do you mean an other tool which and how to use ?
for example a origanal table look like this
USE [053]
GO
/****** Object: Table [dbo].[aaccod] Script Date: 11/09/2009 07:53:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aaccod](
[ID] [int] IDENTITY(1,1) NOT NULL,
[analyt_fld] [char](20) NULL,
[descr30_0] [varchar](30) NULL,
[descr30_1] [varchar](30) NULL,
[descr30_2] [varchar](30) NULL,
[descr30_3] [varchar](30) NULL,
[descr30_4] [varchar](30) NULL,
[length] [int] NOT NULL DEFAULT (0),
[xprecision] [int] NOT NULL DEFAULT (0),
[capitals] [tinyint] NOT NULL DEFAULT (0),
[positive] [tinyint] NOT NULL DEFAULT (0),
[analyt_tbl] [char](10) NULL,
[active] [tinyint] NOT NULL DEFAULT (1),
[mandatory] [tinyint] NOT NULL DEFAULT (0),
[adjustable] [tinyint] NOT NULL DEFAULT (1),
[balanced] [tinyint] NOT NULL DEFAULT (0),
[allocation] [tinyint] NOT NULL DEFAULT (0),
[multiple_lines] [tinyint] NOT NULL DEFAULT (0),
[seq_no] [int] NOT NULL DEFAULT (0),
[linkto_items] [tinyint] NOT NULL DEFAULT (0),
[linkto_glaccounts] [tinyint] NOT NULL DEFAULT (0),
[linkto_debtors] [tinyint] NOT NULL DEFAULT (0),
[linkto_creditors] [tinyint] NOT NULL DEFAULT (0),
[linkto_projects] [tinyint] NOT NULL DEFAULT (0),
[linkto_humres] [tinyint] NOT NULL DEFAULT (0),
[linkto_assets] [tinyint] NOT NULL DEFAULT (0),
[linkto_table1] [tinyint] NOT NULL DEFAULT (0),
[linkto_table2] [tinyint] NOT NULL DEFAULT (0),
[linkto_table3] [tinyint] NOT NULL DEFAULT (0),
[linkto_table4] [tinyint] NOT NULL DEFAULT (0),
[linkto_table5] [tinyint] NOT NULL DEFAULT (0),
[linkto_table6] [tinyint] NOT NULL DEFAULT (0),
[linkto_table7] [tinyint] NOT NULL DEFAULT (0),
[linkto_table8] [tinyint] NOT NULL DEFAULT (0),
[linkto_table9] [tinyint] NOT NULL DEFAULT (0),
[linkto_table10] [tinyint] NOT NULL DEFAULT (0),
[syscreated] [datetime] NOT NULL DEFAULT (getdate()),
[expression] [char](200) NULL,
[syscreator] [int] NOT NULL DEFAULT (0),
[sysmodified] [datetime] NOT NULL DEFAULT (getdate()),
[sysmodifier] [int] NOT NULL DEFAULT (0),
[sysguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
[timestamp] [timestamp] NOT NULL,
[analyt_fieldname] [char](30) NULL,
[Division] [smallint] NULL,
CONSTRAINT [PK_aaccod] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
after I use the import export wizzard it look likes this
USE [053]
GO
/****** Object: Table [dbo].[aaccod] Script Date: 11/09/2009 07:53:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aaccod](
[ID] [int] IDENTITY(1,1) NOT NULL,
[analyt_fld] [char](20) NULL,
[descr30_0] [varchar](30) NULL,
[descr30_1] [varchar](30) NULL,
[descr30_2] [varchar](30) NULL,
[descr30_3] [varchar](30) NULL,
[descr30_4] [varchar](30) NULL,
[length] [int] NOT NULL DEFAULT (0),
[xprecision] [int] NOT NULL DEFAULT (0),
[capitals] [tinyint] NOT NULL DEFAULT (0),
[positive] [tinyint] NOT NULL DEFAULT (0),
[analyt_tbl] [char](10) NULL,
[active] [tinyint] NOT NULL DEFAULT (1),
[mandatory] [tinyint] NOT NULL DEFAULT (0),
[adjustable] [tinyint] NOT NULL DEFAULT (1),
[balanced] [tinyint] NOT NULL DEFAULT (0),
[allocation] [tinyint] NOT NULL DEFAULT (0),
[multiple_lines] [tinyint] NOT NULL DEFAULT (0),
[seq_no] [int] NOT NULL DEFAULT (0),
[linkto_items] [tinyint] NOT NULL DEFAULT (0),
[linkto_glaccounts] [tinyint] NOT NULL DEFAULT (0),
[linkto_debtors] [tinyint] NOT NULL DEFAULT (0),
[linkto_creditors] [tinyint] NOT NULL DEFAULT (0),
[linkto_projects] [tinyint] NOT NULL DEFAULT (0),
[linkto_humres] [tinyint] NOT NULL DEFAULT (0),
[linkto_assets] [tinyint] NOT NULL DEFAULT (0),
[linkto_table1] [tinyint] NOT NULL DEFAULT (0),
[linkto_table2] [tinyint] NOT NULL DEFAULT (0),
[linkto_table3] [tinyint] NOT NULL DEFAULT (0),
[linkto_table4] [tinyint] NOT NULL DEFAULT (0),
[linkto_table5] [tinyint] NOT NULL DEFAULT (0),
[linkto_table6] [tinyint] NOT NULL DEFAULT (0),
[linkto_table7] [tinyint] NOT NULL DEFAULT (0),
[linkto_table8] [tinyint] NOT NULL DEFAULT (0),
[linkto_table9] [tinyint] NOT NULL DEFAULT (0),
[linkto_table10] [tinyint] NOT NULL DEFAULT (0),
[syscreated] [datetime] NOT NULL DEFAULT (getdate()),
[expression] [char](200) NULL,
[syscreator] [int] NOT NULL DEFAULT (0),
[sysmodified] [datetime] NOT NULL DEFAULT (getdate()),
[sysmodifier] [int] NOT NULL DEFAULT (0),
[sysguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
[timestamp] [timestamp] NOT NULL,
[analyt_fieldname] [char](30) NULL,
[Division] [smallint] NULL,
CONSTRAINT [PK_aaccod] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
sorry I ment like this
GO
/****** Object: Table [dbo].[aaccod] Script Date: 11/09/2009 09:14:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aaccod](
[ID] [int] NOT NULL,
[analyt_fld] [char](20) NULL,
[descr30_0] [varchar](30) NULL,
[descr30_1] [varchar](30) NULL,
[descr30_2] [varchar](30) NULL,
[descr30_3] [varchar](30) NULL,
[descr30_4] [varchar](30) NULL,
[length] [int] NOT NULL,
[xprecision] [int] NOT NULL,
[capitals] [tinyint] NOT NULL,
[positive] [tinyint] NOT NULL,
[analyt_tbl] [char](10) NULL,
[active] [tinyint] NOT NULL,
[mandatory] [tinyint] NOT NULL,
[adjustable] [tinyint] NOT NULL,
[balanced] [tinyint] NOT NULL,
[allocation] [tinyint] NOT NULL,
[multiple_lines] [tinyint] NOT NULL,
[seq_no] [int] NOT NULL,
[linkto_items] [tinyint] NOT NULL,
[linkto_glaccounts] [tinyint] NOT NULL,
[linkto_debtors] [tinyint] NOT NULL,
[linkto_creditors] [tinyint] NOT NULL,
[linkto_projects] [tinyint] NOT NULL,
[linkto_humres] [tinyint] NOT NULL,
[linkto_assets] [tinyint] NOT NULL,
[linkto_table1] [tinyint] NOT NULL,
[linkto_table2] [tinyint] NOT NULL,
[linkto_table3] [tinyint] NOT NULL,
[linkto_table4] [tinyint] NOT NULL,
[linkto_table5] [tinyint] NOT NULL,
[linkto_table6] [tinyint] NOT NULL,
[linkto_table7] [tinyint] NOT NULL,
[linkto_table8] [tinyint] NOT NULL,
[linkto_table9] [tinyint] NOT NULL,
[linkto_table10] [tinyint] NOT NULL,
[syscreated] [datetime] NOT NULL,
[expression] [char](200) NULL,
[syscreator] [int] NOT NULL,
[sysmodified] [datetime] NOT NULL,
[sysmodifier] [int] NOT NULL,
[sysguid] [uniqueidentifier] NOT NULL,
[timestamp] [timestamp] NOT NULL,
[analyt_fieldname] [char](30) NULL,
[Division] [smallint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
OK I got this.
What about this solution:
generate a sql script from management studio :
right click on database ->Tasks->generate sql script
set options for generate script for all tables structures with keys and indexes and ...
then run this script on destination database, this will create correct tables structures there
then run import/export to transfer data
Reza rad
Thanks this is indead the right steps and order to do this.
during import I used commands below to diable constraint and trigger
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
EXEC sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
Marcel
Business Accounts
Answer for Membership
by: reza_radPosted on 2009-11-08 at 14:19:49ID: 25772234
You can do it via Import/Export or an SSIS package
if your source and destination tables are the same, just move data, this is simple way