March 25, 2003 at 12:44 pm
Will the backup be faster? I dont mind 8 hours like it previously did on my 100 mbps network, but 4 G in 8 hours is too slow for a 40G file.
I have a log file showing thru windows explorer but did not choose any specific options during installation. I believe there are different backup options one can have for recovery.
If I backup on instance A and restore on instance B, is that not going to take twice as long? Once for the backup and once for the restore? If I can fix the primary filegroup problem and then copy the objects, I can rightaway use Query Analyzer to make sure it is okay. Eventually I would want to run off the external hard disk (my internal is only 80G, with about 10G left, 4 gig being used up every 3 months or so)
March 25, 2003 at 1:19 pm
YEs, I think it will be faster. Another option is to simply detach the database from instance A, copy the files (at least the data files, log files will be created new ones if they can't be found) to the external disk and then attach these new files to instance B. You would of course also reattach the database on instance A as soon as the files have been copied.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 3:43 pm
I can probably lookup how to attach/detach the database, but what about copying? Can I use windows explorer, or the bcp utility? What are the extensions of the files I need to copy?
March 26, 2003 at 3:24 am
Detaching the database is done with the system stored procedure sp_detach_db. This will drop your database from the server instance, but will not delete the files. You can then copy the files (data files i.e. .mdf, .ndf etc and log files if you want (.ldf)) as normal files with Windows Explorer. The reason you can't copy them without detaching the database is because SQL Server has them open and you can not be sure they are in a consistent state. Then when you're done copying you use sp_attach_db to reattach the database to the server instance. Look in BOL for more info on detaching/attaching databases, and there is probably also some articles here at the site on the matter.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 31, 2003 at 4:31 pm
I did EXEC sp_detach_db 'aoc','true' and it detached the database, but when I did EXEC sp_attach_db @dbname='aoc' it said it was expecting a parameter @filename1. What am I doing wrong?
I had to reformat my drive C and reinstall W2000 server. I still have my detached database on d:\programfiles\mssqlserver\mssql\data.
When I install SQLServer2000 again, and choose the same directory (d:\progf..\data) will it overwrite the aoc database? Should I choose a different directory for the data and attach the aoc database from the old directory where it now resides(if that can be done, then how)?
April 1, 2003 at 12:07 am
Simply installing SQL Server 2000 with the same data directory will not overwrite your user database aoc, but neither will it attach it so that you can use it in SQL Server. After installing you can attach it manually though. If you are unsure, move the files while installing and then move them back and attach them. Look up sop_attach_db in BOL for syntax, and there is also some good info in this article: http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply