Labels

Backup \ Restore Scripts For Litespeed in SQL Server




Below are scripts for taking backup or restore database when you have litespeed as third party tool. This tool was considered very good till SQL server doesn’t introduce compression. Sice compression is introduce , tools like litespeed and redgate are loosing its shine for normal backup restore operations.



Backup Scripts for SQL Server with litespeed


/**** Full Backup ****/
execute master.dbo.xp_backup_database  
@database = 'database name',  
@filename = 'Backup path\backup file name.bak',  
@init = 1,  
@compressionlevel = 4

/**** Differential Backup ****/
execute master.dbo.xp_backup_database 
@database = 'database name', 
@filename = 'Backup path\backup file name.bak', 
@init = 1,  @compressionlevel = 4, 
@with =  differential

/**** Transaction Log Backup ****/
execute master.dbo.xp_backup_log 
@database = 'database name', 
@filename = 'Backup path\backup file name.trn', 
@init = 1,  @compressionlevel = 4

/**** Filegroup Backup ****/
execute master.dbo.xp_backup_database  
@database = 'database name', 
@filename = 'Backup path\backup file name.bck', 
@init = 1,
@compressionlevel = 4, 
@filegroup = 'filegroupname'


Restore Scripts for SQL Server with litespeed



/**** Script to check the data and log file information from backup file ****/
exec master.dbo.xp_restore_filelistonly
@filename ='BackupPath\BackupFileName.bak'
GO

/**** Script to check the backup file header information ****/
exec master.dbo.xp_restore_headeronly
@filename ='BackupPath\BackupFileName.bak'
GO

/**** Script to check if the backup file is valid or not ****/
EXEC master.dbo.xp_restore_verifyonly
@filename ='BackupPath\BackupFileName.bak' 
GO

/**** Script to restore database using Full backup with the default options ****/
exec master.dbo.xp_restore_database
@database = 'dbname',
@filename = 'BackupPath\BackupFileName.bak'
GO

/**** Script to restore database using Full backup with file move option ****/
exec master.dbo.xp_restore_database
@database = 'dbname',
@filename = 'BackupPath\BackupFileName.bak',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"' 
GO

/**** Script to restore database using Full backup with replace option ****/
exec master.dbo.xp_restore_database
@database = 'dbname',
@filename = 'BackupPath\BackupFileName.bak',
@with = 'replace', @with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'
GO

/**** Script to restore Full backup with no recovery ****/
exec master.dbo.xp_restore_database
@database = 'dbname',
@filename = 'BackupPath\BackupFileName.bak',
@with = 'replace', @with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"',
@with='NORECOVERY' 
GO

/**** Script to restore log backup with no recovery ****/
EXEC master.dbo.xp_restore_log
@database = 'dbname',
@filename = 'BackupPath\BackupFileName.trn',
@with ='NORecovery' 
GO

/**** Script to restore log backup with recovery ****/
EXEC master.dbo.xp_restore_log
@database = 'dbname',
@filename = 'BackupPath\BackupFileName.trn',
@with ='Recovery' 
GO

/**** Script to do point in time recovery ****/
EXEC master.dbo.xp_restore_log
 @database = 'dbname',
 @filename = 'BackupPath\BackupFileName.trn',
 @with ='Recovery',
 @with = 'STOPBEFOREMARK = LogMark'
GO

 


5 comments:
Write comments
  1. 3 gb database size taking 45 mint huge time .What need to check normaly it should take 15 to 20 mints please suggest

    ReplyDelete
    Replies
    1. Check db checked there is no error. Checked storage health there are enough space . But still taking 40 minutes. Back up size 2.77 gb and data mdf and ldf files 8 gb is there

      Delete
  2. How do I store DB backup on S3? and how do I restore DB from backup source at S3? What is the command respectively?

    ReplyDelete
  3. how can i run .sql file against to sql server instnaces

    ReplyDelete

Please do not enter spam links

Services

More Services