December 22, 2005 at 12:59 pm
I'm trying to back up DB1 and restore it as DB2.
This is what I'm doing:
USE master
BACKUP DATABASE DB1 TO DB1_BU
USE master
RESTORE DATABASE DB2 FROM DB1_BU
Both commands execute successully. DB1_BU is being created in the Dump Device that I had added, the timestap is updated everytime I run it. But DB2 is not being updated by the restore, but the command executes correctly.
The results on the Restore stmt are:
Processed 77232 pages for database 'DB2', file 'DB1_Data' on file 1.
Processed 1 pages for database 'DB2', file 'DB2_Log' on file 1.
RESTORE DATABASE successfully processed 77233 pages in 47.094 seconds (13.434 MB/sec).
Any ideas?
December 23, 2005 at 2:12 am
This doesn't seem right. Are you sure this is the complete commands, and the right resultset from the restore?
Since the restore is specifying another dbname than the one backed up, the restore command should indicate an error that the files are being used by db1, and suggest using the command with the 'with move' clause.
Could it be that the device you're backing up to also contains previous backups from db2, and in fact that is the one being restored, and not the latest db1 backup as you might think?
/Kenneth
December 23, 2005 at 9:44 am
The device only contains a backup of DB1. And DB1 is not being restored. I tested this by running the backup, changing data, and then doing the restore.
The server log actually says that it is restoring DB2.
Since today should be a slow day at work, I'll have some more time to play with this. I'll try adding the MOVE cmds.
Thanks for you help!
December 23, 2005 at 10:18 am
I took the following out of BOL (didn't read down enough before), but it still isn't working.
USE master
BACKUP DATABASE DB1
TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB1_1.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB1_1.bak'
RESTORE DATABASE DB2
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB1_1.bak'
WITH MOVE 'DB_Data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB2.mdf',
MOVE 'DB_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB2_log.ldf'
Basically, I'm just trying to come up with a way to copy a live database and make a test database that could be automated, that could be scheduled. That way you could have a fresh test database every night.
December 23, 2005 at 11:55 am
Hi Mattyk6,
I just finished setting up an automated process pretty close to what your asking I believe.
1) I have a nightly backup that overwrites the same db name on our production environment.
2) After success on nightly backup, step 2 of the sql agent job is to xcopy this db to our dev environment.
3) I have a sql job that runs at night and restores the nightly backup from production that was copied over to dev. This sql agent job has 3 steps. a) kill user process for that db, b) restore/replace that db, 3) grant db access for users.
It's been working great so far. I'm thinking about adding a 4th step to above #3 on failure to email me. I'm really busy here at work so it's been tuff.
If above scenario is what your looking for, let me know so I can post it.
Thanks,
Lawrence
December 23, 2005 at 3:07 pm
Yes, please post it.
December 25, 2005 at 11:05 pm
You can find the scripts for all of these steps already posted, they will need to be combined for your task requirements. Here is the Restore portion:
http://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=764
Search for the others, you will be surprised on the variants that have been posted.
Andy
December 27, 2005 at 3:01 am
Matty,
you need to tell us what errors you get. 'Not working' isn't very much to go on.
However, I didn't have any problems doing the below (minor changes to logical names only) It's a complete test of create a test db, back it up, restore as a new db with new name and finally drop both db's.. Works just fine.
USE master
go
create database DB1
go
BACKUP DATABASE DB1
TO DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB1_1.bak'
go
RESTORE FILELISTONLY
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB1_1.bak'
go
RESTORE DATABASE DB2
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB1_1.bak'
WITH MOVE 'DB1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB2.mdf',
MOVE 'DB1_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB2_log.ldf'
go
drop database DB1, DB2
go
/Kenneth
December 27, 2005 at 9:38 am
That's just it. I have never gotten an error during this process. But the data in the two databases do not match. The security log even says that the restore completed successfully.
December 27, 2005 at 10:55 am
Maybe I was too tired last week or what, but that script appears to be working fine this morning. I'll go ahead and put it into a test environment and will see how it goes.
Thanks,
Matt
December 27, 2005 at 10:58 am
You say the data doesn't match....in what way does it not match?
Do you have more data in DB1 than DB2? That could be true if DB1 continues to get transactions during the backup.
Is the data totally different? Is it older?
When you do the backup and restore, do you move the backup files from one server to another? Could you be moving the wrong backup files?
-SQLBill
February 18, 2006 at 1:19 pm
Hi David, (David A. Long)
I've been using your script (Restore DB from Disk File) since I came across it last summer. I use it to refresh a database on a separate server that is used for training. Thanks for posting ... it has saved me alot of work!!! Espescially in keeping all userids intact.
Thanks again!
~Peg
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply