BACKUP and RESTORE GENERATE SCRIPTS
Below scripts dont backup or restore the databases. It will generate the scripts that will help DBA job
easier to copy the database from one server to other server. We have to run the script in source server.
It will be helpful when we have to migrate N of dtabases or database with more files. Press CTRL+T to get
the text output. You can modify however you want. Use yellow color highlighted where condition to
filter the databases.
Generate Backup Script:
-- PRESS CTRL+T to generate the scripts
set nocount on
declare @backupfilepath nvarchar(400), @backuptype smallint
set @backupfilepath = 'D:\DBA\'
set @backuptype = 1 -- 1 for FULL backup, 2 for DIFFERENTIAL backup, 3 for LOG backup
declare @dbname nvarchar(300)
declare dbnamecursor cursor for
select name
from sys.sysdatabases
where dbid > 4
open dbnamecursor
fetch next from dbnamecursor into @dbname
while(@@FETCH_STATUS = 0)
begin
if @backuptype = 1
begin
print 'BACKUP DATABASE ['+@dbname+']'
print 'TO DISK = N'''+@backupfilepath+@dbname+'.bak'''
end
if @backuptype = 2
begin
print 'BACKUP DATABASE ['+@dbname+']'
print 'TO DISK = N'''+@backupfilepath+@dbname+'.diff'''
end
if @backuptype = 3
begin
print 'BACKUP LOG ['+@dbname+']'
print 'TO DISK = N'''+@backupfilepath+@dbname+'.trn'''
end
if @backuptype = 2
print 'WITH COMPRESSION, DIFFERENTIAL'
else
print 'WITH COMPRESSION'
print 'GO'
fetch next from dbnamecursor into @dbname
end
close dbnamecursor
deallocate dbnamecursor
Generate Restore Script:
-- PRESS CTRL+T to generate the scripts
set nocount on
declare @datafilepath nvarchar(400), @logfilepath nvarchar(400),@backupfilepath nvarchar(200)
declare @isnorecovery bit, @iscopytosecondary bit
set @datafilepath = 'D:\SQL\'
set @logfilepath = 'D:\SQL\'
set @backupfilepath = 'D:\DBA\'
set @isnorecovery = 1 -- 1 for norecovery , 0 for recovering the database while restoring
set @iscopytosecondary = 1 -- 1 in case you want to use same file path, 0 for above data & log file path
declare @dbname nvarchar(200), @filetype smallint,@logicalname nvarchar(200), @filename
nvarchar(200)
declare dbnamecursor cursor for
select name
from sys.sysdatabases
where dbid > 4
declare @dbfiles table (id int identity(1,1),dbid int,filetype smallint, logicalname
nvarchar(200),physicalname nvarchar(350))
insert into @dbfiles
select database_id,type,name,physical_name
from sys.master_files
declare @min int, @max int
open dbnamecursor
fetch next from dbnamecursor into @dbname
while(@@FETCH_STATUS = 0)
begin
print 'RESTORE DATABASE ['+@dbname+']'
print 'FROM DISK = N'''+@backupfilepath+@dbname+'.bak'''
print 'WITH FILE = 1,'
select @min = min(id), @max = max(id)
from @dbfiles
where dbid = db_id(@dbname)
while(@min <= @max)
begin
--select @filetype = filetype, @logicalname = logicalname, @filename =
RIGHT(physicalname, CHARINDEX('\', REVERSE(physicalname)) -1)
select @filetype = filetype, @logicalname = logicalname, @filename = physicalname
from @dbfiles
where id = @min and dbid = db_id(@dbname)
if @filetype = 0
if @iscopytosecondary =1
print 'MOVE N'''+@logicalname+''' TO N'''+@filename+''','
else
print 'MOVE N'''+@logicalname+''' TO
N'''+@datafilepath+RIGHT(@filename, CHARINDEX('\', REVERSE(@filename)) -1)+''','
else if @filetype = 1
if @iscopytosecondary =1
print 'MOVE N'''+@logicalname+''' TO N'''+@filename+''','
else
print 'MOVE N'''+@logicalname+''' TO
N'''+@logfilepath+RIGHT(@filename, CHARINDEX('\', REVERSE(@filename)) -1)+''','
set @min = @min + 1
end
if @isnorecovery =1
print 'NORECOVERY,'
else
print 'RECOVERY,'
print 'NOUNLOAD, STATS = 10'
print 'GO'
fetch next from dbnamecursor into @dbname
end
close dbnamecursor
deallocate dbnamecursor
