Scripting DB

• johncyriac

  SSCommitted

  Points: 1542

  More actions

  January 14, 2004 at 11:14 pm

  #83358

  I want to have a full db script but ,The problem is its taking hell a lot of time ,so many hours ,any other way to script the db as a whole

  regards

  john

• Semih_erakay

  Newbie

  Points: 7

  More actions

  January 16, 2004 at 7:29 am

  #490424

  You can use the stored procedure indicated below to script all the databases in your server except DBs that are in 'not in' clause. Also you can search the usage of scptxfr.exe.

  CREATE PROCEDURE usp_GenerateDBScripts @FolderName varchar(150) AS

  -- Author: Haidong Ji

  -- Purpose: This stored procedure will generate DB scripts

  -- for each user databases on this server.

  -- Note that DB property info will not be generated, such as file location,

  -- recovery model, and other db options like Auto Update Statistics,

  -- Auto Shrink, etc.

  --Note also that you can use UNC path for output file.

  -- ***Important*** Depending upon your installation of SQL Server,

  -- you might need to change the directory name

  -- to get to the scptxfr.exe utility

  set nocount on

  declare @DBName varchar(50)

  Declare @CMD Varchar(1000)

  --Check whether the user supply \ in the directory name

  if not (right(@FolderName, 1) = '\')

   set @FolderName = @FolderName + '\'

  declare UserDB_Cursor cursor fast_forward for

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

  open UserDB_Cursor

  fetch next from UserDB_Cursor into @DBName

  WHILE @@FETCH_STATUS = 0

  Begin

  Select @CMD='Exec master..xp_cmdshell ''"c:\Program Files\Microsoft SQL Server\MSSQL\upgrade\scptxfr.exe" /I /s '

  +@@SERVERNAME+' /d ' + @DBName + ' /f ' + @FolderName + @DBName + '_'+

  Replace(convert(Varchar(8),getdate(),112)+convert(Varchar(8),getdate(),114),':','')+

  '.sql /r /O /Y /G '''

  print @CMD

  Exec (@CMD)

  fetch next from UserDB_Cursor into @DBName

  end

  close UserDB_Cursor

  deallocate UserDB_Cursor

  GO

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736568

  More actions

  January 16, 2004 at 11:08 am

  #490497

  You can also script in stages, tables, then views, then procs, then logins, etc.

• johncyriac

  SSCommitted

  Points: 1542

  More actions

  January 16, 2004 at 9:48 pm

  #490580

  The script is fundastic I feel (I didn't try ).But Mr johnes my problem is more complicated I feel ,if I am trying to script the tables alone ,after some 14 % completion in status bar it stands still .Any Idea about this or only option remains is xp_commandshell ,The server is resonable I feel Compaq Proliant 7000 ,2 PIII Xions ,36 GB Hrd Drives

  regards

  john