basic backup and Restore SQL Server database SQL scripts with same and different name

–Backup

USE [master]
GO

BACKUP DATABASE [DbName] TO DISK = N’\\server_name\folder_name\Db_BackUp_File_name.bak’ WITH NOFORMAT, NOINIT, NAME = N’DbName’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

–Restore

USE [master]
GO
Declare @sqlfilepath varchar(max)

set @sqlfilepath = ‘\\server_name\folder_name\Db_BackUp_File_name.bak’

RESTORE DATABASE [DbName] FROM DISK = @sqlfilepath WITH FILE=1, MOVE N’DbName’ TO N’L:\DATA\DbName.mdf’, MOVE N’DbName_Log’ TO N’Q:\Logs\DbName_log.ldf’, NOUNLOAD, STATS = 10

–Restore with different name, say with _07092016. We have to change at 3 different places

USE [master]
GO
Declare @sqlfilepath varchar(max)

set @sqlfilepath = ‘\\server_name\folder_name\Db_BackUp_File_name.bak’

RESTORE DATABASE [DbName_07092016] FROM DISK = @sqlfilepath WITH FILE=1, MOVE N’DbName’ TO N’L:\DATA\DbName_07092016.mdf’, MOVE N’DbName_Log’ TO N’Q:\Logs\DbName_log_07092016.ldf’, NOUNLOAD, STATS = 10

 

Advertisements

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