December 8, 2015 at 2:26 am
To copy a database from a newer version to an older version, in the past I generated code:
1. Switch the constraints to nocheck.
2. Copy the tables with identity from the source database to the target database.
3. Copy the tables without identity from the source database to the target database.
4. Switch the constraints to check.
Now I am re-using the code.
But run into the problem of Computed Columns. These should be excluded, this will take me some effort.
And I run into the problem of 'timestamp' columns.
Before spending some more effort in this copying method, I thought of first going online (here) and see if I am doing something that has allready be done by somebody else.
Is there a method to copy the a table where the 'timestamp' columns are not altered ?
Other hints/suggestions/methods for copying a database ?
Thanks for your time and attention,
Ben
Extract generating code.
The function Column_list is not included here.
---------------------------------------------------------------------------------------
-- All tables
select table_name into All_ from INFORMATION_SCHEMA.tables where table_type ='base table'
-- All tables with an identity
select table_name into Identity_ from INFORMATION_SCHEMA.columns where COLUMNPROPERTY ( OBJECT_ID(table_name) , column_name , 'IsIdentity' ) = 1
-- tables without an identity
select * into NOidentity_ from (select * from all_ except select * from identity_ ) xxx
-- views
SELECT * INTO view_ FROM INFORMATION_SCHEMA.tables WHERE TABLE_TYPE = 'VIEW'
-- remove views from NoIdentity and from identity
DELETE Z FROM NOidentity_ Z JOIN view_ V ON Z.TABLE_NAME = V.TABLE_NAME
DELETE M FROM identity_ M JOIN view_ V ON M.TABLE_NAME = V.TABLE_NAME
---------------------------------------------------------------------------------------
-- 1. Code to switch off all the constraints.
SET NOCOUNT ON
select 'alter table ['+c.table_name+'] nocheck constraint '+c.constraint_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON
R.CONSTRAINT_NAME = C.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE P ON
r.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME AND C.ORDINAL_POSITION = P.ORDINAL_POSITION
ORDER by p.TABLE_NAME, UNIQUE_CONSTRAINT_NAME, c.CONSTRAINT_NAME, P.ORDINAL_POSITION
-- 2. Code to copy a table. A column_list is generated. (Tables with an identity)
select
'
SET IDENTITY_INSERT ['+table_name+'] ON
insert into ['+table_name+'] ('+dbo.column_list(',[<COLUMN_NAME>]',TABLE_NAME, 99, '','')+')
select * from [SourceDatabase].[TestDb].dbo.['+table_name+']
SET IDENTITY_INSERT ['+table_name+'] OFF
'
from identity_ where table_name not in ('identity_','NOidentity_','all_', 'VIEW_')
-- 3. Code to copy a table. Tables without an identity
select
'
insert into ['+table_name+']
select * from [SourceDatabase].[TestDb].dbo.['+table_name+']
'
from NOidentity_ where table_name not in ('identity_','NOidentity_','all_', 'VIEW_')
-- 4. Code to switch on all the constraints.
select 'alter table ['+C.table_name+'] WITH CHECK check constraint '+C.constraint_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON
R.CONSTRAINT_NAME = C.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE P ON
r.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME AND C.ORDINAL_POSITION = P.ORDINAL_POSITION
ORDER by p.TABLE_NAME, UNIQUE_CONSTRAINT_NAME, p.CONSTRAINT_NAME, P.ORDINAL_POSITION
December 8, 2015 at 3:00 am
ben.brugman (12/8/2015)
Is there a method to copy the a table where the 'timestamp' columns are not altered ?
Ben
Timestamp is deprecated - you should use rowversion instead. If you insist on using it, SELECT INTO works, but I've never tried that across databases.
Other hints/suggestions/methods for copying a database ?
Have you tried backup and restore?
John
December 8, 2015 at 3:14 am
John Mitchell-245523 (12/8/2015)
Timestamp is deprecated
Thanks, I will not put an efford in this then. I'll solve this when I encounter them, without generating code for this. (Timestamps are rare anyway).
Have you tried backup and restore?
This does not work if you go to an older version of SQL-server.
Thanks,
Ben
Inserting the timestamp gives:
Msg 273, Level 16, State 1, Line 2
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
December 8, 2015 at 3:16 am
john,
Ben has mentioned from newer to older version. So backup restore wont work for him.
Ben,
You can create a generic script but you might need a lot of work in that direction.
I normally start with import / export wizard or SSIS packages.
But if you are serious on scripting, why don't you try SMO in an SSIS script task ?
December 8, 2015 at 3:50 am
joeroshan (12/8/2015)
You can create a generic script but you might need a lot of work in that direction.I normally start with import / export wizard or SSIS packages.
Using the Copy Wizard I run into a 'fail', SQL-logging does not give an error, Windows logging is not to clear. (So I am a bit stuck there).
But if you are serious on scripting, why don't you try SMO in an SSIS script task ?
Now the actual generation script is about 40 lines of code. I feel that I have control over these 40 lines of code and the generated script.
My experience with SSIS is that this adds a layer of complexity, where I normally run into some obscure errors. It always takes me some time to get an SSIS package to run. But this might be because of lack of experience with SSIS and SMO. With SSIS I always have the feeling that a part of it is trial and error and I do not feel completely in control.
Thanks,
Ben
I should be in control of SSIS, but lack the experience to feel in control over SSIS.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply