Restoring a database to a new server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restoring a database to a new server

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.
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
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 />
i got passed this part but it says the database is in use how do i stop it.
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
Also when you’re running this script from QA make sure you’re not using the same database. HTH Satya SKJ
]]>