Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problems backing up SQL Server 2005 DB

Posted on 2009-04-03
21
Medium Priority
?
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

688 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