March 7, 2008 at 6:52 am
Hi,
I have take a backup of database in .csv format. now i want restore these files into sql server 2005.
How it is possible? tell me.:)
Regards
Sarvesh Kumar Gupta
March 7, 2008 at 7:07 am
Sarvesh Kumar Gupta (3/7/2008)
Hi,I have take a backup of database in .csv format. now i want restore these files into sql server 2005.
How it is possible? tell me.:)
Regards
Sarvesh Kumar Gupta
You can use various methods to bulk load your data (use the bcp utility, OPENROWSET BULK, ...) These are described on http://technet.microsoft.com/en-us/library/ms187042.aspx
Regards,
Andras
March 8, 2008 at 5:17 pm
Hi Sarvesh,
I understand you have performed backup of database not imported data. If it is then following command will help you to restore .csv file:
restore database Test from disk='c:\mohan\test.csv' with replace
Replace Test with actual database and path with wherever your file is.....
-- with replace is required if you are overriding existing one otherwise you donot need to use it.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 10, 2008 at 3:36 am
Mohan Kumar (3/8/2008)
Hi Sarvesh,I understand you have performed backup of database not imported data. If it is then following command will help you to restore .csv file:
restore database Test from disk='c:\mohan\test.csv' with replace
Replace Test with actual database and path with wherever your file is.....
-- with replace is required if you are overriding existing one otherwise you donot need to use it.
I'm afraid this may not help. What the above command will do is that it will restore a database backup from a file that has the csv extension only if the file contains a valid SQL Server backup. But it has not much to do with the csv format. If the data is in csv format, the restore command will not help, as it expects data in SQL Server backup format (the backup file itself must be follow the MTF (Microsoft Tape Format).
Regards,
Andras
March 10, 2008 at 6:36 am
Andras
Good point, but the poster said he had a backup. I suspect as you, that he has at least one CSV file that needs to be imported, not a SQL Server backup. However, if indeed he does have a backup then the restore script was correct. In other words, more information is needed to offer proper assistance.
Thanks
Marvin Dillard
Senior Consultant
Claraview Inc
March 10, 2008 at 6:44 am
MD (3/10/2008)
AndrasGood point, but the poster said he had a backup. I suspect as you, that he has at least one CSV file that needs to be imported, not a SQL Server backup. However, if indeed he does have a backup then the restore script was correct. In other words, more information is needed to offer proper assistance.
Thanks
Hi Marvin,
you are right, the original post is rather ambiguous 🙂 and Mohan's solution is correct if the "csv backup" is a SQL Server backup and not csv.
Regards,
Andras
March 11, 2008 at 1:36 am
Hi all of you,
Full Info regarding my topic:
I have multiples tables in my database and i have taken backup of these tables in my folder where are multiple .csv files, one for each table. Now i want to restore into my database all these in once time.
I have a button when i click on this then these files should be restore into the database.
March 11, 2008 at 8:04 am
Looks like you need to do an import, not a backup restore. You can do this utilizing the Integration Services tool provided, where you can set it up to do all at once, or you can perform each individually by right clicking on the database, and choosing tasks and import. The key thing is you will have to overwrite the data, instead of append it to the end of the file if you do not want to keep the current data in the table.
Hope this helps
Marvin Dillard
Senior Consultant
Claraview Inc
March 11, 2008 at 8:56 am
You mention you 'have a button', given that, we must asume that someone wrote a program to do a restore of a sort. This would most likey be a bulk copy as has been mentioned previously.
If these truly are Comma Seperated Value (csv) files then what you actually have is an export of the data out of SQL into plain text.
Try opening a file with notepad. If it opens you will see something like the following...
123456,Smith,Tom,123 Any Street,AnyTown,TX,75111 etc. ad nauseum
If you have Excel on your machine and double click the file you wil most likely fire Excel and it will try to open it. However it won't open more then 64k rows.
Upon determing that the file is actually csv I suggest you fire up SSIS and build a package to Import this data into the set of tables in your database. Use Project >> SSIS Import and Export Wizard... to get started. Or as mentioned use the bcp utility and write a vbscript with a line for each table. http://msdn2.microsoft.com/en-us/library/aa196743(SQL.80).aspx.
Keep in mind you will be adding records to each table in the SQL Server database. This is probably not a good thing. Therefore, you will need to truncate (or delete the rows from) the existing tables. This assumes that every record in the csv dataset is actually a match for every record in the database.
If the DB itself is actually hosed then you will need to recreate it and the structure of the tables keeping watch for all PK and FK issues and identity attribute issues.
After you have completed all this fun stuff make a real backup and create a job to do this periodically.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply