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 BackupAlldatabases

as



declare cur_databases cursor for

select name from master..sysdatabases where name not in
('tempdb','master','Northwind','pubs','model','msd b')



open cur_databases



declare @path varchar(100), @DBName varchar(100), @BackupPath
varchar(100), @cleardbs varchar(100),@day varchar(2),@month
varchar(2),@year varchar(4)

set @Path = '\\sdg_bankalert3\BA_Project_BackUp\SDG_BA25'


fetch next from cur_databases into @DBName



While (@@fetch_status = 0)

BEGIN

set @month= Case when len(month(getdate()))=1 then
'0'+Cast(month(getdate()) as varchar) else Cast(month(getdate()) as
varchar) end
set @day=Case when len(day(getdate()))=1 then '0'+Cast(day(getdate()) as
varchar) else Cast(day(getdate()) as varchar) end
set @year=year( GETDATE() )

--set @BackupPath = @Path + '\'+@month+'-'+
@year+'\'+@month+'-'+@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