By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Share this Question
Share on Google+
4 Replies


debasisdas
Expert 5K+
P: 8,127
question moved to sql -server forum.
Nov 3 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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

P: 2
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
Expert 2.5K+
P: 2,878
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

Post your reply

Sign in to post your reply or Sign up for a free account.