Friday, February 5, 2010

SQL Server Native Backup Command Vs LiteSpeed Backup Command

Litespeed for SQL Server is very good third party tool where we can take compressed backup up-to 8 level. Here is a list of sql queries for different kind of backups we can take from Litespeed and comparison with sql server native commands.
 
1. Backup Database With Init
Native Command:
BACKUP DATABASE MyDB 
TO DISK = 'C:\MSSQL\Backup\MyDB_Backup.BAK' 
WITH INIT

LiteSpeed Command:
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename='C:\MSSQL\Backup\MyDB_Backup.BAK' , 
@init= 1

2. Differential Backup
Native Command:
BACKUP DATABASE MyDB 
TO DISK= 'C:\MSSQL\Backup\MyDB_Backup.BAK' 
WITH DIFFERENTIAL

LiteSpeed Command:
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename = 'C:\MSSQL\Backup\MyDB_Backup.BAK' , 
@with = 'DIFFERENTIAL'

3. Filegroup Backup
Native Command:
BACKUP DATABASE MyDB 
FILEGROUP = 'PRIMARY' , 
TO DISK = 'C:\MSSQL\Backup\MyDB_Backup.BAK' 
WITH INIT

LiteSpeed Command:
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename= 'C:\MSSQL\Backup\MyDB_Backup.BAK' ,
@filegroup = 'PRIMARY' , 
@init= 1

4. Backup Database to Tape
Native Command:
BACKUP DATABASE MyDB TO TAPE = '\\.\TAPE0'

LiteSpeed Command:
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename='\\.\TAPE0' , 
@desc = 'Daily tape backup' , 
@format = 0 Multiple

5. Backup Devices (Striped Backup)
Native Command: 
BACKUP DATABASE MyDB 
TO DISK = 'C:\MSSQL\Backup\MyDB_Backup1.BAK' ,
DISK = 'D:\MSSQL\Backup\MyDB_Backup2.BAK' ,
DISK = 'E:\MSSQL\Backup\MyDB_Backup3.BAK' ,
INIT

LiteSpeed Command:
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename = 'C:\MSSQL\Backup\MyDB_Backup1.BAK' , 
@filename= 'D:\MSSQL\Backup\MyDB_Backup2.BAK' , 
@filename = 'E:\MSSQL\Backup\MyDB_Backup3.BAK' , 
@init = 1 

Note: @threads is ignored, LiteSpeed will use 1 thread per output device specified. 

6. Backup Database with Multiple Threads 
Native Command: 
(NOT SUPPORTED) 

LiteSpeed Command: 
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename = 'C:\MSSQL\Backup\MyDB_Backup.BAK' , 
@threads = 3 

7. Backup Database with Encryption 
Native Command: 
BACKUP DATABASE MyDB 
TO DISK= 'C:\MSSQL\Backup\MyDB_Backup.BAK' 
WITH INIT , 
PASSWORD= 'Password' 

Note: Encryption is not supported by the native SQL Server backup. The WITH PASSWORD option only requires a password to be supplied to restore the database device. Raw data can be obtained from the backup device using an ASCII text reader. 

LiteSpeed Command: 
EXEC master.dbo.xp_backup_database 
@database='MyDB' , 
@filename='C:\MSSQL\Backup\MyDB_Backup.BAK' , 
@init=1 , 
@encryptionkey='Password' 

8. Backup Transaction Log With Init 
Native Command: 
BACKUP LOG MyDB 
TO DISK = 'C:\MSSQL\Backup\MyDB_Backup.BAK' 
WITH INIT 

LiteSpeed Command: 
EXEC master.dbo.xp_backup_log 
@database = 'MyDB' , 
@filename='C:\MSSQL\Backup\MyDB_Backup.BAK' , 
@init = 1
how to to take backup through litespeed. how to take backup through sql script. how to restore database through litespeed. how to restore database through sql query. in sql server 2008 r2. in sql server 2012. in sql server 2005. in sql server 2000. sql server backup script. sql server litespeed backup script. litespeed restore commands. litespeed backup query. litespeed scripts. litespeed backup failure. litespeed restore failure. litespeed issues. lite speed backup. lite speed restore. litespeed benifits. litespeed usefull scripts. litespeed commands. lite speed queries. third party tools for sql server. third party tools for slq server 2012 backup and restore. third party software lite speed utility for sql server 2008. 2005. table restore. single table restore from backup. table backup and restore. how to do table restore from backup file. how to take single table backup. how to restore single table from old backup file. 

0 comments:

Post a Comment