SQLCMD – Database Backup and Restore – Automation – Quickest and Simplest method

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.

https://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.

Image

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 –

Image

After –

Image

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Backup and Restore, Robocopy, SETVAR, SQL, SQLCMD, Uncategorized and tagged , , , . Bookmark the permalink.

7 Responses to SQLCMD – Database Backup and Restore – Automation – Quickest and Simplest method

  1. kyo says:

    Hello Prashant
    It was just phenomenal, Can you provide the same with the powershell to automate the db Refreshes. Here’s the scenario
    1)We used to take backups that exist on the same folder of around more than 10 databases
    2)script out users,privileges -note-the output of this should be in text format.
    3)Restore the multiple databases from that backup folder
    4)Execute the scripts of step 2

    Any help would be great !

  2. kyo says:

    Hello PJ,
    Sorry for the delay though, this was the perfect thing that I was looking for but this doesn’t not include the 2)script out users,privileges -note-the output of this should be in text format.
    of the step 2 of my requirement and then later we can execute the results of this scripts to map the users again with the appropriated privileges.

    Any help would be much appreciable.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s