Help How Make Backup and Restore ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Help How Make Backup and Restore ?

Author

Topic

Fanco
Starting Member

4 Posts

Posted - 2003-09-27 : 14:17:12

I connect like database admin and

try to make differential backup using this command

1- Back up Database Cyber to XXX With Differential

2- Back up Log Cyber to XXX

The to test the back up

I run then this restore command

1- Restore database Cyber from XXX with norecovery and

2- Restore Log Cyber from XXX with recovery.

All is perfect

I close the connection

When I try to connect to Cyber database the server send me this message

Server Msg 927, Level 14 State 2

Database Cyber Cannot Be opened. It is in the middle of restore ?

Why the database cannot be opened ?

I'm trying to open from query analizer, because my application must be do it (the backup - restore) using transaq - sql.

Thank In advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-28 : 00:18:49

Why are you doing a differential backup followed by backing up the transaction log? Why not just run a full backup then restore it? Please explain what you are trying to do.

Also, your commands do not follow each other. If you are needing a differential backup, then did you first restore the full backup then the differential?

I do not understand why you are getting this error because the WITH RECOVERY option would have put it into the correct mode. But I suspect that the problem has to do with the order that you are running these.

Here is my recommendation of what to run:

BACKUP DATABASE Cyber
TO DISK = 'E:\SomeDirectory\Somefile.BAK'
WITH INIT

RESTORE DATABASE Cyber
FROM DISK = 'E:\SomeDirectory\Somefile.BAK'
WITH REPLACE, RECOVERY

Tara

Fanco
Starting Member

4 Posts

Posted - 2003-09-29 : 16:30:31

quote:
Originally posted by tduggan

Why are you doing a differential backup followed by backing up the transaction log? Why not just run a full backup then restore it? Please explain what you are trying to do.

Also, your commands do not follow each other. If you are needing a differential backup, then did you first restore the full backup then the differential?

I do not understand why you are getting this error because the WITH RECOVERY option would have put it into the correct mode. But I suspect that the problem has to do with the order that you are running these.

Here is my recommendation of what to run:

BACKUP DATABASE Cyber
TO DISK = 'E:\SomeDirectory\Somefile.BAK'
WITH INIT

RESTORE DATABASE Cyber
FROM DISK = 'E:\SomeDirectory\Somefile.BAK'
WITH REPLACE, RECOVERY

Tara

Thanks Tara
I need yo make log backup, because I only need the new data and send this file to others users ( same city or others cities ) and then they restore this data to yours Database.

Please ,explain me if this is the right form to do it.

Thanks in Advance

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-29 : 18:10:03

If they have recovered the database then they wil not be able to apply the log - they need to leave it in standby mode which will give them read access but allow the log to be applied.

Sounds like your recovery did not work when you did this test - look for errors in the sql server log and nt log.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-30 : 12:28:17

Yes it sounds like the database was recovered which means no additional transaction logs can be applied. Have you looked into replication to solve your problem?

Tara

Fanco
Starting Member

4 Posts

Posted - 2003-10-01 : 19:05:26

Well I think that I need something Like replication of Palm vs PC
(only the new data is insert into the remote database).

Is it possible ?

Thanks in Advance
Franklin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-01 : 19:25:43

I don't know what replication of Palm vs PC means. Please explain.

Tara

Fanco
Starting Member

4 Posts

Posted - 2003-10-01 : 20:18:57

Well, I only need that the new data insert in my database (everyday), can be access for the others users (others cities).

Then, I was think that database and logs backup files,can be used for they (others users) to restore this new information and all users have the same information.

I think that i need study a lot of sqlserver administration !!

I´m really need help
Franklin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-01 : 20:21:22

Replication will solve your problem.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources