SQL Server Performance Forum – Threads Archive
Copy/Paste database to another server
This probably is real basic stuff but I am new to this and am starting with some very basic questions. I have a database on SQL 2000. The xxx data.mdf is about 25 megs. The xxx log .ldf has grown to about 60 megs for some reason. I copy my database from this server to a warm backup server across a very, very slow network. I stop the production server and copy only my database and log files to the desktop…. winzip it….restart sql server, copy across the network to the other server…. unzip it… stop the other server and extract those 2 files to the appropriate data folder on the sql server. Even when zipped from 80 megs to about 10, it will take over an hour to come across. My questions:Should I copy both files over or can I get away with just the mdf?
or should I be including more: master, msdb, temp?
I do this each morning as an offsite warm recovery plan as well as provide up to date info only (no data input) for users at site 2. Replication etc has failed because of network unreliability Thanks
Merv[?]
No, your need both files, mdf and log in order to use sp_dbattach to backup server. In log, there is transactions not actually in database (mdf).
One option may be use "checkpoint" to force transaction log write into database (mdf).
But, why no use logic backup to copy to backup server?.
Master, msdb and tempdb are not necesary to have a production database backup copy.
In case main server crash, well, if you need database on line as soon as posible, then master, msdb and tempdb are necesary.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
You can take help of Log shipping in this case to keepup the warm standby server for transactions.
If you’ve slow network then issues might comeup due to failure or consistency of file copy during the process. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks guys. I do a backup but keep it local. I tried a backup over a network… even a transactional is sssslooww. When I tried replication, i just got too many errors. The copu/zip/paste seemed to have been the only way
Performing backup over a network will be slow as expected and it totally depends on the network capacity. Its always suggested to do a local backup and copy accross the network to the standby server which is reliable and will not be any stress on the SQL server too. Take help of the network admin to trap any bottlenecks on the network, keep an eye on event viewer & SQL error log for any information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
– backup log db_name with truncate_only
– dbcc shrinkdatabase(db_name, 10)
– run full backup of the database
– zip the backup file and copy it
– unzip it and run a restore.
– run sp_change_users_login to fix broken login if needed. Bambola.
Bambola’s approach will help in case if you need to invoke warm backup server as live server. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>