2

I am using SQL server 2008 R2 and VS2010. I made simple application by using this tools. I attach database as .mdf in my application and deploy that application on other machine its works fine. Now if I plan for new release of my app which some extended features, I can upload Code by DLL, But problem is updating .mdf file, to handle this I am exporting database into .xls sheets (Application have one utility to backup database) and then import into SQL Server to create new .mdf file. Someone have better solution on this? Can I open old version of .mdf file in SQL Server(Third party software) and Execute DML/DDL script on it to make latest code and database compatible ? May I keep .sql file in one of my project code and execute it by some utility..? Any Class in C# which can handle this..?

Ankush Madankar
  • 3,689
  • 4
  • 40
  • 74

2 Answers2

0

I did not get your query completely. Do you want to upgrade the DB through the application? You can of course run .sql files through your application, but I'm not sure it would help you change the Database configuration.

Alternatively, if you already have the updated .mdf file and the database name is same, then you can follow the following steps. 1. Detach the database by SSMS in the third party environment through SSMS. 2. Replace the .mdf, .ldf and .ndf (if any) in the disk. 3. Attach the updated .mdf file. This will get the new Object definitions as well as data.

Bedabrata
  • 15
  • 1
  • I dont want to lost data from old .mdf file. Simply I want data from old .mdf file and some new table structure or new colm get added into that .mdf file. – Ankush Madankar Jul 04 '13 at 07:18
  • Oh Ok. Then I guess you can simply generate the sql scripts to a .sql file for all the modified objects from the old DB via SSMS and run them in the new DB server. – Bedabrata Jul 04 '13 at 08:10
  • Its best to do by the SSMS, where, you can go by the following steps. 1. Connect Object explorer to old DB. 2. Right click on database and got to Tasks -> Generate SQL Scripts and follow the instructions from the windows thereon. It is also possible to do by SQL code, but that is a bit roundabout. Let me know if you're not comfortable with this process, and I can provide you the code. – Bedabrata Jul 04 '13 at 08:49
  • I know the script generation process of database in SSMS. But concern is not about generating database script, It’s about merging old .mdf data into **new database structure** and after merging that data I can copy new version of .mdf file into my project which integrate my new database structure and client data into new version of .mdf file, even if I able to generate database script how can I merge old data into this script?? – Ankush Madankar Jul 04 '13 at 12:11
  • As far as I'm aware, there is no process for merging .mdf files, because the SQL Server would not know which data to keep in the final data base, in case the table structure, constraints or data conflicts occur. – Bedabrata Jul 05 '13 at 05:49
0

As far as I'm aware, there is no process for merging .mdf files, because the SQL Server might not be able to identify the similar objects properly as sys tables may be different, and also would not know which data to keep in the final data base, in case the table structure, constraints or data conflicts occur.

However, looking at your requirement, the best way I can suggest is,
1. Generate the Alter scripts for the tables modified (By right clicking on the object name and using Script Table As.. option). Of course, I assume you have the list of objects modified and the modifications.
2. Connect the two DB servers over network and write an SSIS package or Import data from the old DB to the new one for the tables you want.

Hope this helps.

Bedabrata
  • 15
  • 1