none
Running Restore Commands From One Server To Several Other Servers RRS feed

  • Question

  • Hello Forum

    I am in the middle of designing a "Restore Factory" that uses Source Server/Source Database backups to restore databases to other Destination Servers.

    The scripts are stored in a single "Central Database" on our DBA SQL Server, what I want to know is, is there a way to run the commands from the Central Database on the DBA SQL Server?


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Wednesday, October 16, 2019 10:09 AM
    Wednesday, October 16, 2019 9:05 AM

All replies

  • You meant the script should have another parameter named as destination server?

    USE Master
    GO
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Restore_DB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[sp_Restore_DB]
    GO
    CREATE PROCEDURE [dbo].[sp_Restore_DB]
    @LocalBackupPath AS VARCHAR(255)='\\server\n$\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\',
    @BackupDate AS VARCHAR(10), --20090101
    @oldname SYSNAME, ---Old name of db
    @newname SYSNAME
     AS


    DECLARE @SQL AS VARCHAR(8000)
    DECLARE @LogicalName_Data VARCHAR(50),@LogicalName_Log VARCHAR(50)
    DECLARE @PhysicalName_Data VARCHAR(50),@PhysicalName_Log VARCHAR(50)

    DECLARE @Str VARCHAR(500)
    DECLARE @LocalBackupPath_Unrar VARCHAR(250),@defaultpath VARCHAR(250)

    ---checking if the file exists on the filesystem
    SELECT @Result=dbo.fn_file_exists('N:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'+@oldname+'\'+@oldname+@BackupDate+'.rar')
    IF @Result=0
    BEGIN
    RAISERROR ('There is no such file in the filesystem!!!',16,1)
    RETURN 
    END 






    CREATE TABLE #stage
    (
     LogicalName VARCHAR(50),
     PhysicalName VARCHAR(255),
     Type CHAR(1),
     FileGroupName  VARCHAR(50),
     [Size]VARCHAR(50),
     [MaxSize]VARCHAR(50),
     FileId  INT,
     CreateLSN  INT,
     DropLSN   INT,
     UniqueId  VARCHAR(100),
     ReadOnlyLSN INT,
     ReadWriteLSN INT,
     BackupSizeInBytes VARCHAR(50),
     SourceBlockSize INT,
     FileGroupId INT,
     LogGroupGUID  VARCHAR(255),
     DifferentialBaseLSN VARCHAR(255),
     DifferentialBaseGUID VARCHAR(255),
     IsReadOnly INT,
     IsPresent INT
    )

    ----Identify a Logical and a Physical Name file's name  of the database

    INSERT INTO #stage EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @LocalBackupPath+ @oldname+'.bak''')


    SELECT @LogicalName_Data=LogicalName ,
    @PhysicalName_Data='New'+RIGHT(PhysicalName, CHARINDEX('\', REVERSE(PhysicalName))-1),
    @PhysicalName_Log='New'+(SELECT RIGHT(PhysicalName, CHARINDEX('\', REVERSE(PhysicalName))-1)  FROM #stage WHERE Type='L'),
    @LogicalName_Log=(SELECT LogicalName  FROM #stage WHERE Type='L')
    FROM #stage WHERE Type='D'

    SET @defaultpath='N:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
    ----Perform Restore opeartion
    SET  @SQL = 'RESTORE DATABASE [' + @newname + '] FROM DISK = N''' + @LocalBackupPath+ @oldname+'.bak''
    WITH 
       MOVE '''+@LogicalName_Data+''' TO  ''' + @defaultpath + @PhysicalName_Data+''',
       MOVE '''+@LogicalName_Log+ ''' TO  ''' + @defaultpath + @PhysicalName_Log+''''
    EXEC (@SQL)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 16, 2019 9:14 AM
    Answerer
  • No....

    I have the restore commands already compiled in a Database, the Table includes the column "DestinationServer" and a bunch of Commands, I want to run the Commands on the "DestinationServer" from our DBA Server


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, October 16, 2019 10:08 AM
  • Well you need to have a linked server between your DBA Server and  the destination

    EXEC DestinatinServer.databasename.dbo.yourSP


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 16, 2019 10:28 AM
    Answerer
  • Hi Anthony C-UK,

     

    According to your description, I think you might create a linked server. For more information about how to create a linked server , please refer to following articles .

     

    Linked servers enable the SQL Server Database Engine and Azure SQL Database Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server.

     

    Create Linked Servers (SQL Server Database Engine)

     

    Understanding SQL Server Linked Servers

     

    Hope it will help you.

     

    Best Regard,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 17, 2019 7:17 AM
  • Thanks everyone for your suggestions

    We are now looking at creating .bat files to execure SQLCMD lines; will keep this post updated...


    Please click "Mark As Answer" if my post helped. Tony C.

    Friday, October 18, 2019 10:09 AM
  • Thank you for your posting .

    We will waiting for you. By the way, if you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 6:46 AM