We help IT Professionals succeed at work.

Create Offline Copy of SQL Server 2008

How do I create a copy of my production database on my local harddrive for testing purposes?
Comment
Watch Question

Top Expert 2010

Commented:
hope this helps :

http://stackoverflow.com/questions/2676754/easiest-way-to-make-copy-sql-server-db-to-test-db

We always use a back up and then restore it onto our test environment.
Hello,

This is the simplest, most flexible, and least invasive method. The steps needed to complete this task are easy and don’t necessarily have to be performed in the order we present them.

For all of these examples we’ll assume the database name is Mydatabase. Let’s assume you want to move the log file to drive D:\.

Open a query window in SQL Server Management Studio (SSMS) and type the following code:
ALTER DATABASE Mydatabase
MODIFY FILE (NAME = 'MydatabaseLog1', FILENAME = 'D:\MydatabaseLog1.ldf')
This command is easy. You’re telling SQL Server to modify the log file by its virtual name (“MydatabaseLog1”) and saying you want it mapped to the D:\MydatabaseLog1.ldf physical file.
Michael VasilevskySolutions Architect

Author

Commented:
OK I've backed-up my database, how do I restore it locally? SQL Server noob here
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
Assuming you've got the Management Studio installed locally, use that to restore the backup.  Here's how: http://msdn.microsoft.com/en-us/library/ms177429.aspx
David ToddSenior Database Administrator

Commented:
Hi mvasilevsky,

>> OK I've backed-up my database, how do I restore it locally? SQL Server noob here

Okay steps are:
1. Backup database - you've done this
2. Copy the resulting backup file to your workstation - not manditory but makes things easier
3. Look at the logical files within said backup file
4. Restore to your machine

HTH
  David
-- example using ManagementDW on my workstation

--== 1 Backup
backup database ManagementDW to disk = 'c:\data\tmp\ManagementDW_201112221040.bak' with stats

--== 2 copy - not needed as still on my workstation

--== 3 What logical files
restore filelistonly
from disk = 'c:\data\tmp\ManagementDW_201112221040.bak'
;

--== 4 restore
restore database ManagementDW_copy
from disk = 'c:\data\tmp\ManagementDW_201112221040.bak'
with
	-- replace as it is a foreign backup file to this server
	replace
	-- need to specify where to place and what to call the files
	-- _logical_ file names from step above
	, move 'ManagementDW_data' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\ManagementDW_copy_data.mdf'
	, move 'ManagementDW_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\ManagementDW_copy_log.ldf'
	-- want to see some progress
	, stats
;

Open in new window

Distinguished Expert 2019

Commented:
Use the information from the link to add the logins from the production to the offline/development system.
http://support.microsoft.com/kb/918992

Then following the instructions the others have provided you can restore the database/s and have the same credentials.
This enables you to have a complete test system and identical to the production.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

Please not that if you backup your database with code provided above your log chain will be broken due to new full backup. In some cases it might create a problem.

Please find following code:

--Backup database as a copy, without breaking log chain
--I assume that folder C:\BACKUP, C:\RESTORE and C:\RESTORE\FILESTREAM exists and SQL Server has access to them
BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'C:\BACKUP\AdventureWorks2008R2_backup.bak' WITH COPY_ONLY;
GO

--Check database file names and locations from backup file
RESTORE FILELISTONLY FROM DISK = 'C:\BACKUP\AdventureWorks2008R2_backup.bak';
GO

--Restore database to another name
--Please note that we need to provide different physical file names, logical filenames can be the same
--You can restore it also to default location
RESTORE DATABASE AdventureWorks2008R2_COPY FROM DISK = 'C:\BACKUP\AdventureWorks2008R2_backup.bak'
WITH 
MOVE 'AdventureWorks2008R2_Data' TO 'C:\RESTORE\AdventureWorks2008R2_COPY_Data.mdf',
MOVE 'AdventureWorks2008R2_Log' TO 'C:\RESTORE\AdventureWorks2008R2_COPY_Log.ldf',
MOVE 'FileStreamDocuments2008R2' TO 'C:\RESTORE\FILESTREAM\Documents2008R2_COPY';
GO

--Get databases files details
SELECT DB_NAME(database_id), name as logical_filename, physical_name FROM sys.master_files
WHERE DB_NAME(database_id) LIKE 'AdventureWorks2008R2%';
GO

Open in new window

Michael VasilevskySolutions Architect

Author

Commented:
@ValentinoV I'd like to use the instructions you pointed to (http://msdn.microsoft.com/en-us/library/ms177429.aspx) but am stuck at the first step:

1.After you connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

How do I create an appropriate instance of the database engine locally?

I have a .bak backup file and SQL Server Management Studio running but I don't know how to set up a local database engine.
Distinguished Expert 2019

Commented:
appropriate instance is created when MS SQL server is installed.

You need to install an SQL server or sql server express.
Michael VasilevskySolutions Architect

Author

Commented:
Ok, if I have a local server SQLEXPRESS I follow the steps here http://msdn.microsoft.com/en-us/library/ms177429.aspx but get an error at step 8: "Cannot open backup device 'C:\...'. Operating system error 5(Access is denied). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)"

Any help on that one?
Distinguished Expert 2019

Commented:
You need to select restore from file and not from device and point to the .bak file that you created when you backed up the database.

A device is a tape, etc.

Michael VasilevskySolutions Architect

Author

Commented:
Right, restoring "from device" allows me to select the .bak file on my local hard drive. It get the "Access is denied" error however. Do I need to set permissions somewhere? To access the database on the server I use credentials...
David ToddSenior Database Administrator

Commented:
Hi,

The problem you have is that you or more correctly the SQLExpress, doesn't have permission to open the backup file. It is a filesystem issue.

Regards
  David
Michael VasilevskySolutions Architect

Author

Commented:
@dtodd, I suspect you are right, any suggestions on how to correct that situation?
Distinguished Expert 2019
Commented:
Was skipping the device and the options where you can select the source of the data.

Lets go at it a step at a time.
1) you open the SSMS/SSMSE and connect to the local instance
2) you right click and select restore database
3) you select restore from device\file in the drop down and then you click add.
4) you select the database name from the drop down at the top where it has "To database"
5) check the restore box
6) selection the options in the left pane
The following is under the "Restore Database files as"
7) adjust the location where the database files will be stored from the SQL server reference which might be g:\program files\microsoft sql server\ ... etc. to a local drive reference.
This has to be done for both the database file (mdb) and the transaction log file (ldf)
* if the database already exists on the system, you have to select the overwrite option.
8) ok

That should do it.

The access denied error is not because you can not access the .bak file, but because the restore location to which the MDB and LDF files need to be stored do not exist on your system.
David ToddSenior Database Administrator

Commented:
Hi,

To check the permissions on the backup file:

In Windows Explorer, locate the .bak file, and right click, select security. Make sure everyone has read access. (not the best practice, but one that should get you moving again ... Not a worst practice though. )

Regards
  David
Michael VasilevskySolutions Architect

Author

Commented:
I know I'm doing something wrong.
1.PNG
Michael VasilevskySolutions Architect

Author

Commented:
I cancel this and then click on Registered Servers and double-click on SQLEXPRESS Register Servers
to get here:
Michael VasilevskySolutions Architect

Author

Commented:
SQLEXPRESS
then right-click on the Databases folder and select Restore Database to get there:

3.PNG
Michael VasilevskySolutions Architect

Author

Commented:
I select From device and click the ellipses to get:
 Restore File
Click Add and get an error:

5.PNG
Michael VasilevskySolutions Architect

Author

Commented:
Click Ok and navigate to my .bak file and then click OK and get the error:

6.PNG
Distinguished Expert 2019

Commented:
download I think has a protection where you may need to unblock the file or copy it to another location.

Not sure why you are doing a restore headeronly.

Aer you on the system where the SQL server is or are you accessing the resource remotely?
A remotely accessed resource will have the drives that are local to it not to you.

ie. if you from your workstation using ssms/ssmse to access a remote SQL instance want to restore something from backup, you have to copy the .bak file from your workstation to the server/system where the restore is to occur.
David ToddSenior Database Administrator

Commented:
Hi,

What is the name and full path of the backup file you are attempting to restore?

Regards
  David
Distinguished Expert 2019
Commented:
you are using SQL authentication to gain access into the sql instance which means you can only restore files from locations where the account under which SQL server is running. It is often run as local system unless changed, but access into user's profile is restricted.
copy the backup to c:\ or another drive on the system where sql server is running.
Daniel_PLDB Expert/Architect
Top Expert 2011
Commented:
Not sure why you are doing a restore headeronly.
It's for the optionSelect backup sets to restore showed in screen in comment ID:37351584

Same effect in my procedure.

If you don't mind and would like to listen (without ignore) other advices please note following:

SQL Server Express edition - which we've talking about here - by default runs as a NT AUTHORITY\NETWORK SERVICE account. It cannot access files in your personal profile (exactly TVS user). To resolve your issue is you should grant read (at least) access to your backup (*.bak) file or Downloads folder for NT AUTHORITY\NETWORK SERVICE account. In case SQL Server is using different service account grant that account access to already mentioned file or/and folder.
David ToddSenior Database Administrator
Commented:
Hi,

Wouldn't matter if using Windows Authentication - it is SQL (and the account it is running as) that need access to the file.

File is
c:\Users\TVS\Downloads\somefile.bak

C:\Users\TVS will have pretty strong permissions - TVS (you) and system and administrators ONLY will have rights to that tree ...
Follow Arnold's suggestion and _copy_ the file somewhere else.

SSMS can see the file as SSMS is running as YOU.

HTH
  David
Michael VasilevskySolutions Architect

Author

Commented:
Ah I thought that would be it. I moved the file to C:\ and now get a new message at the same place:
 Restore Error 3
Michael VasilevskySolutions Architect

Author

Commented:
Ok it looks like this is due to different SQL Server versions (http://dotnetguts.blogspot.com/2010/06/restore-failed-for-server-restore.html)

I think it's time to close this questions, I'll open another if I'm still having trouble. Thanks all!!
David ToddSenior Database Administrator

Commented:
Hi,

As you just guessed, generally can restore on to a later version/service pack than the original. Sometimes can go back a service pack level - it depends. I know that on SQL 2005 there was a hotfix that couldn't be restored pre-hotfix.

Regards
  David