We have a production SQL Server 2005 database server with the production version of our application's database on it. I would like to be able to copy down the data contents of the production database to a development server for testing.
我们有一个生产SQL Server 2005数据库服务器,上面有我们应用程序数据库的生产版本。我希望能够将生产数据库的数据内容复制到开发服务器进行测试。
Several sites (and Microsoft's forums) suggest using the Backup/Restore options to copy databases from one server from another, but this solution is unworkable for several reasons (I don't have backup authority on our production database, I don't want to overwrite permissions on the development server, I don't want to overwrite structure changes on the development server, etc...)
有几个站点(和微软的论坛)建议使用备份/恢复选项从一台服务器复制数据库,但由于多种原因,这个解决方案不可行(我没有对我们的生产数据库的备份权限,我不想覆盖开发服务器上的权限,我不想覆盖开发服务器上的结构更改等...)
I've tried using the SQL Import/Export Wizard in SQL Server 2005, but it always reports primary key violations. How can I copy the contents of a database from the production server to development without using the "Backup/Restore" method?
我已尝试在SQL Server 2005中使用SQL导入/导出向导,但它始终报告主键冲突。如何在不使用“备份/恢复”方法的情况下将数据库的内容从生产服务器复制到开发中?
11 个解决方案
#1
3
Well without the proper rights it really becomes more tedious and less than ideal.
没有适当的权利,它真的变得更乏味,更不理想。
One way that I would recommend though is to drop all of your constraints and indexes and then add them again once the data has been imported/exported.
我建议的一种方法是删除所有约束和索引,然后在导入/导出数据后再次添加它们。
Not an elegant solution but it'll process really fast.
不是一个优雅的解决方案,但它的处理速度非常快。
EDIT: Another option is to create an SSIS package where you specifically dump the tables in an order that won't violate the constraints.
编辑:另一种选择是创建一个SSIS包,您可以按照不违反约束的顺序专门转储表。
#2
2
I often use SQL Data Compare (http://www.red-gate.com/products/sql_data_compare/index.htm) for this task: the synchronization scripts it writes will remove the relationships during the transfer and reapply them, but that is OK in most development cases. It works especially well with smaller databases or subsets of databases.
我经常使用SQL Data Compare(http://www.red-gate.com/products/sql_data_compare/index.htm)执行此任务:它编写的同步脚本将删除传输过程中的关系并重新应用它们,但这是大多数开发案例都可以。它适用于较小的数据库或数据库子集。
If your database is large, I would recommend finding someone with the keys to the kingdom. Doing an out of sequence backup could mess with the ability to restore the database from the primary backup (if they are doing partials during the week for example) by marking records backed up when they are only in your backup, so don't try to bypass that security if you are unsure why it is there.
如果您的数据库很大,我建议找一个带有王国钥匙的人。执行无序备份可能会破坏从主备份恢复数据库的能力(例如,如果他们在一周内做部分),通过标记备份时备份的记录,所以不要尝试如果您不确定它为何存在,请绕过该安全措施。
#3
2
Assuming that you can connect to both DB's from the same machine (which almost always you can - I do it with my production servers via a VPN).
假设您可以从同一台机器连接到两台数据库(几乎总是可以 - 我通过VPN与我的生产服务器一起工作)。
For each table
对于每张桌子
DELETE FROM devserv.dbo.tablename;
SET identity_insert [devserv.dbo.tablename] ON;
INSERT into devserv.dbo.tablename SELECT * from prodserv.dbo.tablename;
SET identity_insert [devname.dbo.tablename] OFF;
It is obviously worth noting that you will need to do this in a certain order if your tables have foreign key constraints.
显然值得注意的是,如果表具有外键约束,则需要按特定顺序执行此操作。
#4
1
I'd contact someone that does have access to backup the database. Permissions are usually there for a reason.
我会联系有权备份数据库的人。权限通常是有原因的。
#5
1
The import/ export wizard is notorious for this sort of thing, and actually has a bug that makes it even less useful in working out the dependencies (sorry, don't have the details to hand).
导入/导出向导因此类而臭名昭着,并且实际上有一个错误,使得它在计算依赖关系时更没用(抱歉,没有详细信息)。
SSIS does a much better job, but you'll have to add each table copy task by hand (in fact a datasource, copy task and data destination objects. It's a little tedious to set up (more than it should be), but a lot simpler than writing your own code.
SSIS做得更好,但是您必须手动添加每个表复制任务(实际上是数据源,复制任务和数据目标对象。设置(比应该更多)有点乏味,但是比编写自己的代码简单得多。
One tip: avoid generating an SSIS project with the import/ export wizard, thinking it will be easier to just tweak it. It generates something that most people would find unrecognisable, even with some SSIS experience!
一个提示:避免使用导入/导出向导生成SSIS项目,认为调整它会更容易。即使有一些SSIS经验,它也能产生大多数人无法辨认的东西!
#6
1
If you do not have backup permission on the production server, I guess this is because you are using a shared SQL Server from a webhoster. In this case, check if your webhoster provides the tool called myLittleBackup. It allows installing a db from one server to another in a few clicks...
如果您没有生产服务器上的备份权限,我想这是因为您正在使用来自webhoster的共享SQL Server。在这种情况下,请检查您的webhoster是否提供名为myLittleBackup的工具。它允许通过几次点击将数据库从一台服务器安装到另一台服务器......
#7
0
I might consider getting a backup as there will be one wether you run it or not (t least in theory a Prod DB is being backed up :) )
我可能会考虑获得一个备份,因为你会运行或不运行(至少在理论上正在备份Prod DB :))
Then just restore to a brand new database on your dev box so you dont conflict with anything or anyone else.
然后只需恢复到开发箱上的全新数据库,这样就不会与任何人或任何其他人发生冲突。
If you restore to a new DB you could also pull the tables and data across manually if you wanted and since you create the DB you give yourself rights and it's all ok. There's a number of other methods, all tedious.
如果你还原到一个新的数据库,你也可以手动拉动表和数据,如果你想要,你创建数据库就可以给自己权利,这一切都没问题。还有很多其他的方法,都很繁琐。
#8
0
It is obviously worth noting that you will need to do this in a certain order if your tables have foreign key constraints.
显然值得注意的是,如果表具有外键约束,则需要按特定顺序执行此操作。
#9
0
We just use the SQL Server Database Publishing Wizard at work.
我们只是在使用SQL Server数据库发布向导。
You would use this little utility to generate a T-SQL script that describes your production database (including all its data). Then connect to your dev server and run the generated script.
您可以使用此小实用程序生成描述生产数据库(包括其所有数据)的T-SQL脚本。然后连接到您的开发服务器并运行生成的脚本。
#10
0
If you have to avoid backup/restore this is what I would recommend (these steps assuming you don't want to maintain the old schema NAME, just the structure) -
如果你不得不避免备份/恢复这就是我的建议(这些步骤假设你不想维护旧架构NAME,只是结构) -
Download opendbdiff. Choose 'Compare' between source and (empty) destination. Choose sync. script tab and copy only the create table rows (without dbo.sysdiagrams tables etc.) paste into sql managment studio new query, delete all the schemas names appearing before the table names.
下载opendbdiff。选择源和(空)目标之间的“比较”。选择同步。脚本选项卡和复制只将create table行(不带dbo.sysdiagrams表等)粘贴到sql管理工作室的新查询中,删除表名前出现的所有模式名称。
Now you have the full structure including primary keys, identity etc. Next step - use sql server import and export data like you did before (make sure you choose edit mappings and choose destination schema as dbo etc.). Also make sure you tick drop and recreate destination table.
现在您拥有完整的结构,包括主键,身份等。下一步 - 使用sql server导入和导出数据,就像之前一样(确保选择编辑映射并选择目标模式为dbo等)。还要确保勾选并重新创建目标表。
#11
-1
On your Dev machine, setup a linked server to your production machine. Then just
在您的Dev计算机上,将链接服务器设置到生产计算机。然后就是
INSERT dev.db.dbo.table (fieldlist)
INSERT dev.db.dbo.table(fieldlist)
SELECT (fieldlist) from prod.db.dbo.table
来自prod.db.dbo.table的SELECT(fieldlist)
#1
3
Well without the proper rights it really becomes more tedious and less than ideal.
没有适当的权利,它真的变得更乏味,更不理想。
One way that I would recommend though is to drop all of your constraints and indexes and then add them again once the data has been imported/exported.
我建议的一种方法是删除所有约束和索引,然后在导入/导出数据后再次添加它们。
Not an elegant solution but it'll process really fast.
不是一个优雅的解决方案,但它的处理速度非常快。
EDIT: Another option is to create an SSIS package where you specifically dump the tables in an order that won't violate the constraints.
编辑:另一种选择是创建一个SSIS包,您可以按照不违反约束的顺序专门转储表。
#2
2
I often use SQL Data Compare (http://www.red-gate.com/products/sql_data_compare/index.htm) for this task: the synchronization scripts it writes will remove the relationships during the transfer and reapply them, but that is OK in most development cases. It works especially well with smaller databases or subsets of databases.
我经常使用SQL Data Compare(http://www.red-gate.com/products/sql_data_compare/index.htm)执行此任务:它编写的同步脚本将删除传输过程中的关系并重新应用它们,但这是大多数开发案例都可以。它适用于较小的数据库或数据库子集。
If your database is large, I would recommend finding someone with the keys to the kingdom. Doing an out of sequence backup could mess with the ability to restore the database from the primary backup (if they are doing partials during the week for example) by marking records backed up when they are only in your backup, so don't try to bypass that security if you are unsure why it is there.
如果您的数据库很大,我建议找一个带有王国钥匙的人。执行无序备份可能会破坏从主备份恢复数据库的能力(例如,如果他们在一周内做部分),通过标记备份时备份的记录,所以不要尝试如果您不确定它为何存在,请绕过该安全措施。
#3
2
Assuming that you can connect to both DB's from the same machine (which almost always you can - I do it with my production servers via a VPN).
假设您可以从同一台机器连接到两台数据库(几乎总是可以 - 我通过VPN与我的生产服务器一起工作)。
For each table
对于每张桌子
DELETE FROM devserv.dbo.tablename;
SET identity_insert [devserv.dbo.tablename] ON;
INSERT into devserv.dbo.tablename SELECT * from prodserv.dbo.tablename;
SET identity_insert [devname.dbo.tablename] OFF;
It is obviously worth noting that you will need to do this in a certain order if your tables have foreign key constraints.
显然值得注意的是,如果表具有外键约束,则需要按特定顺序执行此操作。
#4
1
I'd contact someone that does have access to backup the database. Permissions are usually there for a reason.
我会联系有权备份数据库的人。权限通常是有原因的。
#5
1
The import/ export wizard is notorious for this sort of thing, and actually has a bug that makes it even less useful in working out the dependencies (sorry, don't have the details to hand).
导入/导出向导因此类而臭名昭着,并且实际上有一个错误,使得它在计算依赖关系时更没用(抱歉,没有详细信息)。
SSIS does a much better job, but you'll have to add each table copy task by hand (in fact a datasource, copy task and data destination objects. It's a little tedious to set up (more than it should be), but a lot simpler than writing your own code.
SSIS做得更好,但是您必须手动添加每个表复制任务(实际上是数据源,复制任务和数据目标对象。设置(比应该更多)有点乏味,但是比编写自己的代码简单得多。
One tip: avoid generating an SSIS project with the import/ export wizard, thinking it will be easier to just tweak it. It generates something that most people would find unrecognisable, even with some SSIS experience!
一个提示:避免使用导入/导出向导生成SSIS项目,认为调整它会更容易。即使有一些SSIS经验,它也能产生大多数人无法辨认的东西!
#6
1
If you do not have backup permission on the production server, I guess this is because you are using a shared SQL Server from a webhoster. In this case, check if your webhoster provides the tool called myLittleBackup. It allows installing a db from one server to another in a few clicks...
如果您没有生产服务器上的备份权限,我想这是因为您正在使用来自webhoster的共享SQL Server。在这种情况下,请检查您的webhoster是否提供名为myLittleBackup的工具。它允许通过几次点击将数据库从一台服务器安装到另一台服务器......
#7
0
I might consider getting a backup as there will be one wether you run it or not (t least in theory a Prod DB is being backed up :) )
我可能会考虑获得一个备份,因为你会运行或不运行(至少在理论上正在备份Prod DB :))
Then just restore to a brand new database on your dev box so you dont conflict with anything or anyone else.
然后只需恢复到开发箱上的全新数据库,这样就不会与任何人或任何其他人发生冲突。
If you restore to a new DB you could also pull the tables and data across manually if you wanted and since you create the DB you give yourself rights and it's all ok. There's a number of other methods, all tedious.
如果你还原到一个新的数据库,你也可以手动拉动表和数据,如果你想要,你创建数据库就可以给自己权利,这一切都没问题。还有很多其他的方法,都很繁琐。
#8
0
It is obviously worth noting that you will need to do this in a certain order if your tables have foreign key constraints.
显然值得注意的是,如果表具有外键约束,则需要按特定顺序执行此操作。
#9
0
We just use the SQL Server Database Publishing Wizard at work.
我们只是在使用SQL Server数据库发布向导。
You would use this little utility to generate a T-SQL script that describes your production database (including all its data). Then connect to your dev server and run the generated script.
您可以使用此小实用程序生成描述生产数据库(包括其所有数据)的T-SQL脚本。然后连接到您的开发服务器并运行生成的脚本。
#10
0
If you have to avoid backup/restore this is what I would recommend (these steps assuming you don't want to maintain the old schema NAME, just the structure) -
如果你不得不避免备份/恢复这就是我的建议(这些步骤假设你不想维护旧架构NAME,只是结构) -
Download opendbdiff. Choose 'Compare' between source and (empty) destination. Choose sync. script tab and copy only the create table rows (without dbo.sysdiagrams tables etc.) paste into sql managment studio new query, delete all the schemas names appearing before the table names.
下载opendbdiff。选择源和(空)目标之间的“比较”。选择同步。脚本选项卡和复制只将create table行(不带dbo.sysdiagrams表等)粘贴到sql管理工作室的新查询中,删除表名前出现的所有模式名称。
Now you have the full structure including primary keys, identity etc. Next step - use sql server import and export data like you did before (make sure you choose edit mappings and choose destination schema as dbo etc.). Also make sure you tick drop and recreate destination table.
现在您拥有完整的结构,包括主键,身份等。下一步 - 使用sql server导入和导出数据,就像之前一样(确保选择编辑映射并选择目标模式为dbo等)。还要确保勾选并重新创建目标表。
#11
-1
On your Dev machine, setup a linked server to your production machine. Then just
在您的Dev计算机上,将链接服务器设置到生产计算机。然后就是
INSERT dev.db.dbo.table (fieldlist)
INSERT dev.db.dbo.table(fieldlist)
SELECT (fieldlist) from prod.db.dbo.table
来自prod.db.dbo.table的SELECT(fieldlist)