Copy a tables to another database (Flashdisk)
-
Hi I'd like to copy a couple of tables with all field information (primary keys, constraints etc) from SQL server 2008 at work to a pc at home running same SQL server. I dont have any sort of link to the work server from home, so I need to somehow export the tables and save them on a flashdisk, then import them to the other database at home. How can I do this? Tks Richard
-
Hi I'd like to copy a couple of tables with all field information (primary keys, constraints etc) from SQL server 2008 at work to a pc at home running same SQL server. I dont have any sort of link to the work server from home, so I need to somehow export the tables and save them on a flashdisk, then import them to the other database at home. How can I do this? Tks Richard
Easiest way is to right-click the table and then select 'Script Table As', 'Create To...' and then select whichever is the easiest way for you to accomplish your task. You can then run that script on another database. You can also use the Database Export Wizard in VS to accomplish a similar task and it will, optionally, get the table data as well. This is found on the Server Explorer tab. Right click on a db connection and select 'Publish to provider...' and follow the prompts.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Easiest way is to right-click the table and then select 'Script Table As', 'Create To...' and then select whichever is the easiest way for you to accomplish your task. You can then run that script on another database. You can also use the Database Export Wizard in VS to accomplish a similar task and it will, optionally, get the table data as well. This is found on the Server Explorer tab. Right click on a db connection and select 'Publish to provider...' and follow the prompts.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
Thank Marc Tried the 'Script Table' .. 'Create To' and this looks good.. I would like the data as well though, when you refer to VS do you mean Visual Studio? I looked at Database Explorer in VS, and tried to add a connection, using SQL Authentication, browsed to (mmmm what I think is the path to the database) and get error You dont have permission... Am I on the right track here? How can I tell from SQL management Studio what the path is to the actual database ( I battled with the SQL server installation, and I think I have more than one instance installed)
-
Thank Marc Tried the 'Script Table' .. 'Create To' and this looks good.. I would like the data as well though, when you refer to VS do you mean Visual Studio? I looked at Database Explorer in VS, and tried to add a connection, using SQL Authentication, browsed to (mmmm what I think is the path to the database) and get error You dont have permission... Am I on the right track here? How can I tell from SQL management Studio what the path is to the actual database ( I battled with the SQL server installation, and I think I have more than one instance installed)
It's 'Mark' spelt the correct way: with a 'K'. Yes, Visual Studio or you can download a stand-alon version from here[^]. Try using Windows Authentication. To get the path to a database right-click on the database and select 'Properties', then select 'Files' in the dialog.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
It's 'Mark' spelt the correct way: with a 'K'. Yes, Visual Studio or you can download a stand-alon version from here[^]. Try using Windows Authentication. To get the path to a database right-click on the database and select 'Properties', then select 'Files' in the dialog.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
Sorry Mark - Dunno where I got the 'c' from. Using Visual Studio, tried Windows, and SQL authentication??? If I open SQL management studio, I connect using sa with no password, but same does not happen with VS???? What is confusing is that if I click Properties on the top level item in the SQL Server Management studio, the path shows as below. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL Yet I have found two two .mdf database files both of which show in management studio a) C:\1\SQL2008\simmstock.mdf b) C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\vektron.mdf I know I battled with clearing the sa password, so not sure if I am browsing to the correct databse file. How can I check the actual database path specific to the vektron database from management studio, since if you right click the database, there is no path shown (You only see a path if you click properties on very top of the tree (Server) and not the individual databases under the databases folder?
-
Hi I'd like to copy a couple of tables with all field information (primary keys, constraints etc) from SQL server 2008 at work to a pc at home running same SQL server. I dont have any sort of link to the work server from home, so I need to somehow export the tables and save them on a flashdisk, then import them to the other database at home. How can I do this? Tks Richard
How about this ... On your work server, 1) create a DB 2) Issue some select into commands to copy the tables from to 3) Do a full backup of small DB and copy that BAK file to a Flashdisk 4) Restore the DB from the flashdisk to your home PC. You will get both the schema and the data in one neat package and you can easily make this a very repeatable process to bring "fresh" copies home. Just a thought. :rose:
-
How about this ... On your work server, 1) create a DB 2) Issue some select into commands to copy the tables from to 3) Do a full backup of small DB and copy that BAK file to a Flashdisk 4) Restore the DB from the flashdisk to your home PC. You will get both the schema and the data in one neat package and you can easily make this a very repeatable process to bring "fresh" copies home. Just a thought. :rose:
That gets my 5.
I wasn't, now I am, then I won't be anymore.
-
How about this ... On your work server, 1) create a DB 2) Issue some select into commands to copy the tables from to 3) Do a full backup of small DB and copy that BAK file to a Flashdisk 4) Restore the DB from the flashdisk to your home PC. You will get both the schema and the data in one neat package and you can easily make this a very repeatable process to bring "fresh" copies home. Just a thought. :rose:
Hello David Thanks very much for the suggestion The full DB was not as large as I though it would be so just used a full backup, took that home and restored it - all perfect! Thanks Richard