September 22, 2010 at 2:42 am
I don't understand why there are no export/import utilities in SQL Server. Oracle, DB2 (LUW), and MySQL have export and import utilities. SQL Server 6.5 had export/import utilities. With export/import utilities, one does not have to restore the entire database to recover data. You just restore the applicable table(s).
And, I would like to thank that the Microsoft SQL Server team is just as smart as the Oracle, DB2 (LUW), and MySQL teams!
September 22, 2010 at 2:46 am
just right click on database and select task option, you will be welcomed with import/export option
----------
Ashish
September 22, 2010 at 3:08 am
Exports in SQL Server
Is there a script to export only data in all user tables in SQL Server 2005 database?
September 22, 2010 at 3:16 am
Is there a script to export only data in all user tables in SQL Server 2005 database?
if you mean by 'export only data FROM all user tables' then yes select only users table and the data from those tables will be export to destination.
yes,before you blame me...... Limitation is one database at a time.
----------
Ashish
September 22, 2010 at 3:29 am
Exports in SQL Server
Thanks for the info. Also, I found this script to export only data in all user tables in a database (OnlineApplication). Now, I can put the output from the script below in a .bat job and schedule it to run daily.
use OnLineApplication
select 'bcp OnlineApplication..' +
name + ' out ' + name + '.txt -c -t , -r -e ' +
name + '.err -m100 -b500 -a4096 -S servername-U userid -P password'
from sys.tables
where name <> 'sysdiagrams'
order by name;
September 22, 2010 at 3:49 am
Ed.Edwards (9/22/2010)
I don't understand why there are no export/import utilities in SQL Server. Oracle, DB2 (LUW), and MySQL have export and import utilities. SQL Server 6.5 had export/import utilities.
You mean like bcp, BULK INSERT and SSIS?
You can set up an export package via the import/export wizard (right click a database, tasks-> export data), save the package and then set it up as a SQL Agent job. Same with an import wizard package
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 3:55 am
Exports In SQL Server
To Gail Shaw,
Outstanding! Thanks for the info! Have a great day!
September 22, 2010 at 4:07 am
Exports In SQL Server
To Gail Shaw,
When I used the Export/Import Wizard via the instructions you send, it only let me pick one user table. How do I select all user tables in a database at one time?
September 22, 2010 at 4:11 am
If you right-click the database and select tasks -> export, the fourth page of the wizard allows you to select any number of tables and map them to a destination.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 4:19 am
Exports In SQL Server
To Gail Shaw,
I am sorry that I did not explain clearly the task I was trying to do. The task I am trying to do is "export" only data in all user tables in a SQL Server database to .txt files (each individual .txt file for each user table).
September 22, 2010 at 4:21 am
Then you probably want to either use bcp or write an SSIS package. That's beyond the capabilities if the import/export wizard (though you can use that to create one package per table and save the separate packages)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 4:25 am
if you interested in using impor export wizard, then you can choose flat file as your destination to export the data in txt file.
----------
Ashish
September 22, 2010 at 4:41 am
Exports In SQL Server
To Gail Shaw,
Ok. I'll use the bcp script I found via google (for creating output .txt files). Also, I like your suggestion about using the export/import wizard to save all the user tables to another database (Example MyDB to MyDB_Restore). Now, if something happened to the data and a user wanted me to put back a particular table, I could just import that one table back from the MyDB_Restore database.
Thanks for the info. Have a great day!
September 22, 2010 at 4:44 am
Thanks for the info. Have a great day!
seems like i didnt provide the correct information.
----------
Ashish
September 22, 2010 at 5:00 am
Exports In SQL Server
To Ashish,
Your info that you sent was correct and I thank you for your response! You see, I am an Oracle (Solaris, Linux, Windows), DB2 (OS/390 z/os - mainframe), DB2 (LUW), SQL Server (Windows), and a MySQL (Oracle - Sun) DBA. Many years ago, I started with SQL Server 6.5. At that time SQL Server 6.5 had the export and import utilities similar to Oracle, DB2 (LUW), and MySQL. But, who knows why, the export and import utilities were removed from SQL Server, starting with SQL Server 7.
I was trying to complete a customer's task (one that I normally use export/import utilities, in other databases, to accomplish) of outputting only table data in all user tables in a SQL Server database to individual text files (.txt, csv).
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply