SQL Server Performance Forum – Threads Archive

Importing or exporting live production database

I want to import the tables, views, and stored procedures from one database to another on the same server. There are about 60 users in the database as we speak. The reason for doing this is to compile some reports and perform some additional analysis – and play, without impacting any of the "real" data. Are there any issues in doing this with the import/export wizard in the Enterprise Manager? Will it disrupt my users in any way other than any load it puts on the server to move the data? Thanks. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

Do you want to actually make a new copy of existing database?

yes good point by mmarovic – if you want a replica, use backup/restore, it’s much much easier. Tom Pullen
DBA, Oxfam GB

I want to make a backup/restore of an existing database – back onto the same server with a different name – same user, same key fields, same identity integers, same indexes, etc. Basically, a duplicate with the same data. So backup/restore is a better method. Here is the challenge, I am remote from this server and have limited permissions except on my two particular databases. But that might work. So then, the question is, can I do this with a live, in production database, and not run into problems? Thanks. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

permissions may be an issue – can’t remember off the top of my head what you need to restore a database cos I always do with with sysadmin rights. you would of course need to ensure that you use RESTORE dbNEWname WITH MOVE (parameters), REPLACE, etc. Need to ensure there are no users in the target database (if you’re over-writing your existing one). Tom Pullen
DBA, Oxfam GB

The same user is the owner for each database. There will be no users in the 2nd database (where I am copying too) as it is just a playground for me. So, to be very specific, are you saying that I can do the backup from the live, running, currently active with 60 users, database – without impacting them?? Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

<blockquote id="quote">quote:<hr height="1" noshade id="quote">are you saying that I can do the backup from the live, running, currently active with 60 users, database – without impacting them??<hr height="1" noshade id="quote"></blockquote id="quote">I guess your live … and so on database has a recent backup <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

But do you realize that my database is in production? The database is, to the best of my knowledge, backed up by corporate every evening. However, I want to be able to run this backup – sometimes 1-3 times a day… Is this not recommended or do you ask about my backup as a standard precaution and I shouldn’t have a problem. I’m not getting the warm-fuzzies because no one seems to be able – or want to – indicate whether what I am asking is fairly straight-forward. So, assuming I have a backup – assuming, all things working as they should – assuming the stars are aligned and that I have appropriate permissions and that I have the latest patches, updates, virus protection, etc. – and no hardware failures, earthquakes, spilled coffee, or other natural disaster strikes… Can I:
Copy a database that is in production named, DB1 on a server named, server1, with a database owner user named, user1
to a database named, DB2 on the same server, server 1, with the same database owner user named, user1 If so, is the backup/restore utility the recommended method, or
is the import/export wizard the recommended method, or
some other option. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

You will need to secure the following server role to do what you need to do: Backup Administrator You can then backup/restore the database with a different name. The database will at that point have the exact same everything, including users if it was restored on the same server. When you RESTORE, you will need to use the WITH MOVE option which is explained quite well in Books Online. It’s the easiest and most recommended. The import/export won’t do a great job if you have a lot of relationships. I would remove the other logins from the database if I were you. There’s no sense in taking chances of them accidentally using this database while it’s out there. Hopefully, the environment is controlled enough this wouldn’t happen, but since you’re using the same server for both, I’m kind of doubting it. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Sorry cbtoolkit, I thought it was one-time task, just to create copy of db on the same server. My point was that backup/restore is much more straight forward as Derrick and Thomas explained. To be sure I got it right: Do you want to establish process to make this replica regurarly or you want to be able to do it on demand? For regular process another possibility is log shipping even though it is on the same server. I believe it is possible to establish "log shipping on demand (but at least once a day)" on the same server, but I don’t have experience with it. If it is possible and not too complex, the advantage would be that only changes from last full/transaction log backup restore would have to be done each time you refresh data. Satya or anyone experienced in log shipping, what do you think about the idea?

Backup/restore to a seperate database during the working day WILL impact your users. Backup and restore will saturate your disk subsystem while it’s running and will slow down users’ I/O [and therefore queries] etc. But it will only be slowness – they won’t be stopped. However they will also be affected if you use DTS, transfer objects etc – there will still be a large amount of I/O competing with them and their work. Have you ever thought about getting a development server (seperate?) Might be an idea. Even if it’s just a PC with large IDE disks.
Tom Pullen
DBA, Oxfam GB

I support Mmarovic idea of log shipping which will be ideal in this situation, where the users against secondary server will have only contention during log restores. Search under this website for Brad’s articles on Log shipping. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also, I assumed your itention was just to select data, not to insert/update/delete.

I have been reading up on log shipping, backup/restore, etc. Thanks for everyone’s help on this. Actually, it is both a one-time thing (I am going to run numbers today and want to play with the data) but I anticipate that this can happen very frequently over the next month or so as we both develop and roll-out changes very rapidly. After this first month, we will have standard days to roll out updates and a much more controlled environment. Matthew Moran
The IT Career Builder’s Toolkit
http://www.cbtoolkit.com

]]>