Backup of the structure of a database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Backup of the structure of a database

Author

Topic

winsletmathew
Starting Member

2 Posts

Posted - 2008-11-06 : 06:32:26

How to take the backup of the structure of the database, that includes structure of tables and script for procedures.

I have used this procedure :
ALTER Procedure BackupAlldatabases

as

declare cur_databases cursor for

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

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

-- -- If we ignore the while(begin,end) , then only one databases will be considered ,But this procedure gives me the backup of the whole database(including data.), Which i dont want.
Please if any one can find the solution

Thanks,
winslet

Elisabeth Redei
Starting Member

15 Posts

Posted - 2008-11-06 : 14:26:30

Hi Winslet,

Judging from the databases you excluded, you are on SQL Server 2000, or? You can script the databases either in Enterprise Manager or maybe you can do it via SQL-DMO if you want a more automated solution (BACKUP statement will always backup your data).

HTH!

/Elisabeth

elisabeth@sqlserverland.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources