{"id":832,"date":"2022-12-09T10:14:14","date_gmt":"2022-12-09T16:14:14","guid":{"rendered":"https:\/\/www.sqlfreelancer.com\/blog\/?p=832"},"modified":"2022-12-09T18:42:57","modified_gmt":"2022-12-10T00:42:57","slug":"quick-backup-restore-script","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/quick-backup-restore-script\/","title":{"rendered":"Quick Backup\/Restore Script"},"content":{"rendered":"\r\n<p><\/p>\r\n<p>Throughout the years I\u2019ve probably performed hundreds if not thousands of migrations or \u201crefreshes\u201d to lower environments. It\u2019s pretty simple to run a quick maintenance plan or SQL job to backup all databases, but the more complex piece is creating a restore script to point to that backup location and the backup name. It can be done, but it takes a little longer, IMO. A lot of backup file names contain numeric characters such as date or time of backup.<\/p>\r\n<p>\r\n\r\n<\/p>\r\n<p><a href=\"https:\/\/www.linkedin.com\/in\/johnmorehouse\/\">John Morehouse<\/a> created a dynamic script that simplifies everything and it can be easily modified to fit particular needs. I\u2019ve had this script bookmarked for years and use it at least once a week. Kudos to John for sharing this with the SQL community. I\u2019ll link the script below, but here\u2019s a quick snippet.<\/p>\r\n<pre class=\"wp-block-code\"><code>DECLARE @date CHAR(8)\r\nSET @date = (SELECT CONVERT(char(8), GETDATE(), 112))\r\n\r\nDECLARE @path VARCHAR(125)\r\nSET @path = '\\\\UNCPath\\Folder\\'\r\n\r\n;WITH MoveCmdCTE ( DatabaseName, MoveCmd )\r\n          AS ( SELECT DISTINCT\r\n                        DB_NAME(database_id) ,\r\n                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''\r\n                                        + CASE Type\r\n                                            WHEN 0 THEN ' TO ''D:\\SQLData\\'\r\n                                            ELSE ' TO ''E:\\SQLTLogs\\'\r\n                                          END\r\n                                        + REVERSE(LEFT(REVERSE(physical_name),\r\n                                                       CHARINDEX('\\',\r\n                                                              REVERSE(physical_name),\r\n                                                              1) - 1)) + ''''\r\n                               FROM     sys.master_files sm1\r\n                               WHERE    sm1.database_id = sm2.database_ID\r\n                        FOR   XML PATH('') ,\r\n                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd\r\n               FROM     sys.master_files sm2\r\n  )\r\nSELECT\r\n\t'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',\r\n\t'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + movecmdCTE.MoveCmd\r\nFROM sys.databases d\r\n\tINNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename\r\nWHERE d.name LIKE '%DatabaseName%'\r\nGO\r\n<\/code><\/pre>\r\n<p>\r\n\r\n<\/p>\r\n<p>To change the backup\/restore path you can modify this section of code to fit your needs:<\/p>\r\n<p>\r\n\r\n<\/p>\r\n<pre class=\"wp-block-code\"><code>SET @path = '\\\\UNCPath\\Folder\\'<\/code><\/pre>\r\n<p>\r\n\r\n<\/p>\r\n<p>Another modification I perform initially is changing the where clause, for example, if I don\u2019t want system databases I can change:<\/p>\r\n<p>\r\n\r\n<\/p>\r\n<pre class=\"wp-block-code\"><code>WHERE d.name LIKE '%DatabaseName%'<\/code><\/pre>\r\n<p>\r\n\r\n<\/p>\r\n<p>to<\/p>\r\n<p>\r\n\r\n<\/p>\r\n<pre class=\"wp-block-code\"><code>WHERE d.database_id &gt; 4<\/code><\/pre>\r\n<p>\r\n\r\n<\/p>\r\n<p>A lot of times I don\u2019t need to move the data and log files to a new location because I\u2019m overwriting other databases so I just comment all of the MoveCmdCTE CTE code out:<\/p>\r\n<p>\r\n\r\n<\/p>\r\n<pre class=\"wp-block-code\"><code>DECLARE @date CHAR(8)\r\nSET @date = (SELECT CONVERT(char(8), GETDATE(), 112))\r\n\r\nDECLARE @path VARCHAR(125)\r\nSET @path = '\\\\UNCPath\\Folder\\'\r\n\r\n--;WITH MoveCmdCTE ( DatabaseName, MoveCmd )\r\n--          AS ( SELECT DISTINCT\r\n--                        DB_NAME(database_id) ,\r\n--                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''\r\n--                                        + CASE Type\r\n--                                            WHEN 0 THEN ' TO ''D:\\SQLData\\'\r\n--                                            ELSE ' TO ''E:\\SQLTLogs\\'\r\n--                                          END\r\n--                                        + REVERSE(LEFT(REVERSE(physical_name),\r\n--                                                       CHARINDEX('\\',\r\n--                                                              REVERSE(physical_name),\r\n--                                                              1) - 1)) + ''''\r\n--                               FROM     sys.master_files sm1\r\n--                               WHERE    sm1.database_id = sm2.database_ID\r\n--                        FOR   XML PATH('') ,\r\n--                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd\r\n--               FROM     sys.master_files sm2\r\n--  )\r\nSELECT\r\n\t'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',\r\n\t'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' --+ movecmdCTE.MoveCmd\r\nFROM sys.databases d\r\n\t--INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename\r\nWHERE d.name LIKE '%DatabaseName%'\r\nGO<\/code><\/pre>\r\n<p>\r\n\r\n<\/p>\r\n<p>There&#8217;s a lot of option here and this script is quick and easy.<\/p>\r\n<p>\r\n\r\n<\/p>\r\n<p>Link to script: https:\/\/gist.github.com\/airtank20\/a826c6f37439482edd5070e8aaeb1ee1<\/p>\r\n<p><\/p>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[104,84],"tags":[],"class_list":["post-832","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-upgradesmigrations"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=832"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/832\/revisions"}],"predecessor-version":[{"id":837,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/832\/revisions\/837"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}