SQL Server Performance

Restoring a database to a new server

Discussion in 'General DBA Questions' started by daniel5455, Jan 7, 2003.

  1. daniel5455 New Member

    I have a Nt4 server with sql 7 and i setup another server with the same setup to be a testing server. I want to do a restore of a user and system databases to test on disaster recovery. I backup up the data base using the Database maintenance plan to the harddrive rather than tape drive. I need to know what are the steps to restoreing the databases on to the test server. WHat are the scripts i run.
  2. bradmcgehee New Member

    The way I would do this, because I like things simple, is to use sp_detach_db and sp_attach_db to perform the move. But this may not be practical as this requires you to bring down individual databases during this process. But if you do have some downtime available on the production server, this can be a quick method of moving all of the databases. See the Books Online for details on how to do this.

    The other option is to use backup and restore, as you have already described. To do this, look up this topic in Books Online: "How to restore a database backup". This page links to many other related pages that outline all the steps you must follow. This option will take longer than the first one, but will allow your production databases to stay up during this process.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. daniel5455 New Member

    this is the script that i used but im getting an error in the error.log stating this: 2003-01-07 14:26:18.81 kernel BackupDiskFile:<img src='/community/emoticons/emotion-3.gif' alt=':O' />penMedia: Backup device '\server1d$aandbaandb_db_200301050333.BAK' failed to open. Operating system error = 1326(Logon failure: unknown user name or bad password.).<br /><br />this is the script i ran:<br />restore database baandb from DISK='\server1aandbaandb_db_200301050333.BAK'<br />with move 'baandb_data' to 'e:mssql7dataaandb_data.mdf', move 'baandb_log' to 'c:sql7dataaandb_log.ldf',<br />replace<br /><br />
  4. daniel5455 New Member

    i got passed this part but it says the database is in use how do i stop it.
  5. bradmcgehee New Member

    If you get a database is in use message, you must have one or more connections to the database. You can use Enterprise Manger to identify the connections (Current Activity, Process Info) to the database in question and then KILL the connection (Right-click on the connection), assuming this won't get anyone mad at you.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. satya Moderator

    Also when you're running this script from QA make sure you're not using the same database.

    HTH

    Satya SKJ

Share This Page