HOW TO COPY A DB ONTO A CD FOR BACKUP

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)

HOW TO COPY A DB ONTO A CD FOR BACKUP

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-08 : 11:52:20

HELLO
I look at this path c:\program files\microsoft SQL Server\MSSQL\data
I saw two files, I didn't see my db name but I saw data and log files, jennyDB_Data and JennyDB_Log...
I want to copy my db 'jennyDB' onto a CD for backup, which one should I copy onto the cd.
PLEASE HELP.
JENNY.

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-08 : 12:19:53

You should dump the database and copy the bak files

You could copy the files you mentioned, but you should copy both...

Do the first thing

Brett

8-)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-08 : 13:24:03

Thanks X, I finished back up the db. This is how I did:
Right-click on dbname, All Tasks, Backup Database, then followed the steps.
Now, I have jennyDB.bak file under this path, c:\program files\microsoft SQL Server\MSSQL\BACKUP ... I copied this file to a CD.
My question is: can I restore this backup file to any computer that has sql server software installed? and, after restore, I will have a db that is same as the real one, right???
I appreciated your help.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-08 : 14:36:00

Yup...I run this every night to copy prod to dev


Create Proc usp_Restore_Production as


ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TaxReconDB_Prod
   FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
   WITH   MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Data.MDF' 
        , MOVE 'TaxReconDB_Log'  TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Log.LDF'
	, REPLACE


ALTER DATABASE TaxReconDB_Prod SET READ_WRITE

Brett

8-)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-08 : 14:57:07

I try to run your script on another machine to see what happen.
thanks.
jenny

The stupid question is the question you don't ask.
www.single123.com

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-12 : 12:56:39

It didn't work.
I tried your script, it said "you have to create a db before loading that script.....
Thanks, anyway.
jenny.,

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 12:59:22

Well if the db doesn't exists, take out the first ALTER..

Where's your modified code...

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:04:11

If the database doesn't exist just do without the REPLACE keyword, and SQL will create the database from scratch:


RESTORE DATABASE MyNewDatabaseName
FROM DISK = 'X:\MyPath\MyBackupFileName.BAK'
   WITH   MOVE 'MyOldDatabaseName_Data' TO 'X:\NewPath\NewName.MDF' 
        , MOVE 'MyOldDatabaseName_Log'  TO 'X:\NewPath\Newname.LDF'
	, REPLACE

The problem with this is that, reading your posts, it looks like you are new to this and have recently moved up from Access. The command above will seem like a minefield in comparison, and it basically is - there are all sorts of assumptions in the command, which will take you a while to discover, and just as long to ask questions here and wait for answers!

IMHO you are much better off learning the SQL syntax, but you will get by using Enterprise Manager - probably more easily in the short term.

The problems are (just for this Restore command!):

You have to know the logical names of the Data and Log devices of the backup file. There are commands which will allow you to interogate the backup file to find these out - so yet more hassle there I'm afraid.

Then there are options like REPLACE and NORECOVERY which are dead handy - but only after you've found out that they a) exist and b) how to use them. More hassle ...

And as they say, in the interim, "a little knowledge is a dangerous thing"

My advice, FWIW, is to either decide that the skills for SQL Server are a benefit to you, longer term (in terms of salary / job prospects / secure data / sleep easy at night having robust data plans) or choose an easier life and run with Access or something more plug-and-play.

If you are going to go long haul with SQL Server you'll need some books / training courses, and I would recommend that you read the questions and their answers on SQLTeam - probably best to pick the questions that are a week old and have already been fully answered.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-12 : 19:31:15

Not correct about the take out the REPLACE part. REPLACE just says overwrite it if one exists. The problem with her script was the ALTER statement as Brett mentioned. Brett's script is to replace a database with a newer copy but before doing so kick everyone out (else your restore fails).

Tara

Kristen
Test

22859 Posts

Posted - 2004-10-13 : 01:20:43

Your're absolutely right Tara. Darn it! I've been manually editing the REPLACE out of syntax generated by our Restore Syntax Generator SProc for years ... what a waste of keystrokes.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources