May 31, 2010 at 12:48 pm
You restored the diff WITH RECOVERY, indicating that was the last backup that you wanted to restore and that the DB must come online. If you want to restore log backups, all backups except the very last log backup must be restored WITH NORECOVERY.
Drop the DB, start again.
p.s. the diff doesn't need the REPLACE option. That's only for the full when restoring over an existing database.
If you haven't already, I suggest you read through Books Online, all the stuff in there on restore sequences. There's a lot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2010 at 12:52 pm
OMG that was stupid.
yep, I better do this again.
Thanks to both of you for hanging in there with me. I had been butting up against this original problem of not being able to restore for a time before posting to this site. so so excellent help and advice is very appreciated.
The results of my first flawless backup and restore using t-sql. Thank you all.
USE AdventureWorksDW
GO
--test data
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before FullBackUp')
-- Back up the AdventureWorksDW database
BACKUP DATABASE AdventureWorksDW
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH INIT, COMPRESSION;
GO
--test data
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before DiffBackUp')
--Back up the AdventureworksDW differential
BACKUP DATABASE [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_DIF.dif'
WITH DIFFERENTIAL, INIT
GO
--test data
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before TransBackUp')
-- Back up the AdventureWorksDW transaction log
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH INIT
GO
--test data
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before SecondTransBackUp')
-- Back up the second AdventureWorksDW transaction log
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN1.trn'
WITH INIT
GO
--test data
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before Restore')
USE master
GO
--stopped instanced, deleted AdventureWorkDW database (both data and log files in MSSQL/DATA folder), restarted instance. Then...
--to delete metadata around this database
DROP DATABASE AdventureWorksDW
--Restore full
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY
GO
--Restore Diff
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_DIF.dif'
WITH NORECOVERY
GO
--Restore transaction log
RESTORE LOG [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH NORECOVERY
GO
RESTORE LOG [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN1.trn'
WITH RECOVERY
GO
May 31, 2010 at 8:01 pm
Jeffrey, in regards to your reply at (6:36:10 today)there are a couple of things here I would like to explore, like the MOVE. I tried it, but without good result. BTW, G is for an external drive. I have been backing up to it. i tried to make the command give explicit instructions to move the db to the default folder C:\...MSSQL\DATA\etc.:
--Instead of Restore full
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY
GO
--I tried using MOVE option
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY,
MOVE N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data'
GO
But got error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'G:\BU\AdventureWorksDW_May31_2010_FULL.bak' is not part of database 'AdventureWorksDW'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Can you tell me what is wrong here? Thanks.
May 31, 2010 at 8:13 pm
Yes - you need to specify the logical file name, not the physical name. You can get those using:
Select * from sys.database_files
Or
Restore filelistonly
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 9:23 pm
Hi Jeffrey, thanks.
I ran
Select * from sys.database_files.
i found a column called "physical_name" (none called "logical name") and used it. See below. Still error.
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY,
MOVE N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data.mdf'
GO
Msg 3234, Level 16, State 2, Line 1
Logical file 'G:\BU\AdventureWorksDW_May31_2010_FULL.bak' is not part of database 'AdventureWorksDW'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
May 31, 2010 at 9:33 pm
Try the restore filelistonly on the backup file. It will be something like 'AdventureWorksDW' for the data file and 'AdventureWorksDW_log' for the log file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 9:56 pm
I am really floundering here.
I don't have any official backup_device. I just send my backups to my G:\BU folder.
When I run RESTORE FILELISTONLY FROM AdventureWorksDW_May31_2010_FULL I get an error that "Backup device 'AdventureWorksDW_May31_2010_FULL' does not exist. To view existing backup devices, use the catalog view. To create a new backup device use either sp_addumpdevice or SQL Server Management Studio."
When I do a SELECT * FROM sys.backup_devices, I get 0 rows.
Do I need to officially create a 'backup device' before this MOVE option will work?
Thank you.
May 31, 2010 at 10:02 pm
No - you don't need a device. Just like the normal restore command you need to use the disk parameter.
Restore ... with disk = '...'
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 11:04 pm
USE AdventureWorksDW
GO
RESTORE FILELISTONLY
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
The logical file name given for the data file is AdventureWorksDW_Data. I used this in the Restore WITH MOVE statement as follows.
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY,
MOVE N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
TO 'AdventureWorksDW_Data'
GO
Msg 3234, Level 16, State 2, Line 1
Logical file 'G:\BU\AdventureWorksDW_May31_2010_FULL.bak' is not part of database 'AdventureWorksDW'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I also tried this:
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY,
MOVE N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data'
GO
Error:
Msg 3234, Level 16, State 2, Line 1
Logical file 'G:\BU\AdventureWorksDW_May31_2010_FULL.bak' is not part of database 'AdventureWorksDW'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
June 1, 2010 at 12:49 am
This is how your RESTORE should look:
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorksDW.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW_Data' TO N'D:\Databases\UserDB\AdventureWorksDW_Data.mdf',
MOVE N'AdventureWorksDW_Log' TO N'D:\Databases\UserLog\AdventureWorksDW_Log.ldf',
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
The above code will restore the AdventureWorksDW database to the specified location on my system here at home.
You need to change the destination in the TO portion of the MOVE clauses to restore the database where you need it.
You should take the time to read BOL (Books Online) and the syntax of the RESTORE command.
June 1, 2010 at 1:11 am
Thank you. this worked.
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'<--origin
WITH FILE = 1,
MOVE N'AdventureWorksDW_Data'<--logical name
TO N'C:\Program Files\Microsoft SQL <--destination
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data.mdf',
NORECOVERY
GO
Problem was combination of syntax and flow.
I do spend time with BOL and am getting better at understanding how to interpret the syntax statements. It is difficult for me, i will be honest. But, I do look and will continue to.
The help here is staggering and i am very grateful!
June 1, 2010 at 1:45 am
Jeffrey Williams-493691 (5/31/2010)
".... validate that the login running SQL Server has access to the directory and file where the backup file exists....."
Jeffrey, if you aren't totally sick of me....will you give me a place to start to begin to know how to do this thing you mentioned? it wasn't the source of the problem, but want to know what it's about.
"validate that the login running SQL Server has access to the directory and file where the backup file exists."
This will cover all the ideas that surfaced for this post. Thanks.
June 1, 2010 at 2:20 am
First of all find out the service account, SQL server is running under. You can find it out from Services Applet in windows or from SQL Server configuration manager.
Then go to the folder containing backup file. Right click this folder and go to properties. Then select Security tab and see whether SQL server service account has access to this folder or not.
If service account does not have access to backup files then restore operation will fail.
June 1, 2010 at 7:10 am
Guarav answered the question quite well - if you still need help with that, let us know.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2010 at 8:05 am
OK, in my case I have all permissions as I am administrator.
But, I want to explore this a little further becuase I am having trouble understanding how to utilize service accounts.
In configuration manager I have three possible built-in accounts: Local Service, Local System, and Network Service. When I change from Admin to built-in account: Local Service (and after SQL Server restarts), I see no change to permissions to the Backup folder (for example).
1. Which services on my server will a user belonging to the Local Service built-in account be restricted from using?
2. Is there a way to create user accounts that belong to the Local Service account?
thank you.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply