How to take backup of only the structure of a database in Ms-sql, no need of data.

[winsletmathew]

I need the backup of only the structure of database, not the data.

I tried, this procedure , but it is copying the whole database


create Procedure BackupAlldataba ses

as



declare cur_databases cursor for

select name from master..sysdata bases where name not in
('tempdb','mast er','Northwind' ,'pubs','model' ,'msdb')



open cur_databases



declare @path varchar(100), @DBName varchar(100), @BackupPath
varchar(100), @cleardbs varchar(100),@d ay varchar(2),@mon th
varchar(2),@yea r varchar(4)

set @Path = '\\sdg_bankaler t3\BA_Project_B ackUp\SDG_BA25'


fetch next from cur_databases into @DBName



While (@@fetch_status = 0)

BEGIN

set @month= Case when len(month(getda te()))=1 then
'0'+Cast(month( getdate()) as varchar) else Cast(month(getd ate()) as
varchar) end
set @day=Case when len(day(getdate ()))=1 then '0'+Cast(day(ge tdate()) as
varchar) else Cast(day(getdat e()) as varchar) end
set @year=year( GETDATE() )

--set @BackupPath = @Path + '\'+@month+'-'+
@year+'\'+@mont h+'-'+@day+'-'+ @year+'\'+'sdg_ ba25'+'\'+ @DBName + '.bak'
set @BackupPath = @Path + @DBName + '.bak'
print @BackupPath
backup database @DBName to disk = @BackupPath with init

fetch next from cur_databases into @DBName

END



Please if any one can give me the solution...


With regards,
winsletmathew

[debasisdas]

question moved to sql -server forum.

[ck9663]

If it's SQL 2005, open your Management Studio. Open your Object Explorer Detail window. Click on Tables. Just like Windows Explorer, select all tables that you want to backup. Right-click, Script table as, Create to, New Query Window.

Copy the code on your query window, open a text editor, paste everything.

Happy coding!

-- CK

[winsletmathew]

If it's SQL 2005, open your Management Studio. Open your Object Explorer Detail window. Click on Tables. Just like Windows Explorer, select all tables that you want to backup. Right-click, Script table as, Create to, New Query Window.

Copy the code on your query window, open a text editor, paste everything.

Happy coding!

-- CK

Hello,

The above solution takes a lot of time if if u have many tables..
And if u take many databases , then this process will take a lot of time.

Can u give a command or procedure, which automatically takes the backup of all the structures in the database

With regards,
winsletmathew

[ck9663]

You can create script to generate structure of each table. The problem is, you will not able to capture the index, constraints, etc.

You can select multiple tables at a time. You just have to browse to other databases.

Maybe our other members can help as well.

-- CK