In today’s world we rely more on third party tools to do a Backup and Restore of databases. Most of us still use SQL Native method to do a Database Backup and Restoration. This post illustrates the quickest way of taking backup and restore it on the destination server. I’m using Robocopy utility to perform the data transfer activity. Please refer below one of my previous post on Robocopy.

http://sqlpowershell.wordpress.com/2013/07/05/sqlcmd-quick-copy-of-files-using-robocopy/

By default, SQLCMD mode is not enabled in the Query Editor. You can enable scripting mode by clicking the SQLCMD Mode icon in the toolbar or by selecting SQLCMD Mode from the Query menu. Please refer this link for more information. Go to SSMS Menu -> Query ->Select SQLCMD.

copy and paste the below code SSMS and Modify the below variables as per requirement . I would rather say it to download it from the below link

T-SQL Code is here –  Backup and Restore

:setvar DATABASE UAT2010_Search
:setvar SOURCE HQSPDBSU01
:setvar DESTINATION HQSPDBSU02
:setvar BACKUPPATH g:\MSSQL
:setvar RESTOREPATH g:\MSSQL
:setvar LOGICALDATANAME UAT2010_Search
:setvar LOGICALLOGNAME UAT2010_Search_Log
:setvar RESTOREDATAPATH “G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA”
:setvar RESTORELOGPATH “G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA”
:setvar COPYPATH g$\MSSQL
:setvar Timeout 10

—Before Execution

:CONNECT $(SOURCE)

select * from sys.databases where name=’$(DATABASE)’

Go

:CONNECT $(DESTINATION)

select * from sys.databases where name=’$(DATABASE)’

SET NOCOUNT ON

GO

:CONNECT $(SOURCE)

Go

print ‘*** Take full backup of Source database $(DATABASE) with copy_only option***’

IF (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.50′ and SERVERPROPERTY(‘EngineEdition’)=3) OR (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.00′ and SERVERPROPERTY(‘EngineEdition’)in(2,3))

BEGIN

– Compression Option is set

BACKUP DATABASE $(DATABASE) TO DISK = ‘$(BACKUPPATH)\$(DATABASE).bak’

WITH COPY_ONLY, NOFORMAT, INIT, NAME = ‘$(DATABASE) Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10,COMPRESSION

END

ELSE

BEGIN

–Backups are not compressed for older versions

BACKUP DATABASE $(DATABASE) TO DISK = ‘$(BACKUPPATH)\$(DATABASE).bak’

WITH COPY_ONLY, NOFORMAT, INIT, NAME = ‘$(DATABASE) Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

END

GO

—-2. Copy the files from Source to Destination -

print ‘*** Copy database $(DATABASE) from Source server $(Source) to Destination server $(destination) ***’

!!ROBOCOPY $(BACKUPPATH)\ \\$(Destination)\$(COPYPATH) $(DATABASE).*

GO

—–3. Restore database to Destination

print ‘*** Restore full backup of database $(DATABASE) ***’

:CONNECT $(Destination)

GO

RESTORE DATABASE $(DATABASE)

FROM disk = ‘$(RESTOREPATH)\$(DATABASE).bak’

WITH RECOVERY, NOUNLOAD, STATS = 10,REPLACE,

MOVE ‘$(LogicalDataName)’ TO

‘$(RestoreDataPath)\$(LogicalDataName).mdf’,

MOVE ‘$(LogicalLogName)’

TO ‘$(RestoreDataPath)\$(LogicalLogName).ldf’

GO

– After Execution

:CONNECT $(SOURCE)

select * from sys.databases where name=’$(DATABASE)’

Go

:CONNECT $(DESTINATION)

select * from sys.databases where name=’$(DATABASE)’

Before -

After -