Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.