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

Nov 3 '08 #1

debasisdas

question moved to sql -server forum.

Nov 3 '08 #2

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

Nov 4 '08 #3

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

Nov 4 '08 #4

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

Nov 4 '08 #5