November 2, 2005 at 5:44 pm
Hi,
I have got a SQL Server 2000 server running on Windows 2003 EE.
I would like to know whether my procedure is correct to build a development database by restoring production database dump(backup).
The procedure i use is as follows:
1. Backup Production database using EM to a local drive.
2. In EM, restore the database using production backup dump file and give a new name(development db name) in the 'Restore database as' field.
3. In the options tab, change the physical filename path to a new filename. And leave the logical name to be the same as the production db.
4. Click OK to perform restore.
I am concern about step 3, do i need to change the logical filename to a different name? I tried to change it but i get an error. "Logical file 'TrustArchitect_ORDSDEV_DATA' is not part of database 'Trust123'. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.
The 'Trust123' is the new name i gave and 'TrustArchitect_ORDSDEV_DATA' is the new logical name i gave.
Regards,
Philip
November 2, 2005 at 6:20 pm
Hi,
I am not too sure about the procedure i have above because i am getting a really strange problem with the database maintenance plan i have created for the production database.
I have a database maintenance plan that
+ creates a dump(backup) of the production database to a network drive every night.
+ backup the production transaction logs to the same network drive every half hour.
A month ago, i changed the time that backup the transaction log from running on the hour(e.g. 6:00am, 6:30am, 7:00am) to 17 mins past the hour (i.e. 6:13am, 6:43am, 7:13am etc).
Recently, i started to see errors on the eventlog complained that SQLServer Production db maintenance job failed. The failed job belongs to the backup of the production transaction log.
When i checked the transaction log backup created by the failed job, they all had the same size (14KB). I had a discussion with another workmate and he suggested that may be there is another maintenance plan trying to backup to the same file. So I checked the maintenance plan stored in the msdb database, sysdbmaintplans, sysdbmainplan_jobs etc. but i couldn't find other maintenance plan that tried to backup the same production database.
Then I tried to recreate the production maintenance plan from scratch and also changed the time it backup the transaction logs from 6:13am, 6:43am, 7:13am ... to 6:17am, 6:47am, 7:17am ...).
When the plan runs, it creates two copies of the transaction logs backup each with a different timestamp, one created at :17 past the hour and the other :13 past the hour and another one at :47 , :13 past the hour.
In the SQL Server maintenance plan log messages, it only reports that it successfully created the :17 logs but it didn't mentioned anything about the :13 logs.
In the msdb database, i can't see the old plan that backups at :13.
Does anyone what's happened here?
Thanks in advance
Philip
November 2, 2005 at 7:15 pm
Check the job scheduler - probably have an older maintenance plan still on the schedule.
Joe
November 2, 2005 at 8:09 pm
Hi Joe,
I checked the Job Scheduler in EM under SQL Server Agent. But i can't find other old plan.
Philip
November 2, 2005 at 8:38 pm
Hi,
I tried to disable the current production maintenance plan. Then no transaction log is backup and even the hidden plan didn't backup transaction log. Is it possible that this problem is caused by corruption in the msdb database?
November 4, 2005 at 7:13 am
your procedure of restoring the db is right, point 3: don't change the logical file name
here is a snippet you can paste into QA:
USE master
GO
RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\Backupfilename.bak'
RESTORE DATABASE testdbnewname
FROM DISK = 'F:\MSSQL\Backup\Backupfilename.bak'
WITH MOVE 'testdb_Data' TO 'F:\MSSQL\Data\testdbnewnamedb_data.mdf',
MOVE 'testdb_Log' TO 'F:\MSSQL\Data\testdbnewnamedb_log.ldf'
GO
Check for the correct path to the backup file and run than first RESTORE FILELISTONLY, then c&p the logical filenames into the second statement and change the db name / physical file names accordingly, then run it. I like to do it this way, I seem to have more control over the whole process.
Be sure, that all user SID's stored in the sysusers table correspond to the master..sysxlogins. There are often problems with that!
Cheers, Meike
November 7, 2005 at 4:48 pm
Hi Meike,
Thanks for your reply.
I tried the snippet code. It's quite handy.
>Be sure, that all user SID's stored in the sysusers table correspond to the master..sysxlogins. There are often problems with >that!
How to fix it if they don't match?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply