Backup/Restore w/ SQL

  • 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?

  • 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

  • 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!

  • 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.

  • 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

  • Yes, please post it.

  • 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://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=764

    Search for the others, you will be surprised on the variants that have been posted.

    Andy

  • 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

  • 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.

  • 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

  • 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

  • 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