July 1, 2003 at 7:27 am
hello at all!
I'm sure here's somebody who can give me hints...
I want to script all my sql-databases, with all objects, constraints, users, a.s.o.
Is there any possibility to do so with transact-sql without writing a script scanning the system-databases/tables and build all these statements.
I looked into sql-server help for many hours but I didn't found anything except doing so taking the generate script - wizard...
July 1, 2003 at 8:08 am
Isn't "Generate Script Wizard" not fitting your needs?!
(OR)
Are you looking for a TSQL Statement to do the same?!
If you do this with "Generate Script Wizard", the Order will also be arranged automatically by the system, which is a great feature to save time.
.
July 1, 2003 at 10:28 pm
Parker77,
I suspect you want to automate the process rather than relying on the interactive wizard. Is that right?
I don't have a "script the lot" example, but a starting point may be Umachandar Jayachandran's example at http://www.umachandar.com/technical/SQL70Scripts/UtilitySPs/Main17.htm
Cheers,
- Mark
July 2, 2003 at 5:38 pm
Have a look at scptxfr.exe supplied by MS.
Maybe usefull with 'xp_cmdshell'
July 2, 2003 at 11:36 pm
I use this stored procedure, which I wretten
create procedure dbo.BACKUP$CreateScript (@path varchar(300)) as
begin
Declare
@resultint,
@namevarchar(128),
@cmdvarchar(2000),
@full_pathvarchar(1800)
Declare
mcur cursor local static for select name from master.dbo.sysdatabases where dbid not in (1,2,3,4) and name <> 'distribution'
Open mcur
fetch next from mcur into @name
while @@fetch_status = 0
begin
exec @result = dbo.UTILS$DirectoryExists @path
if @result = -1
begin
select @cmd = 'mkdir ' + @path
exec master.dbo.xp_cmdshell @cmd
print 'Main directory created'
end
select @full_path = @path + '\' + @name
exec @result = dbo.UTILS$DirectoryExists @full_path
if @result = -1
begin
select @cmd = 'mkdir ' + @path + '\' + @name
exec master.dbo.xp_cmdshell @cmd
print 'Parent directory created'
end
select @cmd = 'c:\SCRIPT\scptxfr.exe /s ' + @@servername + ' /d ' + @name + ' /q /I /F ' + @full_path
exec master.dbo.xp_cmdshell @cmd
print @cmd
fetch next from mcur into @name
end
close mcur
end
GO
July 3, 2003 at 4:48 pm
Have you taken a look at mssqlXpress. It is available from http://www.xpressapps.com
July 3, 2003 at 6:11 pm
Can any one let us know where can we get scptxfr.exe ??
.
July 3, 2003 at 6:29 pm
Belov thanks for the example.
Mdamera look in C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
July 9, 2003 at 4:50 am
Uh... hard to find the thread after a few days:)
sorry for the late response, but I was in holiday...
the sense of scripting database schemes automaticly is that I haven't to rely on information from developers or someone else when objects are added/changed... I imagined this is not only a problem for me... At this point of time I generated the schemes by using the script-wizard.
Many thanks at all for your hints - I will check your tips! :))
August 1, 2003 at 3:24 pm
Lost track of this thread... Many thanks to Belov and 5409045121009.
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply