Member Avatar for Shalinko

Recently had to re-install all of pc...not a prob as all (except db) was backed up to seperate hd. I develop locally using MSSQL client tools and adp's and then upload new tables as and when ready. I had done quite a bit of work when pc died, and now have to recreate half a dozen tables plus repopulate data.
Can I make a 'create table' sql code which I can then run to restore tables, or is there another way?
Thanks in advance.

  • 2 Contributors
  • 3 Replies
  • 100 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by kb.net
Member Avatar for kb.net

Hi,

There are 2 ways, detach, backup, and attach. Or generate Script.
First the first one you will need to right click database and in all tasks sub menu choose detach database. Now go to the C:\Program Files\Microsoft SQL Server\MSSQL$\Data\ and look for the data base name. You will find two files with different extensions. Copy them to the new computer in the same folder and repeat the first step but this time choose Attach DataBase. you will be prompt for DB path and name. Navigate to the folder and choose the .MDF file and that is it.

The easier way is to right click database choose generate script from All Tasks sub menu and the Press show all to choose out the objects you need to script. Press OK and you will be prompt to save the SQL file in some where. Take this file and run it using SQL query Analyzer on the destination computer and it will work.

Member Avatar for Shalinko

Thanks very much for that kb, I noticed from you pp that you familiar with Windows apps, would you by chance know if I can import data from MSSQL into an excel file.

Member Avatar for kb.net

Hi,

You have to ways either export data from MSSQL to Excel or import it from excel to mssql:
For the first choose the database >> right click >> All tasks >> Export data >> choose the data source (the server and data base name) >> press next and choose the destination (Destination as Excel instead of SQL server and then the file name) >> choose the tables to export and press OK.

The second way is to open the excel file >> choose data >> Import External Data >> Import Data >> choose the data source (in your case New SQL Server Connection.odc) >> choose the database and then the tables and that is it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.