Post reply

Error when running an SQL script to restore a database on SQL Server 2008 R2 Express

  • September 9, 2010 at 7:54 pm

    #226793

    Hi,

    At work, I use SQL Server 2008 Express running on Windows XP Professional Service Pack 3.

    At home, I also use SQL Server 2008 Express running on Windows XP Professional Service Pack 3.

    Whenever I do work on the database at work, I usually restore the database at home (or vice versa)

    I have been using the following SQL scripts to back up the database and to restore the same database (at work or at home).

    BackupRealEstate.sql

    USE Master

    BACKUP DATABASE [RealEstate] TO DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH NOFORMAT, INIT, NAME = N'RealEstate-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    RestoreRealEstate.sql

    USE Master

    RESTORE DATABASE [RealEstate] FROM DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    Everything worked fine until I replaced my 8yo home computer with a newer and faster one.

    My new home computer is running Windows 7 Home Premium and I use the latest SQL Server 2008 R2 Express.

    When I tried to run the above restore SQL script, I got the following error message:

    Msg 3101, Level 16, State 1, Line 2

    Exclusive access could not be obtained because the database is in use.

    Msg 3013, Level 16, State 1, Line 2

    RESTORE DATABASE is terminating abnormally.

    There was no other application/process using this database.

    Can someone please help me?

    Regards,

    Herman Gouw

  • September 10, 2010 at 11:14 am

    #1219369

    The error message is because some other user is already connected to the database. Try using ROLLBACK options, check for any open transaction or locks on that database. Even I had a similar issue which is posted here[/url].

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • September 10, 2010 at 9:30 pm

    #1219602

    hermangouw (9/9/2010)

    My new home computer is running Windows 7 Home Premium and I use the latest SQL Server 2008 R2 Express.

    Keep in mind that if SQL Server Express is like the normal versions of SQL Server once you restore a DB to 2008 R2 you won't be able to back it up and restore it to 2008 non-R2. (You can't take a database back to an older version other than coping the data to a new DB hosted on the older server. This applies to backup/restore and detach/attach.)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply