none
Procedimiento Almacenado Para Respaldar Tablas Especificas RRS feed

  • Pregunta

  • Necesito restaurar una base de datos en otro servidor distinto, es para un proceso nocturno, la manera como lo quiero hacer es porque son dos servidores distintos y es imposible que estén linkeados, 

    La idea es crear un procedimiento almacenado para respaldar algunas tablas especificas, el .bak que me genere lo leeré con otro procedimiento almacenado. para restaurar esa tablas en otra base de datos.

    La ayuda que necesito es un sp que respalde alguna tabla de ejemplo, en una ruta especifica. 

    Solo para aclarar necesito el respaldo de las tablas con datos, y unicamente tiene que ser por un procedimiento almacenado.


    • Editado Adalberto88 viernes, 20 de septiembre de 2019 19:42
    viernes, 20 de septiembre de 2019 17:26

Respuestas

  • Hola Procedimiento Alamcenado saltar fechas en insert:

    Lo primero, es que no tengo nada claro, que el procedimiento que has pegado funcione correctamente. Me explico:

    Obviando, que no se tiene en cuenta las relaciones de los datos, con otros objetos (suponiendo que esto lo tengas testado). Además de que no todos los tipos de columna se pueden convertir a varchar max y luego devolver a su estado normal, porque hay datos que no son convertibles.

    A mi juicio el script tiene otro error

    SELECT @CONDITIONS=@CONDITIONS+ ' + Case When ['+Column_Name+'] is null then /* aquí el error */''Null''/*aquí fin del error*/ Else '''''''' + Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  ) +'''''''' end+'+''','''   
     

    Donde he puesto esa marca, para mi debiera de ser:

    SELECT @CONDITIONS=@CONDITIONS+ ' + Case When ['+Column_Name+'] is null then /* sin apostrofes */Null/*fin */ Else '''''''' + Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  ) +'''''''' end+'+''','''   

    No obstante dices que te funciona, pero yo para las pruebas, lo he tenido que corregir.

    Empiezo con mi posible, solución:

    Voy a darte un tip para un procedure, para exportar a un archivo.

    IF OBJECT_ID (N'dbo.spSaveTextResultToFile') IS NOT NULL
       DROP PROCEDURE dbo.spSaveTextResultToFile;
    GO
    create PROCEDURE spSaveTextResultToFile
      @TheSQL VARCHAR(MAX),
      @Filename VARCHAR(255),
      @Unicode INT=0
    /*
    e.g. spSaveTextResultToFile 'Select logstring+'', ''+convert(char(11),insertionDate,113) from activitylog', 'C:\workbench\Logreport.txt'
     
    */
    AS
      SET NOCOUNT ON
      DECLARE @MySpecialTempTable VARCHAR(255)
      DECLARE @Command NVARCHAR(4000)
      DECLARE @RESULT INT
     
      IF CHARINDEX ('Select ',LTRIM(@TheSQL))=0
           BEGIN
           RAISERROR ('Usage spSaveTextResultToFile <The SQL Expression> <The Filename)',16,1)
           RETURN 1
           END
    --firstly we create a global temp table with a unique name
      SELECT  @MySpecialTempTable = '##temp'
           + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
    --then we create it using dynamic SQL,
    --
      SELECT  @Command = 'create table ['
           + @MySpecialTempTable
           + '] (MyID int identity(1,1), MyLine varchar(MAX))
    insert into ['
           + @MySpecialTempTable
           + '](MyLine) ' +@TheSQL
      EXECUTE sp_ExecuteSQL @command
     
    --then we execute the BCP to save the file
      SELECT  @Command = 'bcp "select Myline from ['
              + @MySpecialTempTable + ']'
              + '" queryout '
              + @Filename
             + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
              + ' -T -S' + @@servername
      EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
      EXECUTE ( 'Drop table ' + @MySpecialTempTable ) 

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-tsql-of-text-files/

    Ahora para el laboratorio, he usado de la base de datos NORTHWIND la tabla Region, con 4 filas muy simples.

    Creo un procedure, que se encargará del trabajo en origen.

    CREATE PROCEDURE CrearArchivo
    AS
        BEGIN
            DECLARE @table TABLE
            (id    INT IDENTITY(1, 1), 
             texto VARCHAR(MAX)
            );
            INSERT INTO @table
    
            /* INSERTO EN LA VARIABLE DE TABLA LO QUE VIENE DEL PROCEDIMIENTO dbo.INS */
    
            EXEC dbo.INS 
                 'dbo.Region where 1=1';
    
            /* Tengo todos los valores */
    
            DECLARE @UnaFila VARCHAR(MAX)= '';
            SELECT @UnaFila = @UnaFila + '|' + p.texto
            FROM @table p;
    
            /* Tengo en la variable una fila, toda la consulta en un solo valor, pero me sobran los dos pipes iniciales */
    
            SET @UnaFila = SUBSTRING(@UnaFila, 2, LEN(@UnaFila));
    
            /* Elimiando el punto y coma, y para no tener problemas en la exportación reemplazo apostrofes por acento doble */
    
            SET @UnaFila = REPLACE(@UnaFila, '''', '´´');
    
            /* Envuelvo el valor en una select */
    
            SET @UnaFila = '  Select * from (SELECT * FROM (VALUES (''' + @UnaFila + '''))TBL(CAMPO)) as o;';
    
            /* Exporto mediante el procedimiento de exportar a texto */
    
            EXEC spSaveTextResultToFile 
                 @UnaFila, 
                 'c:\ejemplo\archivo.txt ', 
                 0;
    
            /* Ya dispongo de un archivo. */
    
        END;

    Ejecuto el procedure

    Ahora con el archivo

    En mi base de datos destino.

    USE [prueba]
    GO
    
    /****** Object:  Table [dbo].[Region]    Script Date: 22/09/2019 8:47:17 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Region](
    	[RegionID] [int] NOT NULL,
    	[RegionDescription] [nchar](50) NOT NULL,
     CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED 
    (
    	[RegionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    
    
    Y luego creo el procedure

    CREATE PROCEDURE leerTabla
    AS
        BEGIN
            DROP TABLE IF EXISTS #archivoTXT;
            CREATE TABLE #archivoTXT(CONTENIDO NVARCHAR(MAX));
            BULK INSERT #archivoTXT FROM N'c:\ejemplo\archivo.txt';
            DECLARE @QUERY NVARCHAR(4000)=
            (
                SELECT --replace(contenido, '´´', '''')
                CONTENIDO
                FROM #archivoTXT
            );
            DROP TABLE IF EXISTS #queryRows;
            CREATE TABLE #queryRows
            (id       INT IDENTITY(1, 1), 
             sentence VARCHAR(MAX)
            );
            INSERT INTO #queryRows(sentence)
                   SELECT v.value
                   FROM
                   (
                       SELECT contenido AS v
                       FROM #archivoTXT
                   ) AS tbl
                   CROSS APPLY STRING_SPLIT(tbl.v, '|') AS v;
            DECLARE @contador INT= 1;
            DECLARE @numSentences INT=
            (
                SELECT COUNT(*)
                FROM #queryRows
            );
            DECLARE @sql NVARCHAR(4000);
            WHILE @contador <= @numSentences
                BEGIN
                    SET @sql =
                    (
                        SELECT sentence
                        FROM #queryRows
                        WHERE id = @contador
                    );
                    SET @sql = replace(@sql, '´´', '''');
                    SET @sql = replace(@sql, '|', ';');
                    SET @sql = @sql + ';';
                    PRINT @sql;
                    EXEC sp_executeSql 
                         @sql;
                    SET @contador+=1;
                END;
        END;

    Leo el mismo con bulkInsert, y preparo las sentencias para ejecución como estaban, fila por fila

    Ejecuto el procedure.

        exec leerTabla

    Select * from region

    RegionID    RegionDescription
    ----------- --------------------------------------------------
    1           Eastern                                          
    2           Western                                          
    3           Northern                                         
    4           Southern                                         

    (4 filas afectadas)

    Espero te ayude


    • Marcado como respuesta Adalberto88 lunes, 23 de septiembre de 2019 0:45
    domingo, 22 de septiembre de 2019 7:36

Todas las respuestas

  • No, así no vas a poder hacerlo. El backup en SQL Server siempre copia todas las tablas, y cuando lo restauras, restaura todas las tablas.

    La única forma de separar ciertas tablas en el backup es meter esas tablas en un FileGroup distinto y hacer un backup parcial del FileGroup. Pero eso a su vez implica una serie de complejidades que probablemente no te convienen, por ejemplo, no puedes restaurar el backup parcial sobre una base distinta, tiene que partir de un backup completo de la original.

    Tendrás que buscar otra forma de copiar las tablas que no sea con un backup. Podrías usar replicación. O podrías crear un paquete de SSIS que las exporte un fichero, y otro paquete que importe el fichero en el otro servidor, y automatizarlo todo con SSIS.

    viernes, 20 de septiembre de 2019 20:54
  • Hola Procedimiento Alamcenado saltar fechas en insert:

    Lo primero, es que no tengo nada claro, que el procedimiento que has pegado funcione correctamente. Me explico:

    Obviando, que no se tiene en cuenta las relaciones de los datos, con otros objetos (suponiendo que esto lo tengas testado). Además de que no todos los tipos de columna se pueden convertir a varchar max y luego devolver a su estado normal, porque hay datos que no son convertibles.

    A mi juicio el script tiene otro error

    SELECT @CONDITIONS=@CONDITIONS+ ' + Case When ['+Column_Name+'] is null then /* aquí el error */''Null''/*aquí fin del error*/ Else '''''''' + Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  ) +'''''''' end+'+''','''   
     

    Donde he puesto esa marca, para mi debiera de ser:

    SELECT @CONDITIONS=@CONDITIONS+ ' + Case When ['+Column_Name+'] is null then /* sin apostrofes */Null/*fin */ Else '''''''' + Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  ) +'''''''' end+'+''','''   

    No obstante dices que te funciona, pero yo para las pruebas, lo he tenido que corregir.

    Empiezo con mi posible, solución:

    Voy a darte un tip para un procedure, para exportar a un archivo.

    IF OBJECT_ID (N'dbo.spSaveTextResultToFile') IS NOT NULL
       DROP PROCEDURE dbo.spSaveTextResultToFile;
    GO
    create PROCEDURE spSaveTextResultToFile
      @TheSQL VARCHAR(MAX),
      @Filename VARCHAR(255),
      @Unicode INT=0
    /*
    e.g. spSaveTextResultToFile 'Select logstring+'', ''+convert(char(11),insertionDate,113) from activitylog', 'C:\workbench\Logreport.txt'
     
    */
    AS
      SET NOCOUNT ON
      DECLARE @MySpecialTempTable VARCHAR(255)
      DECLARE @Command NVARCHAR(4000)
      DECLARE @RESULT INT
     
      IF CHARINDEX ('Select ',LTRIM(@TheSQL))=0
           BEGIN
           RAISERROR ('Usage spSaveTextResultToFile <The SQL Expression> <The Filename)',16,1)
           RETURN 1
           END
    --firstly we create a global temp table with a unique name
      SELECT  @MySpecialTempTable = '##temp'
           + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
    --then we create it using dynamic SQL,
    --
      SELECT  @Command = 'create table ['
           + @MySpecialTempTable
           + '] (MyID int identity(1,1), MyLine varchar(MAX))
    insert into ['
           + @MySpecialTempTable
           + '](MyLine) ' +@TheSQL
      EXECUTE sp_ExecuteSQL @command
     
    --then we execute the BCP to save the file
      SELECT  @Command = 'bcp "select Myline from ['
              + @MySpecialTempTable + ']'
              + '" queryout '
              + @Filename
             + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
              + ' -T -S' + @@servername
      EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
      EXECUTE ( 'Drop table ' + @MySpecialTempTable ) 

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-tsql-of-text-files/

    Ahora para el laboratorio, he usado de la base de datos NORTHWIND la tabla Region, con 4 filas muy simples.

    Creo un procedure, que se encargará del trabajo en origen.

    CREATE PROCEDURE CrearArchivo
    AS
        BEGIN
            DECLARE @table TABLE
            (id    INT IDENTITY(1, 1), 
             texto VARCHAR(MAX)
            );
            INSERT INTO @table
    
            /* INSERTO EN LA VARIABLE DE TABLA LO QUE VIENE DEL PROCEDIMIENTO dbo.INS */
    
            EXEC dbo.INS 
                 'dbo.Region where 1=1';
    
            /* Tengo todos los valores */
    
            DECLARE @UnaFila VARCHAR(MAX)= '';
            SELECT @UnaFila = @UnaFila + '|' + p.texto
            FROM @table p;
    
            /* Tengo en la variable una fila, toda la consulta en un solo valor, pero me sobran los dos pipes iniciales */
    
            SET @UnaFila = SUBSTRING(@UnaFila, 2, LEN(@UnaFila));
    
            /* Elimiando el punto y coma, y para no tener problemas en la exportación reemplazo apostrofes por acento doble */
    
            SET @UnaFila = REPLACE(@UnaFila, '''', '´´');
    
            /* Envuelvo el valor en una select */
    
            SET @UnaFila = '  Select * from (SELECT * FROM (VALUES (''' + @UnaFila + '''))TBL(CAMPO)) as o;';
    
            /* Exporto mediante el procedimiento de exportar a texto */
    
            EXEC spSaveTextResultToFile 
                 @UnaFila, 
                 'c:\ejemplo\archivo.txt ', 
                 0;
    
            /* Ya dispongo de un archivo. */
    
        END;

    Ejecuto el procedure

    Ahora con el archivo

    En mi base de datos destino.

    USE [prueba]
    GO
    
    /****** Object:  Table [dbo].[Region]    Script Date: 22/09/2019 8:47:17 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Region](
    	[RegionID] [int] NOT NULL,
    	[RegionDescription] [nchar](50) NOT NULL,
     CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED 
    (
    	[RegionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    
    
    Y luego creo el procedure

    CREATE PROCEDURE leerTabla
    AS
        BEGIN
            DROP TABLE IF EXISTS #archivoTXT;
            CREATE TABLE #archivoTXT(CONTENIDO NVARCHAR(MAX));
            BULK INSERT #archivoTXT FROM N'c:\ejemplo\archivo.txt';
            DECLARE @QUERY NVARCHAR(4000)=
            (
                SELECT --replace(contenido, '´´', '''')
                CONTENIDO
                FROM #archivoTXT
            );
            DROP TABLE IF EXISTS #queryRows;
            CREATE TABLE #queryRows
            (id       INT IDENTITY(1, 1), 
             sentence VARCHAR(MAX)
            );
            INSERT INTO #queryRows(sentence)
                   SELECT v.value
                   FROM
                   (
                       SELECT contenido AS v
                       FROM #archivoTXT
                   ) AS tbl
                   CROSS APPLY STRING_SPLIT(tbl.v, '|') AS v;
            DECLARE @contador INT= 1;
            DECLARE @numSentences INT=
            (
                SELECT COUNT(*)
                FROM #queryRows
            );
            DECLARE @sql NVARCHAR(4000);
            WHILE @contador <= @numSentences
                BEGIN
                    SET @sql =
                    (
                        SELECT sentence
                        FROM #queryRows
                        WHERE id = @contador
                    );
                    SET @sql = replace(@sql, '´´', '''');
                    SET @sql = replace(@sql, '|', ';');
                    SET @sql = @sql + ';';
                    PRINT @sql;
                    EXEC sp_executeSql 
                         @sql;
                    SET @contador+=1;
                END;
        END;

    Leo el mismo con bulkInsert, y preparo las sentencias para ejecución como estaban, fila por fila

    Ejecuto el procedure.

        exec leerTabla

    Select * from region

    RegionID    RegionDescription
    ----------- --------------------------------------------------
    1           Eastern                                          
    2           Western                                          
    3           Northern                                         
    4           Southern                                         

    (4 filas afectadas)

    Espero te ayude


    • Marcado como respuesta Adalberto88 lunes, 23 de septiembre de 2019 0:45
    domingo, 22 de septiembre de 2019 7:36