Generate Sql Script Files For Your DataBase

السلام عليكم ورحمة الله و بركاته :

هدا الكود إجرائية بسيطة تقوم بإنشاء Script files للقاعدة المحددة ..

طبعاً هدا الكود حسب التجربة لا يعمل على النسخة القياسية من Sql Server وإنما يحتاج إلى نسخة Sql Server Developer

ولا أعلم إدا كان يعمل على النسخة Enterprise أم لا .. لأني لم أجرب دلك طبعاً . :D ..

set quoted_identifier off
go
use master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__GenerateScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__GenerateScript]
GO
create proc sp__GenerateScript
@dbname varchar(256) = '',
@Applicationpath varchar(700) ='C:\program files\Microsoft SQL Server\mssql\upgrade\',
@destinationpath varchar(700) ='D:\MSSQLScripts\',
@switches varchar(200) = ' /X /Y /A /q /r /G /I '
as
--Created by:MAK
--Date: Aug 28, 2004
--Objective: Generate SQl Scripts for all or given database
set quoted_identifier off
set nocount on
set concat_null_yields_null off
declare @count int
declare @folderexist int
declare @maxcount int
declare @query varchar(1000)
declare @date varchar(10)
set @date = convert(varchar(10),getdate(),112)
set @count =1
Print 'Generate Script - Started'
print getdate()
set @Applicationpath = @Applicationpath +'scptxfr.exe'
create table #dbtable (id int identity(1,1), dbname varchar(256))
if @dbname = ''
begin
      insert into #dbtable (dbname) select name from 
master..sysdatabases
      where   name != 'tempdb'  and status & 32 != 32
      and status & 256 != 256   and status & 512 != 512
      and status & 1024 != 1024   and status & 4096 != 4096
      and status & 32768 !=32768   and status & 1073741824 !=1073741824
end
else
begin
      insert into #dbtable (dbname) select name from 
master..sysdatabases
      where   name != 'tempdb'  and status & 32 != 32
      and status & 64 != 64   and status & 128 != 128
      and status & 256 != 256   and status & 512 != 512
      and status & 1024 != 1024   and status & 4096 != 4096
      and status & 32768 !=32768   and status & 1073741824 !=1073741824
      and name = @dbname
end

if (select count(*) from #dbtable) = 0
begin
      set @date = convert(varchar(100), getdate(),109)
      Print 'Error: No valid database found for Generating Script'
end
else
begin
      set @destinationpath = @destinationpath +@date
      create table #files (Files int, Folder int, parent int)
      insert #files exec master.dbo.xp_fileexist @destinationpath
      select @folderexist = Folder from #files
      if @folderexist <>1
      begin
      set @query = 'MKDIR "'+@destinationpath+'"'
      print @query
      exec master..xp_cmdshell @query
      set @destinationpath = @destinationpath
      end
	else
	begin
	print 'Information:'+ @destinationpath + ' already exist. Skipping Folder Creation'
	end
      set @maxcount =  (select max(id) from #dbtable)

While @count <= @maxcount
begin
      set @dbname = (select dbname from #dbtable where id = @count)
      set @query = '"'+@applicationpath +'"'+ ' /s '+@@servername+ ' /d '+@dbname+ +' /F '+@destinationpath+ @switches 
      set @query = @query
      print @query
      exec master..xp_cmdshell @query

if @@error <> 0
begin
Print 'Error'
end

set @count = @count+1
end

end
print getdate()
Print 'Generate Script - Completed'

إختبار الإجرائية بالطرق التالية :

--exec sp__GenerateScript '','D:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--exec sp__GenerateScript
--exec sp__GenerateScript 'Master','C:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'
--exec sp__GenerateScript 'Msdb','C:\program files\Microsoft SQL Server\mssql\upgrade\','D:\mssqlscripts\'

إن شاء الله يكون لها فائدة ما