5/10/2023 0 Comments Restore quiterss from backup![]() ![]() MOVE ”DatabaseLogicName_log” TO ”’ + + ”’, NOUNLOAD, REPLACE, STATS = 5′) ĮXEC(‘ALTER DATABASE SET MULTI_USER’) To be able to do ALTER on a database specified in a variable we encapsulate that command in the EXEC function.ĮXEC(‘ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE’) ĮXEC(‘RESTORE DATABASE FROM DISK = ”’ + + ”’ WITH FILE = 1, Before we begin that operation we put the database in SINGLE_USER mode to make sure that we don’t have any locks or open connections to the destination database. SELECT = f.physical_name FROM sys.master_files f RIGHT JOIN sys.databases d ON f.database_id = d.database_id This information can be found in the sys database. Since we are restore a backup for a different database then the destination we need this information to restore to the correct files and not overwrite the source database instead. Then we need to get the destination database actual filenames on disc. WHERE AND type=’D’ ORDER BY backup_start_date DESC) Then we need the actual backup filename that we can grab from the msdb database. Get latest database backup for the FromDatabase and is pretty self explanatory, name of the source and destination databases. The script can be downloaded from Github – T-SQL Automatic restore of latest backupįirst we declare and set some variables. The script grabs the latest backup an makes the restore. Solution to this problem is a simple T-SQL script that you can put in a maintenance plan, to run every night or just on schedule as you please. The reason why is that the automated backups in a Microsoft SQL Server maintenance plan have somewhat unpredictable names. If you don’t have any third party solution for backups where this feature is built in it can be a bit tricky. ![]() You would prefer to have this done during the night right after the backups run. Depending on your system (database) size this can be time consuming. A common need is to restore the latest production backup to a test system or user acceptance test system on a regular basis.
0 Comments
Leave a Reply. |