Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-08 : 09:07:51
|
| Daniel writes "Hi,I normally work with Oracle but we have 1 sql server.I want to make a copy of a database on this server and put it on the same server. I will use this db to test some triggers and perform some action with our oracle database. I don't want to use the real data in case something happens and we lost important data.How would I do that.Excuse my english but I am french canadian. Thank you in advance" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-04-08 : 09:11:52
|
You speak English better than most Americans.  Look in SQL Server Books Online under "Data Transformation Services" or DTS. This will let you connect to your Oracle database and transfer the data directly to SQL Server. It's the easiest and probably best way to accomplish the task.One word of advice though, Oracle triggers/procedures are VASTLY different from SQL Server triggers. The code bases are similar, but the methods are radically different. A trigger written to work on Oracle will either not work at all, or be unbelieveably inefficient in SQL Server, and will not provide any useful measure of performance. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-09 : 17:47:47
|
| Oh, now here I thought the question was how to make a copy of the SQL Server database... If that was your need, then there are a couple of ways to do it.If you can afford to disable the source SQL Server database, then look into using the sp_detach_db and sp_attach_db commands. Basically you would detach the database, make copies with new names of the .mdf, .ldf and .ndf (if they exist) files. Then re-attach the original database. And then attach the copy.But perhaps an easier way is to just do a SQL backup of the database, and then do a RESTORE. In the RESTORE, use the options to change the database name and MOVE the files to new locations.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-13 : 15:44:23
|
quote:
Originally posted by robvolk
You speak English better than most Americans. Look in SQL Server Books Online under "Data Transformation Services" or DTS. This will let you connect to your Oracle database and transfer the data directly to SQL Server. It's the easiest and probably best way to accomplish the task.One word of advice though, Oracle triggers/procedures are VASTLY different from SQL Server triggers. The code bases are similar, but the methods are radically different. A trigger written to work on Oracle will either not work at all, or be unbelieveably inefficient in SQL Server, and will not provide any useful measure of performance.
I don't think he wants to transfer Oracle DB to SQL server...you can't do that easily through DTS anyhow, you can only copy the table definitions and data, it doesn't typically copy triggers, permissions, etc. Unless I am just an unskilled DTS user.I would use AjrnMark's idea of just doing a RESTORE of the last good backup you have of your production database into a new database you create. |
|
|
|
|
|
|
|
|
test copy of a database