Make a copy of my DB at work!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Make a copy of my DB at work!!

Author

Topic

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-12 : 15:34:36

Hi all,

I would like to make a copy of my DB at work (currently using SQL Server 2000, I have about 10 tables with a few hundreds records only) and export it to my SQL Server at home. What are the best ways of doing this??? Should I export it to Access first? Then maybe I can zip it and send it through email. And when I go home, I can unzip it and export it to my SQL Server. OR should I make a copy to my CD ROM (never done this before though) and export it to my SQL Server at home?

Please advice,

Thx, Lisa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 15:37:01

Do you have SQL Server 2000 at home? If so, I would BACKUP the database at work. Burn the .BAK file to CD. RESTORE the database at home using the .BAK on the CD. How big is the database? When zipping up the .BAK file, it usually zips at least 10:1, sometimes 15:1.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:02:48

Since it so small, I wouldn't even bother with the CD. Just zip up the .BAK file and e-mail it. You will probably need to MOVE the database files upon RESTORE. Will you be performing the RESTORE through Query Analyzer or Enterprise Manager? QA will require WITH MOVE option. EM does the move on the second tab of the initial screen.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 16:03:44

How big is the backup?

You could probably zip to a floppy....

Brett

8-)

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-12 : 16:10:51

I would prefer to use QA first to RESTORE it. If I fail, then I will try EM insteads. I am still new to QA but I like to give myself a chance to use QA first. Yes, it would be better off for me to email it or save it to a disk since my db is pretty small. Thanks to both of you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:13:48

Here is a sample RESTORE command:

RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\NameofBackup.BAK'
WITH REPLACE

That is the simplest. If your drives are different than the database server, then the MOVE option is required.

RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\NameofBackup.BAK'
WITH REPLACE, MOVE 'DBName_Data' TO 'C:\MSSQL\DATA\DBName_Data.MDF',
MOVE 'DBName_Log' TO 'C:\MSSQL\DATA\DBName_Log.LDF'

Use RESTORE FILELISTONLY to determine what database files are in the backup. This will help you with the MOVE option.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:15:58

If this isn't a production database, then you can run sp_detach_db on the database server to release the database. Copy the MDF and LDF files to another location. Then reattach it using sp_attach_db so that it is back online. Then at home, copy the MDF and LDF files to where you want them, then run sp_attach_db.

DETACH/ATTACH approach is fastest and easiest if the source database can be brought offline such as in a non-production environment. Otherwise, BACKUP/RESTORE is used.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 16:29:38

Are you sa on the box?

If not just DTS the data out...

Gen a script as well, run the script at home, the DTS the data in....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:30:22

From Enterprise Manager, you can not backup the database to a different server.

"BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. "

Are you a member of any of these roles? Run this in QA to see if you can even perform a BACKUP:

BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\DBName.BAK'
WITH INIT

If you get a permission error, you'll need to contact the DBA or sysadmin.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:47:40

Yes you are a member of one of those roles. The backup file is located on the database server's C drive. So now let's backup directly to your machine:

BACKUP DATABASE DBName
TO DISK = '\\YourMachineName\SomeShare\DBName.BAK'
WITH INIT

If you are admin on your box, then you can do this:

BACKUP DATABASE DBName
TO DISK = '\\YourMachineName\C$\DBName.BAK'
WITH INIT

The above will put DBName.BAK directly onto your C drive.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-12 : 16:48:14

You looking in the data folder?

Do a search.....

How big is it?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 16:55:25

Nah, she's looking at her own client machine instead of the database server.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 17:14:53

To see what your machine is named, go to Start, then run, then type in cmd and hit enter. Type in hostname and hit enter. What does it return?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 17:39:31

Well you have to specify a valid share name if you are going to use SomeShare. C$ is a default admin share for the C drive. Some people can't access admin shares, so that's why I posted both. You have admin privileges, so only use the C$ version.

Does DBName.BAK exist after you execute the C$ code?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 17:46:52

What user are you using to connect to the database server in Query Analyzer? Is it sql authentication or Windows authentication? Can the database server see \\DW-34-SU\C$\? Can you just call the DBA to get the backup file? You already created one on it directly off the root of C. Just have him/her e-mail it to you.

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-12 : 17:54:27

I am using 'Window authentication' to connect to both DB server and QA. I am sure I can call the DBA to get the backup file but I still want to understand why I can't see my .BAK file in my C drive. It did say: "Sucessfully" after I executed this code:

BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\DBName.BAK'
WITH INIT

Thx so much for your time. I will play around some more and keep you posted tomorrow.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 17:56:26

That code backs it up to the database server's C drive, not your C drive. It is very possible that the database server can not get to your machine, thus the error.

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-04-12 : 18:02:24

quote:
Originally posted by tduggan

That code backs it up to the database server's C drive, not your C drive. It is very possible that the database server can not get to your machine, thus the error.

Tara

Ohhhhh.....so that code backs it up to the db server's C drive, not mine. Ok, so is there a way to back up my db to my LOCAL C drive insteads of server's C drive?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 18:04:42

Yes there is. That's when you provide the UNC path:

BACKUP DATABASE DBNameGoesHere
TO DISK = '\\DW-34-SU\C$\IGSurveyMiniCopy.BAK'
WITH INIT

The code executes from the database server's perspective. So if the database server can not get to \\DW-34-SU\C$ using the account that you logged into QA with, then it will not work.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 18:13:40

Just remembered that "SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. "

So you need to grant permissions on your client machine to the account that SQL Server logs on with. The DBA will have this info. As a temporary solution, you can create a share on your machine, grant Everyone permissions to write to the share. Then run the code pointing to the share. Then remove the permissions.

IOW, this isn't going to work unless the account that MSSQLSERVER service uses has admin privs to C$ on your machine.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-13 : 10:20:41

They are not all samples...and I would be VERY careful....

I would make sure that you have a scheduled backup of all SYSTEM databases...especially master and model....

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources