Scribd
Upload
20 views

Script To Generate Backup and Restore TSQL Commands

The document provides scripts to generate backup and restore scripts for databases. The backup script generates FULL, DIFFERENTIAL or LOG backup scripts based on the @backuptype parameter. It loops through databases and prints the BACKUP commands with the file path. The restore script generates RESTORE commands, handles data and log file paths based on @iscopytosecondary, and adds NORECOVERY or RECOVERY options based on @isnorecovery. Both scripts filter databases where dbid > 4 and use cursors to loop through databases.

Uploaded by

mohammed akbar ali
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views

Script To Generate Backup and Restore TSQL Commands

The document provides scripts to generate backup and restore scripts for databases. The backup script generates FULL, DIFFERENTIAL or LOG backup scripts based on the @backuptype parameter. It loops through databases and prints the BACKUP commands with the file path. The restore script generates RESTORE commands, handles data and log file paths based on @iscopytosecondary, and adds NORECOVERY or RECOVERY options based on @isnorecovery. Both scripts filter databases where dbid > 4 and use cursors to loop through databases.

Uploaded by

mohammed akbar ali
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

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

You might also like