February 13, 2008 at 12:49 am
Hi,
I'm having a production database on whose data i don't wish to make any modifications.
I have created a new DB with tables using the scripts from my production database. Now i wish to copy the data from production to Fresh DB.
Can any one advise on me the steps to follow.
Thanks
PL.Seenivasan
February 13, 2008 at 1:13 am
hi
i would have restored a full backup of the production database as the test database
dragos
February 13, 2008 at 6:01 am
Definitely a backup and a restore is the much easier route. All the data, all the structure, all the statistics, just as they are in production. Done.
However, you can create an extract and load using SSIS (SQL Server Integration Services). You can also simply do a series of INSERT SELECT statements between the databases.
INSERT INTO db.schema.table
(cola,colb)
SELECT cola,colb
FROM db2.schema.table
But that's going to be a lot of work.
You could also set up snapshot replication, create a mirror... Ooh, lots of choices. I'd stick with the backup & restore as a first choice or the SSIS package as a second.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2008 at 7:46 am
Backup/restore is my choice.
There's a copy database wizard, but it's been flaky for me.
February 14, 2008 at 4:31 am
Now that you have all your tables in place, why not simply 'Import Data' using the Import/Export Wizard... very convenient and has always worked for me 😎 :hehe:
February 16, 2008 at 6:38 pm
The way of backup/restore is a better way to create your Fresh DB. After it is created, if you would like to refresh data from your production DB to your Fresh DB often, you can create a DTS package to import data from production DB to Fresh DB, but you need to access the production DB from the Fresh DB, i.e., if your Fresh DB is on your laptop, you should be able to access your production DB on a server from your laptop.
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
February 16, 2008 at 6:46 pm
OK, not to jump on the Backup/Restore bandwagon, but it is the best option and the easiest option.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 16, 2008 at 8:49 pm
Especially since a transfer data task may not insert the data into the tables in a way that doesn't violate any referential integrity constraints (you DO have them don't you? 😀 )
Backup / Restore 🙂
February 18, 2008 at 9:01 pm
Hi All,
Thank you for all the valuable guidances from you.:)
February 20, 2008 at 7:15 am
Also, if you have Red Gate software, it will compare two databases and create scripts to synchronise one to the other, taking referential integrity into account. But this is 'by the way' - backup/restore is your best bet.
March 24, 2008 at 8:42 am
hi quick question when you use the import\export data wizard does that lock the source tables?
March 24, 2008 at 9:10 am
I think it puts a shared lock on the table. As an example. Select query in my source table takes about 13 secs but when I used import/export wizard to transfer from this source table it took 35 secs for same select query to run on the source table while the import/export wizard was still transferring from this source table to destination table.
March 26, 2008 at 7:05 am
ok thanks!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply