December 3, 2007 at 8:35 am
Hi
I am quiet new to Sql server 2000.
I have tried to creat a sql script to automate a restore process.
Can someone look at this and tell me where I am going wrong please I feel like a right numpty:)
here is the sql
exec usp_KillConnections @dbname = 'Changepoint'
ALTER DATABASE Changepoint SET OFFLINE
RESTORE Database [Changepoint] From Disk = N'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\fulldbbak.BAK'
RESTORE LOG [Changepoint] FROM DISK = N'd:\temp\cplog.bak' WITH FILE = 1, NOUNLOAD , STATS = 10
ALTER DATABASE Changepoint SET ONLINE
and when i run it, all looks fine till i get the result back i get this and don't understand it any help would be great.
Total number of processes from database Changepoint: 0
Processes ended normally: 0
Processes could not be ended: 0
Server: Msg 5173, Level 16, State 1, Line 3
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 3
Device activation error. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL\INDEXES\RMSPRODDB_Changepoint_CP_IDX.ndf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 3
Cannot associate files with different databases.
Server: Msg 947, Level 16, State 1, Line 3
Error while closing database 'Changepoint' cleanly.
Server: Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Log file 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Changepoint_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Processed 1361528 pages for database 'Changepoint', file 'Changepoint' on file 1.
Processed 297136 pages for database 'Changepoint', file 'RMSPRODDB_Changepoint_CP_IDX' on file 1.
Processed 3 pages for database 'Changepoint', file 'Changepoint_log' on file 1.
RESTORE DATABASE successfully processed 1658667 pages in 999.365 seconds (13.596 MB/sec).
Server: Msg 4306, Level 16, State 1, Line 6
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE LOG is terminating abnormally.
thanks in advance,
Frank.
December 3, 2007 at 9:06 am
you have some pathing problems somewhere.
The way this works is you have an MDF, which stores your data, and an LDF, that stores your log records (track the changes to the MDF). Your files have a physical location and a logical name. If you are restoring on a new server, the paths may be different, so you need to use the WITH MOVE option to pick a new physical path to which your logical files move.
Also, when you restore the full backup, it restore the log as well. If you want to apply a later log file, you need to restore the database using WITH NORECOVERY, which is recommended. Once the logs are restored, you can then ALTER the database and set the option to RECOVERY.
ALTER DATABASE Northwind
SET RECOVERY FULL
December 3, 2007 at 9:26 am
Thanks for the advice,
where would the with move command go in this script.
also do i need to restore the log file after doing a full back restore, i was thinking i don't from what you said in your last reply.
exec usp_KillConnections @dbname = 'Changepoint'
ALTER DATABASE Changepoint SET OFFLINE
RESTORE Database [Changepoint] From Disk = N'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\fulldbbak.BAK' ,WITH MOVE ?
RESTORE LOG [Changepoint] FROM DISK = N'd:\temp\cplog.bak' WITH FILE = 1, NOUNLOAD , STATS = 10
ALTER DATABASE Changepoint SET ONLINE
Cheers
Frank.
December 3, 2007 at 1:57 pm
Use this syntax:
WITH
MOVE 'logical data file name' TO 'physical data file location',
MOVE 'logical log file name' TO 'physical log file location',
NORECOVERY
Use as many MOVE statements as you have data files. You can find the logical file names by running sp_helpfile in the ChangePoint database or RESTORE FILELISTONLY FROM DISK = N'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\fulldbbak.BAK'. See BOL for more details.
Whether you can restore the log or not depends on the recovery model of the database - if it is in SIMPLE, then you can't do log backups let alone restore them.
When restoring your log backups, you need to specify WITH NORECOVERY for all except the last one. Otherwise, only the first log backup will be restored & the database brought online. There are other options for point-in-time recovery as well, but it sounds like you don't need to do that.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 4, 2007 at 8:08 am
Great thanks for that.
got it working now,
Can someone explain a few commands to me, the following ones i don't understand or no my options.
WITH FILE =1 what does the with file mean and the = 1 in restoring the transaction log file.
NOUNLOAD , STATS = 10 and what does this mean.
Do i need the above commands in my script to recover a db see below.
exec usp_KillConnections @dbname = 'Changepoint'
ALTER DATABASE Changepoint SET OFFLINE
RESTORE Database [Changepoint] From Disk = N'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\fulldbbak.BAK'WITH NORECOVERY
RESTORE LOG [Changepoint] FROM DISK = N'd:\temp\CPTRNLOG.bak' WITH FILE = 1, NOUNLOAD , STATS = 10
ALTER DATABASE Changepoint SET ONLINE
Thank you very much.
Frank.
December 4, 2007 at 1:58 pm
The options are described in BOL. A brief precis:
FILE - haven't used it myself. You can have more than one backup (AKA backup set) in a single backup file (i.e. you append to the file rather than overwriting it). Using the FILE option allows you to specify which one to use. If you run one backup per physical file, this option isn't necessary.
NOUNLOAD - only relevant for tape devices. Not using a tape device, don't need to specify.
STATS - provides a percentage indicator of where the backup/restore is at. If you run the command in Query Analyzer and look on the Messages tab, you'll see '10 percent backed up, 20 percent back up' etc. Default is 10, so you could just use WITH STATS.
You'd only need to take the database offline if you are worried about connections being established between when you kick people out & start the restore or people connecting straight after the restore has finished. While the database is in a restoring state, nobody can connect to it.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply