Wara fuq artikolu preċedenti dwar kitba oġġetti database tiegħek, I ħolqot proċedura maħżuna li awtomatikament għall skripts database tiegħek u l-iskrittura jikteb lill folder. Din l-iskrittura hi maħluqa fil SQL Server 2005. Dan huwa utli għaliex issa l-oġġetti bħala tali tabelli, proċeduri maħżuna, l-opinjonijiet u l-funzjonijiet fi b'kitba li.

  [ dbo ] . [ sp_GenDBscript ] PROĊEDURA JINħOLOQ [dbo]. [] Sp_GenDBscript
  ( 150 ) -- Bijv: 'D:\SQLDATA\' Backup @ Path varchar (150) - Eżempju: "D: \ SQLDATA \"
 KIF 

 / * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ 
  SPC Għan: Jiġġenera database iskrittura, l-oġġetti kollha 
  Maħluq By: Kraijesteijn Ronald 
  Maħluqa: Mar 2010 
  Updated: X 
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~ 
  Meħtieġa: 
  http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=56e5b1c5-bf17-42e0-a410-371a838e570a&displayLang=en 

  Eżempju 
  "Sp_GenDBscript D Exec: \ SQLDATA" 

  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ ~ ~ * /

  BEGIN
  NOCOUNT SET FUQ

  @ PATH AS VARCHAR ( 150 ) JIDDIKJARAW @ PASSAĠĠ KIF varchar (150)
  @CMD AS VARCHAR ( 150 ) KIF JIDDIKJARAW @ varchar CMD (150)
  @Param AS VARCHAR ( 150 ) KIF JIDDIKJARAW @ params varchar (150)
  @ SQL AS VARCHAR ( 256 ) JIDDIKJARAW @ SQL KIF varchar (256)
  @ DATE AS VARCHAR ( 10 ) JIDDIKJARAW @ DATA LI varchar (10)
  @TextFile AS VARCHAR ( 150 ) JIDDIKJARAW @ Test File KIF varchar (150)
  @MkdirStr AS VARCHAR ( 150 ) JIDDIKJARAW @ MkdirStr KIF varchar (150)
  @ TIME AS VARCHAR ( 150 ) JIDDIKJARAW @ ĦIN KIF varchar (150)
  @ DATABASE AS VARCHAR ( 150 ) JIDDIKJARAW @ DATABASE KIF varchar (150)
  @RC AS INT KIF JIDDIKJARAW @ INT RC 

  @ TIME = REPLACE ( CONVERT ( VARCHAR , GETDATE ( ) , 114 ) , ':' , '' ) SET @ ĦIN = REPLACE (jikkonvertu (varchar, GETDATE (), 114),':','')
  @ DATE = CONVERT ( VARCHAR , GETDATE ( ) , 112 ) SET @ DATA = jikkonvertu (varchar, GETDATE (), 112)
  @ PATH = '"C: \P rogram Files \M icrosoft SQL Server \9 0 \T ools \P ublishing \S qlPubWiz.exe"' @ SET PASSAĠĠ = "" C: \ P rogram Files \ icrosoft M SQL Server \ 9 0 T ools \ \ ublishing P \ qlPubWiz.exe "S"

 - LISTA TA 'DATABASES IKOLLOK
  CUR_DB CURSOR CUR_DB Cursorius JIDDIKJARAW
  [ name ] GĦALL-isem] TAGĦŻEL [
     sys. databases MILL sistemi. Databases
  [ name ] NOT IN ( 'master' , 'tempdb' , 'model' , 'msdb' , FEJN [isem] MHUX FIL ("kaptan", "tempdb", "mudell", "msdb",
  , 'ReportServerTempDB' ) "Server Rapport", "ReportServerTempDB")

 - JINħOLOQ DATABASE TA 'KULL Script
  CUR_DB OPEN CUR_DB
  CUR_DB INTO @ DATABASE @ CUR_DB DATABASE FIS jġibu
  @@FETCH_STATUS = 0 WAQT @ @ FETCH_STATUS = 0
  BEGIN 
  @MkdirStr = 'mkdir ' + @BackupPath + ' \' + @Database + ' \script ' SET @ MkdirStr = "mkdir" + @ backup Path + "" + \ @ \ "b'kitba + Database  
   _ ' + @Date + ' _ ' + @Time + ' . SQL ' SET @ Test Fajl = @ database + "" + _ @ + Data "" + _ @ Ħin + ". SQL" 
   script ' + @BackupPath + ' \ ' + @Database + ' \script\ ' + @TextFile SET @ Param = "iskrittura" + @ backup Path + "" + \ @ + 'databases "\ iskrittura \" + @ Test File 
   -schemaonly -d ' + @Database + "Schema biss-d" + @ Database  
   SET @ SQL = @ Path + @ Param  

   - Jiċċekkjaw jekk DIR JEŻISTU, JEKK MHUX, JINħOLOQ DIR  
   Exec @ RC = @ master.dbo.xp_cmdshell MkdirStr, NO_OUTPUT 

   - Isir backup 
   ## START scripting DATABASE : %s ## ', 10, 0, @Database ) WITH NOWAIT ; RAISERROR ("# # BIDU scripting DATABASE:% s # #", 10, 0, @ database) MA nowait;  
   Exec @ RC = kaptan .. xp_cmdshell @ SQL, no_output 
   JEKK @ RC <> 0 
   An Error occured WHILE scripting DATABASE : ' + @Database ISTAMPAR "Ġara żball scripting WAQT DATABASE:" + @ Database 

   ## Done scripting DATABASE : %s ## ', 10, 0, @Database ) WITH NOWAIT ; RAISERROR ("# # Magħmul scripting DATABASE:% s # #", 10, 0, @ database) MA nowait; 
   CUR_DB jġibu FIS @ Database 
   END 
   Viċin CUR_DB 
   TTRATATI CUR_DB Alloka  

   END 

Tags Ara kollha Tags

Maħżuna Proċedura