-9

Using a script (however without using data import/export, without using backup restore, without using database copy) how can I copy complete tables (schema and data) from one database on the same server to another database on the same server?

I've already tried to do what I've italicized above; however, due to user permissions etc, I was unable to use those methods.

Perhaps this will work:

 SELECT *
 INTO newdb.dbo.newtable
 FROM olddb.dbo.oldtable

But I would like to apply this to every table (around 100 tables).

Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • 1
    Why don't you GET the permissions to take a backup and restore it? Gobs and gobs and gobs simpler than any of the other approaches. – Aaron Bertrand Jul 03 '13 at 18:11
  • @AaronBertrand if it were simpler, i would definitely do that – Alex Gordon Jul 03 '13 at 18:11
  • Well how is it possible that import/export data won't work while select into would? Either you have permissions to write to the other database, or you don't. – Aaron Bertrand Jul 03 '13 at 18:12
  • @AaronBertrand sql.service and sql agent do the import/export, whereas the select into is done by something else – Alex Gordon Jul 03 '13 at 18:13
  • I don't really get your permissions issue. You can't use the "Data Import/Export"?. Can you right click the database and go to "Tasks->Generate scripts"? – Lamak Jul 03 '13 at 18:14
  • You can run the data import/export wizard or other scripting tools in Object Explorer to generate a script, which can then be "done by something else" whatever that means. – Aaron Bertrand Jul 03 '13 at 18:14
  • Also can you describe what "done by something else" means? Are you saying that the person who would run SELECT INTO can't run the wizard? – Aaron Bertrand Jul 03 '13 at 18:18
  • @AaronBertrand that is correct. i dont think its a person doing it, i think it's the service doing it – Alex Gordon Jul 03 '13 at 18:18
  • @AaronBertrand the only error im getting from the event viewer is Package "CDW_KSLDB252_KSLDB252_6" failed. there are no other details about the error. – Alex Gordon Jul 03 '13 at 18:19
  • @AaronBertrand this is a clean install, so this is probably a known issue; however, i've not been able to track down the source – Alex Gordon Jul 03 '13 at 18:21
  • So right-click the database, tasks > generate scripts, select your tables, when you get to scripting options, click Advanced and make sure "Types of data to script" is set to schema and data. But seriously, get the proper permissions to run a backup and restore. – Aaron Bertrand Jul 03 '13 at 18:22
  • 2
    Also, instead of asking "how do I do this in some cumbersome way because the logical way is broken" why not ask a completely different question, like "how do I fix this thing that is broken"? When I get a flat tire I don't call AAA and ask them to drive me to work, I ask them to come and fix my flat tire. – Aaron Bertrand Jul 03 '13 at 18:23
  • @AaronBertrand wouldnt that be crazy if i have millions of rows of data? – Alex Gordon Jul 03 '13 at 18:23
  • 2
    Probably, which is why you should FIX THE ACTUAL PROBLEM instead of desperately seeking inane workarounds. – Aaron Bertrand Jul 03 '13 at 18:24
  • @AaronBertrand when running the copy database wizard, which accounts does it use to perform the operation? – Alex Gordon Jul 03 '13 at 18:30
  • *sigh* The second and third steps of the copy database wizard ask you for credentials, so it depends on what your answers were. – Aaron Bertrand Jul 03 '13 at 18:32
  • sure, but what about everything else like the sql.server and sqlserveragent etc? there are other operations that are carried out by other users, arent there? – Alex Gordon Jul 03 '13 at 18:34
  • I don't even understand what you're asking. – Aaron Bertrand Jul 03 '13 at 18:35
  • @АртёмЦарионов when you restore a database or copy a database it uses your credentials. It does not use SQL agent's credentials. If I'm understanding you right. You either have permissions or you don't. – Zane Jul 03 '13 at 18:36
  • @Zane thank you. im a sysadmin. i seem to have full permissions, yet i get an error the job failed. check the event log on the destination server for details. the event log just shows "error with packagename" there are no details associated with it – Alex Gordon Jul 03 '13 at 18:40
  • @zane perhaps the source database needs to go offline to be able to do copy database? – Alex Gordon Jul 03 '13 at 18:41
  • And why does a simple `BACKUP` and then `RESTORE ... WITH MOVE` not work for you? If you're a sysadmin then it shouldn't be a permissions problem and you shouldn't even be bothering with all of these hokey, cumbersome workarounds that you can't sort out either. – Aaron Bertrand Jul 03 '13 at 18:44
  • @AaronBertrand thanks! i just crafted my own solution :) seems like its even faster than the copy wizard – Alex Gordon Jul 03 '13 at 18:58
  • @АртёмЦарионов the error doesn't indicate that you don't have permissions remove that from the question. chances are you're choosing the "attach/detach" method and, if the db is in use, that will fail. – swasheck Jul 03 '13 at 18:59
  • @swasheck nope i was using copy wizard – Alex Gordon Jul 03 '13 at 19:02
  • 1
    Yes, and the copy wizard offers two options: detach/attach or SMO. I'm guessing based on this and your question about permissions that you click Next > Next > Next way too quickly. – Aaron Bertrand Jul 03 '13 at 19:03
  • @AaronBertrand sorry i used the SMO, not attach/detach (since the DB is in use ) – Alex Gordon Jul 03 '13 at 19:05
  • sorry about bothering everyone. this must be a bug with the 2012 version. – Alex Gordon Jul 03 '13 at 19:10
  • Bug? What on earth are you talking about? – Aaron Bertrand Jul 03 '13 at 19:11
  • @АртёмЦарионов you should open a connect item for this bug. – swasheck Jul 03 '13 at 19:12
  • @swasheck i mean it's a bug with sql server 2012. the fact that even though i have full permissions, i am getting an error having no detailed info – Alex Gordon Jul 03 '13 at 19:13
  • @АртёмЦарионов is there any remote possibility that it's operator error? – Kermit Jul 03 '13 at 19:14
  • 2
    @АртёмЦарионов right, so go to [Connect](http://connect.microsoft.com/SQLServer/feedback/) and post some feedback for this bug and see what they say about it. – swasheck Jul 03 '13 at 19:14
  • @swasheck ill add it to the list of things to do lol – Alex Gordon Jul 03 '13 at 19:15
  • I suspect you're just not looking in the right place for the detailed info. e.g. go to SQL Server Agent > Jobs, find the one with that package name, right-click, View History, then highlight the step and look in the lower pane. This isn't a bug, it's just a knowledge gap. That's what your question should have been asking about, not "how can I ignore the fact that there is an error and do this in some silly, cumbersome way." I still don't understand why it was so hard to convince you that backup/restore ***IS*** the right way to do this. – Aaron Bertrand Jul 03 '13 at 19:16
  • @AaronBertrand definitely learned something. thank you for teaching me. i got this Executed as user: ...\SQL.Service. Started: 11:17:08 AM Finished: 11:17:21 AM Elapsed: 13.016 seconds. The package execution failed. The step failed. – Alex Gordon Jul 03 '13 at 19:20
  • @АртёмЦарионов Please create a new question. – Kermit Jul 03 '13 at 19:23

3 Answers3

8

If you're truly a sysadmin, I don't understand what's wrong with backup and restore.

BACKUP DATABASE <DatabaseName, sysname, DatabaseName>
TO DISK=N'<backup_location, varchar, BackupLocation>.bak'
WITH INIT, FORMAT, COMPRESSION, COPY_ONLY

RESTORE DATABASE <NewDatabaseName, sysname, NewDatabaseName>
FROM DISK = N'<backup_location, varchar, BackupLocation>\
    <DatabaseName, sysname, DatabaseName>.bak'
WITH 
    MOVE '<DataFileName, sysname, DataFileName>' TO '<DataMDFPath, nvarchar(600), DataMDFPath>',
    MOVE '<LogFilePath, sysname, LogFilePath>' TO '<LogLDFPath, nvarchar(600), LogLDFPath>',
    REPLACE;

Here, see, I even templated it for you.

swasheck
  • 4,644
  • 2
  • 29
  • 56
1

If you have SSMS, you can right-click the database and select "Script Database As" -> "Create To". This will generate DDL scripts for all the objects you want (tables, indices, views, etc)

After you run those, use:

SELECT 'INSERT newdb..'+name+' SELECT * FROM olddb..'+name FROM sys.tables

To generate the copy commands. If you have computed columns, you'll need to join the sys.columns table to filter those out. If you have multiple schemas, you'll need to specify them. You'll also need to sort these DML commands based on your foreign key hierarchy. With only 100 tables, it's feasible to do that manually.

Anon
  • 10,660
  • 1
  • 29
  • 31
-4

Are you using Microsoft SQL Server Management Studio? If so...

You can use the Script Table As... Option and select the New Query Editor Form under the CREATE TO option.

From there you should just be able to change either the Table Name, or the targeted Database.

In conjunction with this you can use the information found here: https://stackoverflow.com/a/7515236/2543917 in order to move over your data.

If you need some more actual code examples then let me know and I can provide.

Community
  • 1
  • 1
TrustedInSci
  • 148
  • 5
  • 1
    Pretty cumbersome to do that for 100 tables, though. – Aaron Bertrand Jul 03 '13 at 18:17
  • True, but it is one way to do it, if you can't utilize the methods described in the original post. Then again I don't know why one would have permission to Create Tables, but not create Backups... – TrustedInSci Jul 03 '13 at 18:22
  • @TrustedInSci Creating tables require DDL permissions in the database. Creating backups require server level permissions to the file system. There is a difference – tsells Jul 04 '13 at 02:01