V nadaljevanju prejšnji članek o skriptni vaših predmetov zbirke podatkov, sem ustvaril shranjeno proceduro, ki samodejno za zbirko podatkov skripte in scenarij piše v mapo. Ta skript, ustvarjen v SQL Server 2005. To je koristno, ker sedaj vse predmete, kot so tabele, shranjene procedure, stališča in funkcij v scenarij je.

  [ dbo ] . [ sp_GenDBscript ] CREATE POSTOPEK [dbo]. [Sp_GenDBscript]
  ( 150 ) -- Bijv: 'D:\SQLDATA\' Backup @ Path varchar (150) - Primer: "D: \ SQLDATA \ '
 AS 

 / * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ 
  SPC Namen: Ustvari scenarij bazo podatkov, vse predmete 
  Ustvaril: Ronald Kraijesteijn 
  Ustvarjeno: marec 2010 
  Posodobljeno: X 
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~ 
  Potrebno: 
  http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=56e5b1c5-bf17-42e0-a410-371a838e570a&displayLang=en 

  Primer 
  Exec sp_GenDBscript "D: \ SQLDATA ' 

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

  BEGIN
  SET NOCOUNT ON

  @ PATH AS VARCHAR ( 150 ) IZRAŽAJO @ PATH AS varchar (150)
  @CMD AS VARCHAR ( 150 ) AS IZRAŽAJO @ CMD varchar (150)
  @Param AS VARCHAR ( 150 ) AS IZRAŽAJO @ wfQuery varchar (150)
  @ SQL AS VARCHAR ( 256 ) IZRAŽAJO @ SQL AS varchar (256)
  @ DATE AS VARCHAR ( 10 ) IZRAŽAJO @ DATE AS varchar (10)
  @TextFile AS VARCHAR ( 150 ) IZRAŽAJO @ Besedilo File AS varchar (150)
  @MkdirStr AS VARCHAR ( 150 ) IZRAŽAJO @ MkdirStr AS varchar (150)
  @ TIME AS VARCHAR ( 150 ) IZRAŽAJO @ TIME AS varchar (150)
  @ DATABASE AS VARCHAR ( 150 ) IZRAŽAJO @ bazo podatkov kot varchar (150)
  @RC AS INT AS IZRAŽAJO @ RC INT 

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

 - GET seznamu baz podatkov
  CUR_DB CURSOR IZRAŽAJO Cursor CUR_DB
  [ name ] ZA SELECT [] ime
     sys. databases IZ sys. Baze podatkov
  [ name ] NOT IN ( 'master' , 'tempdb' , 'model' , 'msdb' , WHERE [ime] NOT IN ('mojster', 'tempdb "," model "," msdb ",
  , 'ReportServerTempDB' ) "Poročilo Server", "ReportServerTempDB ')

 - CREATE DATABASE scenarij vsakega
  CUR_DB OPEN CUR_DB
  CUR_DB INTO @ DATABASE Puščati INTO @ CUR_DB DATABASE
  @@FETCH_STATUS = 0 PRI @ @ FETCH_STATUS = 0
  BEGIN 
  @MkdirStr = 'mkdir ' + @BackupPath + ' \' + @Database + ' \script ' SET @ MkdirStr = 'mkdir' + @ Backup Path + "\ '+ @" \ script + Database  
   _ ' + @Date + ' _ ' + @Time + ' . SQL ' Besedilo File = @ podatkovne baze + "_" SET @ @ + '_' Datum + + + @ Time ". SQL" 
   script ' + @BackupPath + ' \ ' + @Database + ' \script\ ' + @TextFile SET @ Param = 'scenarij' + @ Backup Path + "\ '+ @" \ script + Database \ "+ @ Besedilo File 
   -schemaonly -d ' + @Database + "Schema le-d '+ @ Database  
   SET @ @ SQL = Pot + @ Param  

   - Preveriti, ali DIR obstajajo, če ne, CREATE DIR  
   Exec @ @ RC = master.dbo.xp_cmdshell MkdirStr, NO_OUTPUT 

   - Naredi backup 
   ## START scripting DATABASE : %s ## ', 10, 0, @Database ) WITH NOWAIT ; RAISERROR ('# # START DATABASE skripte:% s # # ", 10, 0, @ podatkovne baze) Z nowait;  
   Exec @ RC = poveljnik .. xp_cmdshell @ SQL, no_output 
   ČE @ RC <> 0 
   An Error occured WHILE scripting DATABASE : ' + @Database Napaka PRINT "prišlo do skripte DATABASE:" + @ Database 

   ## Done scripting DATABASE : %s ## ', 10, 0, @Database ) WITH NOWAIT ; RAISERROR ('# # V skriptno DATABASE:% s # # ", 10, 0, @ podatkovne baze) Z nowait; 
   Puščati CUR_DB INTO @ Database 
   END 
   CLOSE CUR_DB 
   Locate CUR_DB DEAL  

   END 

Tags Glej vse oznake

Postopki Shranjene