Database Backup and Restore SQL Scripts

Database backup steps:

--Full backup
BACKUP DATABASE [DB_NAME] TO  DISK = N'D:\Backup\Test_DB.bak'  WITH FORMAT, STATS = 10
GO
--TransactionLog backup
BACKUP LOg [DB_NAME] TO  DISK = N'D:\Backup\logs\Test_DB.trn' WITH FORMAT, STATS = 10
GO


Restore database steps:

Good article
http://blog.sqlauthority.com/2009/07/14/sql-server-backup-timeline-and-understanding-of-database-restore-process-in-full-recovery-model/

--Display logical names
RESTORE FILELISTONLY  FROM DISK = D:\MSSQL2K5\DBANME_backup.bak'

--To find the logical and physical file names if the DB exis ton instance
SELECT name, filename FROM SYSFILES

--set database in single user mode
ALTER DATABASE RELOAD_DEV SET SINGLE_USER WITH  ROLLBACK IMMEDIATE

-- check online users for that DB
sp_who2

-- STATS = 10:  will display the 10% database restore completed.It could be 10,20...any number.



**************************** Restore ************************************

*****--With Recovery  means you can not restore/add Tlog backup next.
When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.


*****--with NORecovery means you can restore/add Tlog backup next.  --FullBackup then Tlog backup
**** Restore Tlog you can use wiht "Recovery"



Restore database TestDB2 from disk='D:\temp\Restoretest\TestDB_2142013133.bk' with
move'TestDB' to 'D:\temp\Restoretest\TestDB2.mdf',
move 'TestDB_log' to 'D:\temp\Restoretest\TestDB_Log.ldf',
Norecovery,stats=10


Restore log TestDB2 from disk='D:\temp\Restoretest\TestDB__log_2142013134.trn' with
move'TestDB' to 'D:\temp\Restoretest\TestDB2.mdf',
move 'TestDB_log' to 'D:\temp\Restoretest\TestDB_Log.ldf',
recovery,stats=10

****************************Restore end ***********************************

--http://www.mssqltips.com/sqlservertip/1075/copyonly-backups-with-sql-server-2005/
Restore database with copyonly option.


--If database is in restoring mode then run below,if you used Restore script as NORECOVER mode
   RESTORE DATABASE Test_DB  RECOVERY;
Select an option for the Recovery state box. This box determines the state of the database after the restore operation
• RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
• RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
--TRAN Log backups restore:
   NOTE: Restore the full backup with NORECOVERY option and then restore the transaction log backup.

--5. To make database to multiuser mode
    ALTER DATABASE RELOAD_DEV SET MULTI_USER
    GO
--6.Change the database owner to 'sa'