?
Solved

How to create backup of SQL Server db for purposes of creating it on a development pc

Posted on 2005-05-11
11
Medium Priority
?
1,225 Views
Last Modified: 2012-05-05
(Access Sage, SQL newbie)

Hello fellow experts

I have a client that has a SQL back-end, Access front-end where they want me to do some Access enhancements on it.  Is there a way to create a backup copy of their SQL back-end database for purposes of creating it on my development pc, for working on their front-end?

My alternative is to use a TightVNC-like connection to their server in order to do any work connected to the back-end, which I'd like to avoid if possible.

TIA
-Jim
0
Comment
Question by:Jim Horn
  • 6
  • 5
11 Comments
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 2000 total points
ID: 13978200
Sure....do it from their Enterprise Manager console:

Right click the database, All tasks>>Backup database.  Select "to device" and pick a file location/name

copy that file to your dev box and restore from there:

In Enterprise manager on your machine, Tools, restore database (PICK A NEW DATABASE NAME HERE), From Device, Select the file you copied.  Change to the options tab and edit the file locations as necessary for the two files
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 13978434
Good deal (funny man....)

Between the TightVNC connection and this, I'll try to demonstrate to this new, small client that I don't have all the time in the world to drive cross town to their offices and back, especially since I'm already committed 40 hrs/week to another client I'm happy with.

-Jim
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13978547
understood....I do the same thing for one of mine.  But I use remote desktop, copy the existing, most recent backup file into a Zip file and send it to my FTP server.  From there I pull it to my workstation and restore it locally and reconnect the ADP to it
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 66

Author Comment

by:Jim Horn
ID: 13986578
I was able to move the .bak file, but now I receive an error message:

Microsoft SQL-DMO (ODBC SQLState: 42000)
Device activation error.  The fhysical file name {old .mdf file location} ma be correct.  
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13986637
How were attempting to restore?  Enterprise Manger or Query Analyzer....and did you change the physical file locations to something that exists on your local SQL Server?
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 13986722
Whoops nevermind.  I forgot to edit the 'Move to physical file name' path as you stated above.

I have the 'Restore Progress' dialog box now, and will PAQ when its complete.

Thanks.
-Jim
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13986858
cool.  You may have to monkey around with dropping and adding users, depending on how you will be accessing the db.  Users generally get screwed up when moving databases to a different box.  Its the SID (SecurityID)
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 13986861
Worked like a charm.  Thanks.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13987016
NP...Jim...do you do ADPs?
0
 
LVL 66

Author Comment

by:Jim Horn
ID: 13987076
Nope.  I haven't had a project yet that wasn't Access to a supported SQL Server, or anything just Access couldn't handle.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13987140
Darn....thanks anyway :-)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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