I am using SQL Server 2005 Express with SQL Server Management Studio Express. I do not find a tool in SQL Server Management Studio Express to convert a database into a sql file. I want a TSQL command to export a database as a sql file.
Advance thanks.
What do you mean by SQL File? A few thoughts come to mind.
1) Backup file
2) Detached MDF
3) Script of the database and/or data.
Please let me know and I'll try to help further. If you indicate the purpose of the export, then I might be able to suggest a few more ideas. Integration services is not available with Express, so you will not be able to simply export data from one database to another format without the full version of SQL Server 2005. From a SQL Command automated aspect, you might be best off backing up the file or creating a script to export the data.
Chris
|||I just meant the script of the database and data. I believe there should be some TSQL commands to do this.|||You can create dynamic SQL to generate the data for the tables. The SQL would be a SELECT statement that generates INSERT statements as text. If you use this method, you will need to be careful in order to account for single quotes in the data. I've always used Generate Scripts for creating the database stucture, but you can look at the sys.objects and sys.columns (SQL 2005) for sysobjects and syscolumns (2000) along with sys.types in order to generate your own version of the schema. You can create a cursor to loop through the tables in sysobjects and read the syscolumns for each table and then generate the data. This won't be a speedy solution though, depending on how much data you have.
Chris Winland
|||Nope, there really aren't. Scripting the database is doable, and the tools are there in SSMS as well as SQL Server managment objects (SMO). You could also use SMO to script the data if you wanted.
|||In SQL Server Management Studio, there's a wizard to create all the objects (database, tables, constraints, etc.) But there is none for data. You have do create your own script to generate an INSERT statement based on the data you have from your source database.|||
Giritharan--
this is the same problem, which i was struggling a few months ago, what i did i create the SP for that
1) Backup SP Generates plain text file(can set as .sql one) , includes Schema + data (full) for connected DB.
2) Restore SP which does the reverse.
if u want u just mail me : gsgill76@.yahoo.com, or gsgill76@.gmail.com
Hope that will help u
Regards,
Thanks.
Gurpreet S. Gill
