0

I'm looking for a way to duplicate an existing database, tables and data onto a different development machine. Using SQL server 2008;

I have several tools i usually use for duplicating databases or i just restore a database dump, but i cannot use any of these. I have no direct access to the database machine i can logon via SQL enterprise manager and run queries stored procedures etc. But i have no access to the file system so i'm unable to create or copy a database dump. I cannot install or connect any tools to the database as i have to logon via a remote desktop. I also only have access to copy text files on and off the network.

I would like to run a script to generate a script to create the tables, views stored procedures etc and a script to populate the tables with some data.

I need to create a test environment so i can run tests and develop new features without affecting the live DB.

Jammy
  • 779
  • 2
  • 13
  • 26
  • 1
    [Script entire database SQL-Server](https://stackoverflow.com/questions/1162339/script-entire-database-sql-server#1162348) – Lukasz Szozda Jul 11 '18 at 14:40
  • 1
    Dumps are just SQL scripts which means they can be created remotely but are *extremely* slow to create and execute. If you have access through SSMS you *have* remote access. – Panagiotis Kanavos Jul 11 '18 at 14:40
  • 2
    Seems that the real problem here is that you don't have access to do your job. The real solution would be to get the access you need. A backup and restore is by far the best way to do this kind of thing. You could script the entire database but that is just way slower. – Sean Lange Jul 11 '18 at 14:41
  • 3
    Forget dumps. Just ask the DBA for a backup and restore it on your machine. Creating one will only take 1/100000 the time of scripting every single INSERT statement. If the DBA complains, threaten him that you'll create a dump, ie full database script, taking CPU, IO, network bandwidth. For 1000000x the time it would take him to generate the backup – Panagiotis Kanavos Jul 11 '18 at 14:41
  • DB structure is easy: Management Studio, right-click, Tasks -> Generate Scripts. Copying that can be done even over an RDP connection. Doing data import/export that way is feasible only with a handful of data (but if the machine is directly accessible through SQL, which isn't clear from your question, the data import/export wizard can copy rows with typically admirable speed). – Jeroen Mostert Jul 11 '18 at 14:43
  • 1
    Another vote for backup and restore. This is like reinventing the wheel so you can walk to the kitchen. If the DBA won't give you rights, they should be able to easily configure this for you. If not, I'd ask why your company wants you to spend a few weeks on a project that should take an hour. – Jacob H Jul 11 '18 at 14:43
  • @Jammy added bonus - backups can be compressed, thus reducing space and cirtically, disk IO. Backups can be made online without taking locks on the *tables*. Scripting of course would require locks for the entire operation's time to prevent inconsistent data changes from appearing in the scripted commands – Panagiotis Kanavos Jul 11 '18 at 14:45
  • @Jammy that's not a SQL Server problem btw. MySQL has the exact same issues, only they cover it up by talking about "logical" and "physical" backups. Fake and real would be more appropriate. Try taking a "logical" backup of a 10GB database, which is considered small for SQL Server - that's the database limit for the free Express edition – Panagiotis Kanavos Jul 11 '18 at 14:48
  • @Jammy in MySQL you can do the same with the `mysqlbackup` command which is part of the Enterprise edition. Instead of paying for the license, hosters download and build the source of the Enterprise edition. Just a hint – Panagiotis Kanavos Jul 11 '18 at 14:51
  • If you are absolutely stuck doing it yourself, take a look at the the SQL Import Export tool that comes with any SQL Server install (include local). You can copy all or any tables and views, and doesn't require any special privileges on the source system. – BoCoKeith Jul 11 '18 at 15:28
  • Writing it yourself in code is going to be painful as others have noted. – BoCoKeith Jul 11 '18 at 15:29
  • Thanks for the feedback, I totally agree the DBA could do this in a fraction of the time but its likely to take days before he'll even think about it and even then he wont do it, It was the first thing i asked. I've used the SQL generate scripts to make a blank DB but just wondered if there was any alternative. I'm a contractor so dont have much say in access to things. This is by far the most stressful job I've worked on. So secure you cant even do your job. – Jammy Jul 11 '18 at 16:39

1 Answers1

0

Assuming you have access to the database via SSMS, this is the approach I would take, though it could be time consuming:

  1. Right click on the Database
  2. Hover 'Tasks'
  3. Hover 'Generate Scripts' and complete the process, it's self explanatory.

And from there it will generate a script for the Database architecture. This is the quick portion.

Next download Visual Studio and get the Integration Services add on. Using SSIS packages you'll be able to select all data from each source table/database and then insert the data into the destination table/database. Hope this helps you get on the right path.

DarthTommy
  • 419
  • 2
  • 10