Backup & Restore from TSQL

Database Backup and Restore from TSQL

For Native Backup

Database MYDatabase has full backup DatabaseName.bak. The database can be restored using following two steps.


Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY

FROM DISK = 'D:\Backup\DatabaseName.bak'

GO

Step 2: Use the values in the LogicalName Column in following Step.
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE MYDatabase
FROM DISK = 'D:\Backup\DatabaseName.bak'
WITH MOVE 'MDF_LogicalName' TO 'D:\MSSQL\Data\DBFileName_Data.mdf',
MOVE 'LDF_LogicalName' TO 'D:\MSSQL\Log\DBFileName_Log.ldf'

ALTER DATABASE YourDB SET MULTI_USER
GO

To backup database

Backup database Database_Name to Disk='D:\Backup\DatabaseName.bak' with stats=10
===============================================================
For LiteSpeed

--To Check the logical name of the file

exec master.dbo.xp_restore_filelistonly @filename = 'path of the database backup.bak'

i.e logical name like- Database_Data, Database_Log

--To Find the path where mdf and ndf resides for the said database.

--select * from sysaltfiles

sp_helpdb 'DatabaseName'

--To Restore database from given backup file

exec master.dbo.xp_restore_database @database = N'DatabaseName',

@filename = N'path of the database backup.bak',

@with = N'RECOVERY', @with = N'NOUNLOAD', @with = N'STATS = 10',

@with = N'MOVE N'' Database_Data '' TO N''N:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DatabaseName.mdf''',

@with = N'MOVE N'' Database_Log '' TO N''K:\Microsoft SQL Server\MSSQL\Logs\ DatabaseName.ldf'''

===============================================================
For SQL Safe

Backup Database

exec master..xp_ss_backup

@database = 'DatabaseName', @filename = 'D:\Backup\DatabaseName_FUll_Date.safe', @init = 1
, @compressionlevel = 'isize', @threads = 3

To verify backup file
xp_ss_listfiles @filename='D:\DB_Backup\DatabaseName_FUll_Date.safe'

Restore Database

exec master..xp_ss_restore
@database = 'Database_Name',
@filename='D:\DB_Backup\DatabaseName_FUll_Date.safe',
@withmove = 'DB_MDF_LOgicalName D:\MS SQL Server\Data\DBName.mdf'
,@withmove = 'DB_LDF_logicalName_Log F:\MS SQL Server\Logs\DBName.ldf'
,@withmove = 'DB_LDF_logicalName_Log1 F:\MS SQL Server\Logs\DBName1.ldf'
,@withmove = 'DB_Catalog_FT E:\MS SQL Server\FText\DB_Catalog_Production_FT'
,@replace = 1

==================================================================
Below are method to find orphan User and Way to fix it
--To Verify User SID matches system SID

sp_change_users_login 'report'

--If you find any Orphen user with SYSTEM SID, then we can fix with Following Command

sp_change_users_login 'update_one','username','username'

==================================================================

Query to find out Estimated Time, Percentage, Elapsed time of a RESTORE DATABASE/BACKUP DATABASE

SELECT r.session_id ,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]

,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2

,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r

WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')