Restore autmomation

Question

Post reply

Restore autmomation

ramyours2003

SSChampion

Points: 12266
More actions
January 26, 2022 at 8:22 pm

#3977619

i need to create sql job which perform automate restore of the databases with the latest full backup . in one of my server full backup is scheduled on daily basis , we need to refresh the databases on weekly basis with the latest full backup . pls provide any query to copy the latest full backup(last full backup ) file instead of all backup files .

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Do you have any PII or other sensitive information on the original databases that you've backed up?
What are you using to do the original backups?
Does the server you want to do the restores actually have READ privs on the folder(s) that the backups live in?
Are you allowed to use xp_CmdShell (which will make this a whole lot easier)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramyours2003 SSChampion Points: 12266 More actions · Answer 2

We are using the sql agent job to take the local drive backup of all the databases on the source and having sufficient permissions on the destination server .xp_CmdShell is enabled on both the servers to copy the files .Pls provide the query for coping of the last full backup of all the databases to the destination server .

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

ramyours2003 wrote:

We are using the sql agent job to take the local drive backup of all the databases on the source and having sufficient permissions on the destination server .xp_CmdShell is enabled on both the servers to copy the files .Pls provide the query for coping of the last full backup of all the databases to the destination server .

Ok... so what is the directory structure and database names that you want to restore from that structure and what naming convention do you have for the file names so that we can actually find the files for the databases you actually want to restore?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramyours2003 SSChampion Points: 12266 More actions · Answer 4

The backup file name like database_yyyymmdd.bak .

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

**** NOTICE!!!! Please see Scott's warning at https://www.sqlservercentral.com/forums/topic/restore-autmomation#post-3979292 on this subject below and my post right after his. I only use this method on "test" boxes and didn't stress what could happen if the once in a lifetime failure occurs between the SINGLE_USER and the MULTI-USER commands that I use in the code below. You should start with the OFFLINE/ONLINE/DROP method that Scott has posted first and resort to the SINGLE/MULTI user method only on a test/Dev system and only if you experience the issues that I had, which are rare in most environments.

Here's the "dity" I use on one of my test boxes. Read the "TODO" stuff in the comments at the top for what you'll need to do to make it work for you.

Also note that I'm rather rigorous about keep logical names and file names in the particular format/naming that's in the code. You may need to do a little work or make it so you read the header from the BAK file you find (this code always finds the latest one and doesn't check to see if it's in the process of being backed up) to get the correct file info. Of course, that will require a bit of dynamic SQL.

You've been working with SQL for quite a while. You should have already had something similar to do occasional restores to make sure that your databases actually will restore. I automatically restore several databases with some more complete code than this but figure that I don't know your environment and you have to have some of the fun. 😀 If you make money doing this, send me some. 😀

--=====================================================================================================================
--      Presets
-- Todo... Look for everything that contains "DbNameToRestore" and change the name there.
-- Todo... Look for "todo" for other things you may need to change.
--=====================================================================================================================
--===== Make sure that we're NOT in the database that we want to restore.
    USE master
;
--===== Local Variables
     -- /s = Include SubDirectories. There shouldn't be any but it also enables FULL PATH for the current directory.
     -- /b = Bare-bones mode where only the path (FULL PATH in this case) is returned.
     -- /o = "Order by"
     -- -d = Descending order by modified date (default)
DECLARE  @DirCmd VARCHAR(500) = 'DIR "\\MachineNameHere\PathToBakFiles\*.bak" /s /b /o-d'  --TODO... changes required here
        ,@RestoreFileName NVARCHAR(500)
        ,@DbName SYSNAME = 'DbNameToRestore'
;
--===== Create the table to store the results from the @DirCmd execution.
   DROP TABLE IF EXISTS #DirResult;
 CREATE TABLE #DirResult
        (
         RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
        ,RestoreFileName NVARCHAR(500)
        )
;
--=====================================================================================================================
--      Get the latest backup file to restore
--=====================================================================================================================
--===== Execute the DIR command to get the list of the *.bak files in descending order by date.
     -- This list contains the FULL PATH UNC for each of the files.
     -- The latest file will have a RowNum = 1.
 INSERT INTO #DirResult WITH (TABLOCK)
        (RestoreFileName)
   EXEC xp_CmdShell @DirCmd
