HowTo restore from Prod to localDB

Last updated: 2016-11-22

This blog is shows howto backup SQL server from production and restore it to localDB

Backup production

Backup.sql:

use master
-- Create below with SSMS backup wizard
BACKUP DATABASE [MyDB]
TO DISK = N'G:\PROD_backup\Logs\MYDBHOTELSERVER$DB_PROD\MyDB\LOG\MYDBHOTELSERVER$DB_PROD_MyDB_LOG_20160817_020345.trn' WITH
	NOFORMAT, NOINIT,
	NAME = N'MyDB-Full Database Backup',
	SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- Save backup to a fileshare you have access to
BACKUP DATABASE [MyDB]
TO DISK = '\\myshare\Backup\MyDB_prod20160817.bak'

Restore to localDB

Restore.sql

-- Restore from prod to localDB
-- Do first delete target DB (MyDB) if it exist - otherwise you also need WITH REPLACE option --https://technet.microsoft.com/en-us/library/ms191315(v=sql.105).aspx
-- First print logical files
USE [master]
RESTORE FILELISTONLY
FROM DISK = N'\\myshare\backup\MyDB_prod20160822.bak'
GO
-- then do:
-- Restore logical files from bak to phys files
-- Do first Create folder C:\Users\myuserid\AppData\Local\Microsoft\VisualStudio\SSDT\MyDB
USE [master]
RESTORE DATABASE [MyDB] FROM  DISK = N'\\myshare\backup\MyDB_prod20160822.bak' WITH  FILE = 1,
	MOVE N'MyDB_dat' TO N'C:\Users\myuserid\AppData\Local\Microsoft\VisualStudio\SSDT\MyDB\MyDB_data_1.mdf',
	MOVE N'MyDB_log' TO N'C:\Users\myuserid\AppData\Local\Microsoft\VisualStudio\SSDT\MyDB\MyDB_log.ldf',  NOUNLOAD,  STATS = 5
GO

-- Optional: Detach and Re-attach - To avoid "Recovery Pending" state
-- Detach the just-created-DB
-- http://stackoverflow.com/questions/13126726/how-do-i-copy-sql-server-2012-database-to-localdb-instance
EXEC master.dbo.sp_detach_db @dbname = N'MyDB'
GO

-- Re-attach - SSDT perform an upgrade of the data files, that might be in an older format
CREATE DATABASE [MyDB] ON
( FILENAME = N'C:\Users\myuserid\AppData\Local\Microsoft\VisualStudio\SSDT\MyDB\MyDB_data_1.mdf' ),
( FILENAME = N'C:\Users\myuserid\AppData\Local\Microsoft\VisualStudio\SSDT\MyDB\MyDB_log.ldf' )
 FOR ATTACH
GO

The End

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: