Example of Full and Tlog backup and restore script
-- Full and Transaction Log Backup
use master
go
EXEC master.dbo.xp_backup_database
@database = 'pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @desc = 'FULL BACKUP TEST'
, @init =1
, @encryptionkey = 'password'
go
EXEC master.dbo.xp_backup_log
@database = 'pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @desc = 'LOG BACKUP TEST'
, @init = 0
, @encryptionkey = 'password'
go
EXEC master.dbo.xp_backup_log
@database = 'pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @desc = 'LOG BACKUP TEST'
, @encryptionkey = 'password'
-- Full Restore with Transaction Files
use master
go
exec master.dbo.xp_restore_database
@database='pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @encryptionkey='password'
, @filenumber=1
, @with='NORECOVERY'
go
exec master.dbo.xp_restore_log
@database='pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @encryptionkey='password'
, @filenumber=2
, @with='NORECOVERY'
go
EXEC master.dbo.xp_restore_log
@database = 'pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @filenumber = 3
, @encryptionkey = 'password'
, @with='RECOVERY'
go
-- Full Restore with Transaction Files to a different database
use master
go
exec master.dbo.xp_restore_database
@database='Different_pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @encryptionkey='password'
, @filenumber=1
, @with = N'REPLACE'
, @with = N'NORECOVERY',
, @with = N'MOVE N''SLS_data'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SLS_data.mdf'''
, @with = N'MOVE N''SLS_log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SLS_log.ldf'''
go
exec master.dbo.xp_restore_log
@database='Different_pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @encryptionkey='password'
, @with = N'REPLACE'
, @with = N'NORECOVERY',
, @with = N'MOVE N''SLS_data'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SLS_data.mdf'''
, @with = N'MOVE N''SLS_log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SLS_log.ldf'''
go
EXEC master.dbo.xp_restore_log
@database='Different_pubs'
, @filename = 'C:\mssql\tlog\SLS_test.bak'
, @filenumber = 3
, @encryptionkey = 'password'
, @with = N'REPLACE'
, @with = N'RECOVERY',
, @with = N'MOVE N''SLS_data'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SLS_data.mdf'''
, @with = N'MOVE N''SLS_log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SLS_log.ldf'''
go
