copy db from one server to another

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  October 30, 2009 at 3:42 am

  #212739

  Hi,

  I have to copy one database from one server to another.

  as i can't opt back up and restore bcoz of shortage of space,

  i decided to copy the mdf & ldf files then attach the same.

  i copied into another server

  i ran the following script:

  create database DacsTrans

  on (filename ='\dbname.MDF')

  log on (filename = 'dbname.LDF')

  for attach

  but while attaching i'm getting the following error:

  Msg 1813, Level 16, State 2, Line 2

  Could not open new database 'DacsTrans'. CREATE DATABASE is aborted.

  Msg 824, Level 24, State 2, Line 2

  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3067392; actual 0:0). It occurred during a read of page (1:3067392) in database ID 7 at offset 0x000005d9c00000 in file 'E:\DB Backup\DacsTrans.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  i tried to create a new database with the same ldf& mdf in different location. then replaced the mdf file, deleted and thought i can rebuild the log file.

  but as i can't change the sysdatabase status(sql server 2005) i could not follow that method.

  any suggestions and advices are highly appreciated.

  Regards,

  Anamika

• Johan Bijnens

  SSC Guru

  Points: 135364

  More actions

  October 30, 2009 at 4:24 am

  #1073008

  If both sqlservers can "see" eachother and the destination service account has read auth on the source servers backup folder, you can restore using the source server bak files

  restore database test

  from disk='\\Sourceserver\d$\mssql.1\mssql\backup\testFILL.bak'

  -- add with move if needed

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  October 30, 2009 at 4:29 am

  #1073012

  thanks,

  but to take a back up i don't have space in the server. it'z a 200gb database.

  regards,

  anamika

• Johan Bijnens

  SSC Guru

  Points: 135364

  More actions

  October 30, 2009 at 4:44 am

  #1073017

  Anamika (10/30/2009)

  ---

  thanks,

  but to take a back up i don't have space in the server. it'z a 200gb database.

  regards,

  anamika

  You can also produce the backup to a remote location, provided you grant the service account write auth at a shared location.

  Backup database test to disk='\\mysafezone\backupshare\testFull.bak'

  After that you can restore it as shown above.

  If you want to copy/paste the mdf, ndf and ldf files, you will have to take the database offline during the whole copy operation !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  October 30, 2009 at 5:02 am

  #1073021

  thanks,

  i started trying bakup. let it get complete .i'll restore and ping you back.

  ( while taking the maf & ldf, i've dettached the database)

  thanks,

  regards,

  anamika

• Lewis Dowson

  SSC Eights!

  Points: 988

  More actions

  October 30, 2009 at 7:40 am

  #1073121

  Use this for free for 14 days.... saves a whole lot of hassel

  http://www.red-gate.com/products/SQL_Backup/index_v2.htm

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  November 2, 2009 at 2:32 am

  #1073712

  the back up and restore from one network to another worked properly.

  thanks ALZDBA

  regards

  anamika

• Kotturi

  SSChasing Mays

  Points: 611

  More actions

  November 2, 2009 at 2:41 pm

  #1074006

  You can run DBCC CHECKDB with REPAIR_FAST option. This will fix your current database. Then you can use the detach and attach metod to copy database.

• Johan Bijnens

  SSC Guru

  Points: 135364

  More actions

  November 3, 2009 at 1:45 am

  #1074151

  HTH

  Keep in mind that if you are using sqlauthentication and didn't migrate the sqluserid including the SID, you must resync the user ids using:

  -- to be executed per sqluserid !

  exec sp_change_users_login @Action = 'Update_One',@UserNamePattern = 'mysqlID1', @LoginName = 'mysqlID1'

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me