Solved

Problems backing up SQL Server 2005 DB

Posted on 2009-04-03
21
295 Views
Last Modified: 2012-05-06
Hi experts,
I have been taking full backups, but when I try to restored I get this error;
http://msdn.microsoft.com/en-us/library/bb283410.aspx
Why is this happening?
How can I take backups that can be restored?
When I make a backup, I find I cannot select the Transation Log options radiobuttons are disabled.
I found this link which addresses "tail of the log", but although worth looking at, does not seem to help.
http://technet.microsoft.com/en-us/library/dd297499.aspx
0
Comment
Question by:hijiki7777
  • 12
  • 9
21 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24058392
try following script for backup and restore, if you don't have enough space in C: drive, change drive letter.
BACKUP DATABASE AdventureWorks 

TO DISK = 'C:\AdventureWorks.BAK'

GO
 

RESTORE DATABASE AdventureWorks

FROM DISK = 'C:\AdventureWorks.BAK'

GO

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24058395
more over, I would like you to have a look at following URL for backup and restore issue

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23277887.html
0
 

Author Comment

by:hijiki7777
ID: 24075237
I attempted the Backup and Restore scripts.
The Backup worked.
For the Restore I got

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "ResourceManager" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24075251
have you tried to back up and restore on the same server?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24075266
have a look at following URL and try to fix that way.

http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/

if it doesn't work even after following the steps in above URL. Please try to run dbcc checkdb command to check any error.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24075281
try this code snippet I got from above link.
ALTER DATABASE AdventureWorks

SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

RESTORE DATABASE AdventureWorks

FROM DISK = C\:BackupAdventureworks.bak

WITH MOVE AdventureWorks_Data TO C:\Data\datafile.mdf,

MOVE AdventureWorks_Log TO C:\Data\logfile.ldf,

REPLACE

Open in new window

0
 

Author Comment

by:hijiki7777
ID: 24075486
I ran
ALTER DATABASE TestDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE TestDB
FROM DISK = 'C:\TestDB.bak'
WITH MOVE 'TestDB_Data' TO 'C:\Data\TestDBdatafile.mdf',
MOVE 'TestDB_Log' TO 'C:\Data\TestDBlogfile.ldf',
REPLACE
and I got the error message;

Msg 3234, Level 16, State 2, Line 7
Logical file 'TestDB_Data' is not part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24075507
check the log file name and data file name as you have used wrong data file name.

i guess your datafile name is TestDBdatafile instead of TestDB_Data
and your log file name is TestDBlogfile instead of TestDB_Log


0
 

Author Comment

by:hijiki7777
ID: 24076698
When I do my backup I type;
BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB.BAK'
GO

This does not specify a datafile name or a log file name. When I look on the file system, these files do not exist.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24076807
.BAK file is a combination of your data and transaction file, If you want to see data file name and transaction log file name than do run following query

select * from sys.database_files

generally all databases have two file one for data and one for log that is why I used two file name in my snippet

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = C\:BackupAdventureworks.bak
WITH MOVE AdventureWorks_Data TO C:\Data\datafile.mdf,
MOVE AdventureWorks_Log TO C:\Data\logfile.ldf,
REPLACE

if you have more that two records in select * from sys.database_files
then you have to use all the "Name" fields in this query. If you are still not clear than please send me the results of  select * from sys.database_files
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:hijiki7777
ID: 24078117
I cannot run that select statement.
The databse is in single user mode and is currently being used.

When I type sp_who2 to find out what SPID to kill, I cannot identify one.

I reckon the previous script I run has caused this. Thankfully the DB is just a dummy one, but for now I cannot even delete it to start again.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24078167
all the commands I have provided above are not harmful, anyway, can you please try to run dbcc checkdb command?
0
 

Author Comment

by:hijiki7777
ID: 24078766
I get this message;
Msg 924, Level 14, State 1, Line 6
Database 'TestDB' is already open and can only have one user at a time.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24078801
does your DB shows "suspect" status in SSMS?
0
 

Author Comment

by:hijiki7777
ID: 24079336

A google search revealed that to answer the question you ask, I have to run
SELECT DATABASEPROPERTYEX('TestDB', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'TestDB'
GO

The response I got was "ONLINE"

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24083332
Well, in that case there shouldn't be any problem, are you able to access any tables in your DB? try doing restart MS SQL service from control panel->administrative tool->Services

Also observer, after restart your TestDB is still 'Online' or become 'Suspect'


0
 

Author Comment

by:hijiki7777
ID: 24084882
I restarted MS SQL and was able access the tables on the DB.
I run
select * from sys.database_files

ALTER DATABASE TestDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE TestDB
FROM DISK = 'C:\TestDB.bak'
WITH MOVE 'TestDB' TO 'C:\Data\TestDBdatafile.mdf',
MOVE 'TestDB_log' TO 'C:\Data\TestDBlogfile.ldf',
REPLACE

The select statement gave me the names for the restore, and the restore worked this time.

Definite progress and thank you for persevering with me.

All that is left is to change the DB back to multiuser. Please let me know how to do that.

Is the solution to the Restore issue to run a script that performs all the operations that we have been using? It seems odd to have to do it this way.

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24084939
you can change your db to multi user by following script.
ALTER DATABASE [TestDB] SET MULTI_USER WITH NO_WAIT

Open in new window

0
 

Author Comment

by:hijiki7777
ID: 24085619
Thank you for that.
So for me to run a restore I have to run a script that does the following;

ALTER DATABASE TestDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE TestDB
FROM DISK = 'C:\TestDB.bak'
WITH MOVE 'TestDB' TO 'C:\Data\TestDBdatafile.mdf',
MOVE 'TestDB_log' TO 'C:\Data\TestDBlogfile.ldf',
REPLACE

ALTER DATABASE [TestDB] SET MULTI_USER WITH NO_WAIT

Now that is certainly a solution. I certainly appreciate your help as I would never have come up with this solution.

But I am surprised I have to do this. I there no other way to configure the DB so that I can simply use the right-click commands in SSMS?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24085641
I am script guy so always like to do stuff by that only but there is a way to do all these things by SSMS but that is something I can't explain here in one post, I may post one article at my blog at http://sqlhub.com
0
 

Author Closing Comment

by:hijiki7777
ID: 31566168
An unexpected solution, appreciate you finding it.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now