Copy Database

[ethorn10]

Hey guys...

I'm looking for a little guidance here (i.e. a work-around). I am aliased as dbo on both databases at hand here but I am not the actual administrator to where I can just do this myself -- cause it would certainly be faster than my admin getting back to me.

I'm trying to copy our production database to another database to be used as a test environment. I have tried relentlessly to use DTS, Import/Export, Copy Objects, etc. to no avail. The typical error I get is "Access is Denied. [SQL-DMO]CreateFile error on 'Server.DB.LOG.' Access is Denied.' And of course, very limited documentation to be found regarding this. I can simply script out the database tables and create them this way but I have several IDENTITY columns and when I try to do an insert (insert into @name select * from production..' + @name + ' <with correct syntax obviously>) I get errors due to the IDENTITY columns. Basically, to make a long story short what I am looking for is a solution to get around this problem (turn off IDENTITY, turn it back on, anything...really). My hands are tied unless my admin decides to get back to me sometime this month...

Thanks guys.

 

[SQLDenis]

back it up and restore it on the test server

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

SQL Server has a command that will allow you to insert data in to an indentity column.

SET IDENTITY_INSERT [!]TableName[/!] ON

[green]-- insert some records[/green]

SET IDENTITY_INSERT [!]TableName[/!] OFF



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ethorn10]

Thank you both for replying...however:

SQLDenis -- backup and restore is not an option as somehow my permissions are shot right now and like I said...my admin tends to ignore most everybody (or at least me).

gmmastros -- I read about the IDENTITY_INSERT option but the only way I can think to copy the data from db1 to db2 is in Query Analyzer using a cursor (I know, I know) to grab the names of the tables and then do an

exec('insert into ' + @name + ' select * from db2..' + @name + '')

and to the best of my knowledge, when using exec like that, it would ignore any "SET IDENTITY_INSERT ON" command I handed it, but I'm still new...

Thank you both and I will continue to check back.

 

[vongrunt]

So you put "SET IDENTITY_INSERT ON" into exec string

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

what about this...

exec('SET IDENTITY_INSERT ' + @name + ' ON insert into ' + @name + ' select * from db2..' + @name + 'SET IDENTITY_INSERT ' + @name + ' OFF')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ethorn10]

I haven't tried that yet, as I just assumed (my mistake) that it wouldn't allow that many statements in a single exec. I'll give it a go and let you guys know how it goes.

Thanks.

 

[ethorn10]

Ok guys...

Only a minor update. Adding the "SET IDENTITY_INSERT" piece to the exec string didn't cause it to choke...initially...instead it is now generating a new error (of course). This new error is stating that inserting a distinct value into an identity column must be done with IDENTITY_INSERT ON and a column list. Creating a dynamic column list for each table would be unbearable and this is making me wonder if my if statement (that I didn't show) is working properly (checking for an identity column for the given table before turning IDENTITY_INSERT ON, otherwise just insert).

I'll have to keep poking at it...any other ideas are welcome. Thanks guys.

 

[SQLDenis]

Here are 2 ways to find out if a table has an identity column
The first way is using the COLUMNPROPERTY function and the second way is using the OBJECTPROPERTY function
both methods will return 1 if the table has an identity column

USE northwind
GO

DECLARE @tableName VARCHAR(50)
SELECT @tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO


DECLARE @intObjectID INT
SELECT @intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ethorn10]

Thanks...but I was successfully getting whether or not the table contained an identity column (checking for 128 in the status column of the syscolumns table) and, as it turns out, I was also successfully checking that value and doing the appropriate insert statements.

So, I guess this means it isn't really possible without dynamically creating the column list in my insert statement...which would be incredibly painful at first guess.

Thanks to those who helped. Any additional comments welcome.