July 20, 2009 at 11:01 am
Hey All,
At the company I work for, each employee has their own copy of our database. This is so they can insert / delete data and not affect other users.
When they want to recreate their database to the original state, we need to go through a process to drop all of the objects in their local database, recreate all these objects by copying what is in our primary database, and then copy all of the data from the primary database into their own.
The first two steps I have done already, but Im running into some trouble with how to define a generic script that moves all of the data from one database to another. Keeping in mind that these database have FK contrainsts and unique columns.
This is the script I have so far:
DECLARE
@sql VARCHAR(500),
@tableName VARCHAR(128),
@foreignKeyName VARCHAR(128),
@schema varchar(128)
-- A list of all foreign keys and table names
DECLARE foreignKeyCursor CURSOR
FOR SELECT
tbl.name AS tbl_name,
sch.name AS sch_name
FROM
sys.tables tbl
INNER JOIN sys.schemas sch
ON tbl.schema_id = sch.schema_id
OPEN foreignKeyCursor
FETCH NEXT FROM foreignKeyCursor
INTO @tableName, @schema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'ALTER TABLE ['
+ @schema + '].['
+ @tableName + '] NOCHECK CONSTRAINT ALL'
EXECUTE(@sql)
FETCH NEXT FROM foreignKeyCursor
INTO @tableName, @schema
END
CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
-- Move all of the data from project1 into the local database
DECLARE@testIdnumeric(18,0)
DECLARE foreignKeyCursor CURSOR
FOR SELECT
tbl.name AS tbl_name,
sch.name AS sch_name
FROM
sys.tables tbl
INNER JOIN sys.schemas sch
ON tbl.schema_id = sch.schema_id
OPEN foreignKeyCursor
FETCH NEXT FROM foreignKeyCursor
INTO @tableName, @schema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
if ( SELECT IDENT_CURRENT('[' + @schema + '].[' + @tableName + ']')) is not NULL
begin
SET @sql = 'set IDENTITY_INSERT [' + @schema + '].[' + @tableName + '] ON;'
+ 'delete from [' + @schema + '].[' + @tableName + ']' + '; '
+ 'insert into [' + @schema + '].[' + @tableName + '] ('
+ 'select * from [project1].[' + @schema + '].[' + @tableName + ']; '
+ 'set IDENTITY_INSERT [' + @schema + '].[' + @tableName + '] off;'
end
else
begin
SET @sql = 'delete from [' + @schema + '].[' + @tableName + ']' + '; '
+ 'insert into [' + @schema + '].[' + @tableName + '] '
+ 'select * from [project1].[' + @schema + '].[' + @tableName + ']; '
end
print @sql
EXECUTE(@sql)
FETCH NEXT FROM foreignKeyCursor
INTO @tableName, @schema
END
CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
-- Finally turn the constraints back on
DECLARE foreignKeyCursor CURSOR
FOR SELECT
tbl.name AS tbl_name,
sch.name AS sch_name
FROM
sys.tables tbl
INNER JOIN sys.schemas sch
ON tbl.schema_id = sch.schema_id
OPEN foreignKeyCursor
FETCH NEXT FROM foreignKeyCursor
INTO @tableName, @schema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'ALTER TABLE ['
+ @schema + '].['
+ @tableName + '] CHECK CONSTRAINT ALL'
EXECUTE(@sql)
FETCH NEXT FROM foreignKeyCursor
INTO @tableName, @schema
END
CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
This is doing 3 things, disable all of the constraints. Move all of the data. Enable all of the constraints. The issue I'm running into now is the portion of the code which turns IDENTITY INSERT ON doesn't work because it doesn't specify the columns.
So, does anyone have any advice on how to do this? Is there an easier way I'm not thinking of? It seems as though something as simple as, I have two identical database and want to copy the data from one to the other, should be easy through a script.
Thanks,
-Dave
July 20, 2009 at 11:16 am
Try seaching for a script with the sp_MSforEachTable. It is an undocumented stored proc that will loop through all the tables in the database. If you can't find anything, I might have some examples but it will take me a while to find them.
July 20, 2009 at 11:37 am
I'd really recommend you look at one of the compare tools out there. I work for Red Gate, but ApexSQL and other companies have similar tools.
It's not that this is a hard problem, but it has a lot of moving parts to it. Places you can make a mistake, and places where you will need to test/validate it. your time should be worth more than a few hundred dollars to do this, especially if you have to iterate through versions of this.
The compare tools are well tested and work great. I don't think a lot of third party tools necessarily fit many situations, but compare tools do for sure.
July 20, 2009 at 11:43 am
I've thought about that same thing, and we're currently use ApexSQL DataDiff for doing just that.
The issue with this is that now it's a manual process that a DBA needs to run. In the end, I'd like to have a script I provide to the other employees they can use to recreate their local databases without requiring a DBA.
It'd be fantastic if one of these tools had a feature which let me accomplish what I'm looking to do, but I haven't seen anything thus far.
-Dave
July 20, 2009 at 11:47 am
Why no just use BACKUP and RESTORE to make a copy of the database?
It will create an exact copy of all database objects and files.
July 20, 2009 at 11:57 am
BACKUP and RESTORE would be would perfectly, something along the lines:
use master
backup database project1 to DISK='C:\DatabaseBackup.bak'
restore database project1_dcarrigg from DISK='C:\DatabaseBackup.bak'
The problem (as you might have already noticed) is that each local local database is running on the same database server, and is named differently. This causes the RESTORE to print the error: "The backup set holds a backup of a database other than the existing 'project1_dcarrigg' database."
Is it possible that there's a way to restore from a backup like this, where the database isn't named the same as what is in the backup?
-Dave
July 20, 2009 at 12:01 pm
ApexDiff should allow you do generate a script you can give to people. SQLCompare does.
The restore should allow you to restore to a different name and path with the MOVE option.
July 20, 2009 at 12:04 pm
You could create an SSIS package that copies database objects then create a job that runs the package. The users can either run the job or you could create an executable that they can double click.
July 20, 2009 at 12:08 pm
Fantastic. I think using the BACKUP and RESTORE method is going to work, otherwise I'll look into creating an SSIS package. You guys have been a big help.
Thanks,
-Dave
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply