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

 


7 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. 3 GB database does'nt take more than 5 mins normally.
      1. Run DBCC chekc db on your database
      2. Check Storage health
      3. Try to take backup in other drive and compare performance
      4. I hope db size is 3 GB not backup size after compression.

      Delete
    2. 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
    3. Mail me your no. @ saurabh.x.sinha@gmail.com
      Let me have more inputs to understand your environment

      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
    Replies
    1. Hi Mohammed, I am not aware what is S3 is it sum tool / softwre which store preserve backup.

      Ideally if its source, you have to publish link so that location is accessible to your destination sql server and then you can use restore command.

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

    ReplyDelete