Solved

Make a local copy of a MSSQL DB

Posted on 2006-11-08
8
415 Views
Last Modified: 2010-05-18
I've been running our website off a MSSQL database, running off a server in the same place as the HTTP server.

When testing new features, our local copy of the database accesses the remote database server. This causes two problems:
1. It's very slow due to everything being sent over the internet
2. It makes testing new data features difficult as they have to use the live DB

Therefore, I'd like to make a local copy. We don't have any licenses of SQL Server and don't really want to run it either. I'd prefer MSSQL Express 2005 as its a lot lighter. Is there any way to move the current structure from the remote DB to a MSSQL Express DB?
0
Comment
Question by:OliWarner
8 Comments
 
LVL 18

Accepted Solution

by:
rbrooker earned 100 total points
ID: 17901002
you should be able to take a backup and restore it to the 'lite' version, although, the lite version has no gui, it is all command line.
you might also run into size restrictions, i think mssql express is limited to 4gb
0
 
LVL 16

Author Comment

by:OliWarner
ID: 17901128
4gig isn't an issue... its nearer 20meg =)

just getting a DB backup now so I'll post back then
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 100 total points
ID: 17902282
best is to set up the same version and SP/hotfix level to ensure true compatibility.  What version is the live server?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Author Comment

by:OliWarner
ID: 17902329
Well as I said, I don't want to deploy an entire server for the sake of a 20meg DB. So I'd rather use Express as its a lot lighter on its toes.
0
 
LVL 1

Assisted Solution

by:t2wei
t2wei earned 100 total points
ID: 17904617
There is a free version of SQL server:MSDE (Microsoft SQL Server Desktop Engine),  you can download from http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=413744D1-A0BC-479F-BAFA-E4B278EB9147

I do not know the version of your live DB (SQL server 2000 or SQL server 2005), But I think both versions are available in MSDE

Also If you have a licence for SQL server on your live server, you can install the tools for the SQL server on your PC, and you can use the tools to link to your MSDE too.
0
 
LVL 5

Assisted Solution

by:MrNetic
MrNetic earned 100 total points
ID: 17904923

The best recomendation to achive your results, is to backup the Prod Database, and restore it to MSDE ( 2000 Version ).

You could also use MSSQLSERVER 2005 Express Editon, but you should leave de Database Compatibily Mode = 80.

Best Regards,

Paulo Condeça.
0
 
LVL 4

Assisted Solution

by:hclgroup
hclgroup earned 100 total points
ID: 17908520
rbrooker

MS SQL Server Express Edition does have a GUI Management Tool
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17908540
excellent :)

i have used msde 2000, thought that express would be similar...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need a starter for ETL protocol? 4 65
SQL Querying data from 3 tables, all with 1 common column 4 44
ORA-02288: invalid OPEN mode 2 57
physical_device_name field in SQL 3 30
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question