Creating Dynamic Backup and Restore Scripts for Databases in SQL Server

Hello everyone,

In this article, I will try to give information about creating dynamic Backup and Restore Scripts for databases in SQL Server.

In SQL Server, in some cases you may want to create dynamic Backup and Restore Scripts for databases.

You can easily do this using the code below.

select ('--' + a.databasename + '
print ''
========================================
Backup ' + a.databasename + '
========================================
''
Go
BACKUP DATABASE ' + a.databasename + ' TO DISK=''D:\yedek\' + a.databasename
        + '.bak'' WITH INIT, STATS = 10, COPY_ONLY, COMPRESSION
GO'
       ) as Script_Backup,
       ('--' + a.databasename + '
print ''
========================================
Restoring ' + a.databasename + '
========================================
''
Go
USE master
Go
RESTORE DATABASE ' + a.databasename + '
FROM DISK = ''\\yedek' + a.databasename + '.bak''' + ' WITH
MOVE ''' + a.ficherodatos + ''' TO ''D:\yedek\' + a.databasename + '.mdf'',' + '
MOVE ''' + b.ficherolog + ''' TO ''D:\yedek\' + a.databasename + '_log.ldf'',' + 'STATS = 10, RECOVERY, REPLACE
Go
'      ) as Script_Restore
from
(
    select db.name databasename,
           mf.name ficherodatos
    FROM sys.master_files mf
        INNER JOIN sys.databases db
            ON db.database_id = mf.database_id
    where mf.database_id > 4
          and type_desc = 'ROWS'
) a ,
(
    select db.name databasename,
           mf.name ficherolog
    FROM sys.master_files mf
        INNER JOIN sys.databases db
            ON db.database_id = mf.database_id
    where mf.database_id > 4
          and type_desc = 'LOG'
) b
where a.databasename = b.databasename
      and a.databasename IN ( 'NORTHWND' ) --You can write multiple database names here.
order by a.databasename

When you run the above code, you will see a result similar to the one below.

As you can see, we have created dynamic Backup and Restore Scripts for databases.

Good luck to everyone in business and life.