;
--===== Get the latest FULL PATH UNC for the file we want to restore.
     -- Because of the order of the result from the @DirCmd, the latest file is always the 
     -- first row in the #DirResult table.
 SELECT @RestoreFileName = RestoreFileName 
   FROM #DirResult
  WHERE RowNum = 1
;
RAISERROR('FILE FOUND...',0,0) WITH NOWAIT;
 SELECT RestoreFileName FROM #DirResult WHERE RowNum = 1;
--=====================================================================================================================
--      Do the unconditional drop/restore
--=====================================================================================================================
;
--===== If the database exists, boot everyone and everything out and drop the database.
RAISERROR('Working on DROP...',0,0) WITH NOWAIT;
     IF DB_ID(@DbName) IS NOT NULL
  BEGIN   --This won't work if the DB is in a restoring state. I don't have that problem.
          ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
          ALTER DATABASE [DBNameToRestoreHere] SET MULTI_USER;
           DROP DATABASE [DBNameToRestoreHere];
    END
;
--===== Do the restore with MOVEs of the files to the correct drives.
        RAISERROR('Working on RESTORE...',0,0) WITH NOWAIT;
RESTORE DATABASE [DBNameToRestoreHere] 
   FROM  DISK = @RestoreFileName
   WITH  FILE = 1
        ,MOVE N'DbNameToRestore'     TO N'V:\SQLData\DbNameToRestore.mdf' --todo possible changes for logical names and paths
        ,MOVE N'DbNameToRestore_Log' TO N'W:\SQLLOG\DbNameToRestore.ldf'
        ,NOUNLOAD,  REPLACE,  STATS = 1
        ,BUFFERCOUNT=17, MAXTRANSFERSIZE=1048576; --todo these settings work well on my machine. YMMV.
GO
--===== Rename the LOGICAL FILE NAMES (uncomment if needed)
  --ALTER DATABASE [DBNameToRestoreHere] MODIFY FILE (NAME=N'DbNameToRestore'    , NEWNAME=N'DbNameToRestore')
  --ALTER DATABASE [DBNameToRestoreHere] MODIFY FILE (NAME=N'DbNameToRestore_Log', NEWNAME=N'DbNameToRestore_Log')
--GO
--===== Set the database to the normal configuration for this server --todo change these settings as required
  ALTER DATABASE [DBNameToRestoreHere] SET RECOVERY SIMPLE WITH NO_WAIT;
  ALTER AUTHORIZATION ON DATABASE::[DBNameToRestoreHere] TO [SA]
  ALTER DATABASE [DBNameToRestoreHere] SET TRUSTWORTHY ON;
GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 6

Jeff Moden wrote:

--...
      ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
          ALTER DATABASE [DBNameToRestoreHere] SET MULTI_USER;
           DROP DATABASE [DBNameToRestoreHere];
    END
...

I'd strongly urge you not to set the db to SINGLE_USER. Remember, your task is not USEing that db. That means some other task could be the "single" user and you would be locked out. That is really annoying when it happens.

Instead, do this:

ALTER DATABASE [DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [DBName] SET ONLINE;

DROP DATABASE [DBName];

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 7

ScottPletcher wrote:

Jeff Moden wrote:
--...
      ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
          ALTER DATABASE [DBNameToRestoreHere] SET MULTI_USER;
           DROP DATABASE [DBNameToRestoreHere];
    END
...
I'd strongly urge you not to set the db to SINGLE_USER. Remember, your task is not USEing that db. That means some other task could be the "single" user and you would be locked out. That is really annoying when it happens.
Instead, do this:
ALTER DATABASE [DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE; ALTER DATABASE [DBName] SET ONLINE; DROP DATABASE [DBName];

There's no doubt that OFFLINE/ONLINE method is 100% safe and that's what I used to do and I apologize for not mentioning that. Thank you for bringing it up.

The reason why I resorted to the SINGLE_USER with an immediate switch back to MULTI_USER is method is because we have a couple of dev systems where there are a rather large number of connections being made at a very rapid rate and they managed to frequently beat the time between the ONLINE/DROP. I'm down to only one test box now and, so far, they've not been able to beat the time between the MULT-USER and Restore commands.

I agree that if you lose the connection between the SINGLE_USER and MULTI_USER commands then you'll have some serious "fun" trying to get a connection to the database back. On a Dev box, I don't mind taking the once in a lifetime chance and certainly wouldn't use this on a prod system if it ever became necessary. There, I'd use the OFFLINE/ONLINE method and resort to the SINGLE/MULT user method only if it suffered a similar issue.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)