Moving/Copying Entire SQLServer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Moving/Copying Entire SQLServer

Author

Topic

dbaman
Starting Member

46 Posts

Posted - 2008-12-17 : 10:54:23

I have SQLServer 2005 running on WIndows 2003 Server. This server is soon going to be unavailable. I have setup another Server with Windows 2003 Installed as of now. What is the best/easiest way to copy/move the entire SQLServer2005 from old server to the new server. I have approx. 40 Database running on the old server.

Thanks.

R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-17 : 10:55:47

Backup/restore or Attach/detach.

dbaman
Starting Member

46 Posts

Posted - 2008-12-17 : 10:58:19

quote:
Originally posted by sodeep

Backup/restore or Attach/detach.

You mean do this for each database..? Any other way? I am looking for a way (if there is any) that can do for all database at once..

R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-17 : 11:36:33

You can write or find written script to do that? There are millions of posts regarding this issue. Please search it in SQLteam.

swekik
Posting Yak Master

126 Posts

Posted - 2008-12-29 : 14:42:55

You can create 2 batch files.
Include this in one batch file

@echo off
cls
SET SRVNAME='servername'
SET STGRBACKUPPATH='location'

REM **********************************************

isql -S%SRVNAME% -E -Q"set nocount on;select 'Backup database ' + [name] + ' to disk= N' + char(39) + %STGRBACKUPPATH% +'\' + [name] + '.bak' + Char(39) from sysdatabases where [name] not in ('master','msdb', 'model', 'tempdb') order by name" -dmaster -oBackup.sql -h-1 -w3000 -n

copy backup.sql %STGRBACKUPPATH%

In another batch file include this

@echo off
cls
SET SRVNAME='servername'
SET STGRBACKUPPATH='Location'

echo.| time | find /v "new" > %STGRBACKUPPATH%\start.txt
isql -S%SRVNAME% -E -i%STGRBACKUPPATH%\backup.sql
echo.| time | find /v "new" > %STGRBACKUPPATH%\finish.txt

Make sure to mention the servername & location
All you have to do is execute the second batch file.It will take all the database backups to the specified location .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 15:15:42

The easiest way to do this is to ensure that the new server was installed using the same exact paths as the old server and that all of the same drives and path for the database files are present on the new server. Then you just stop the SQL Server service on the old server and the new server, copy all database files to the new server in the same location as the old server, start the SQL Server service on the new server, and then you are good to go. I've done this numerous times. The benefit to this approach is that all databases, logins, jobs, etc... get moved to the new server without the need to run any other scripts.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources