Copying mdf file

[ronnie001]

Hi All,

Is it possible to shut down a SQL Server and then manually copy the MDF and LDF file to another server? (I attempted this but could not copy the files because they were in use)

I tried to backup my SQL database using brightstor and I recieved the following error when i tried to restore to my new server:

Error: 823, Severity: 24, State: 2

I/O error (bad page ID) detected during read at offset 0x00000000012000 in file 'D:\MSSQLData\MSSQL\data\Userinfo_Data.MDF'.

Any advise would be appreciated.

Cheers,

Ronnie

 

[SQLBill]

The .mdf and .ldf files can only be copied when they are not in use. You must either:
1. detach the database and copy the files
2. stop the SQL Server services and copy the files.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

I'd try backing up and restoring the database via the native commands (see BACKUP and RESTORE in BOL).

If you still get the bad page error do a DBCC CHECKDB on your production server. You have a database problem on your production server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ronnie001]

Thanks all.

SQLBill,
I tried stopping the MSSQLServer service and attempting to copy the file but it said file in use. Also I set the SQL service and any other services that I thought would use SQL to manual and restarted the server. I still couldn't copy the file.
I am concerned about detaching and attaching the file from my current database due to the I/O error that I am getting. Worried that it wont reattach back to the production server once I have detached it.

mrdenny,
I am unable to backup and restore as the database is approx 60GB, and I don't have that much free disk space on my production server. Is there anyway to get SQL Enterprise Manager to backup to a remote file system?

Thank you.

Ronnie

 

[SQLBill]

We are getting that same error also. (We migrated to a new server and found out that our app does not support our setup). Microsoft says that SP4 resolves the 823 error, but we haven't found that to be true - yet.

I ran DBCC CHECKDB on my database, then I ran DBCC DBREINDEX on the tables with problems (the errors from DBCC CHECKDB showed they were indexes with the bad page error).

That resolved the issue for a day, then it came back. In our case, we are looking at the fact that our SAN was formatted while attached to a Win2K server and we migrated the server to a Win2k3 server. We are going to format the SAN via the Win2k3 server and hope that resolves our issue. If not, we have to do as the vendor says - rebuild the server with Win2k3 and no service pack. Would have been nice if they had told us at the beginning they don't support their product on Win2k3 SP1 (we even told them that was what we were going to use).

-SQLBill

Posting advice: FAQ481-4875

 

[maswien]

If the account that run the SQL Server service has the write permission on the remote machine, you should be able to backup the whole database to it, all you need to do is use UNC path instead of the local path.

 

[ronnie001]

Hi maswien,

Where so you mean I should enter the UNC path?

Cheers,

Ronnie

 

[mrdenny]

When you do the back there is a field that you can type in that has the path to the file you are backing up to. In that field type in the full path to the remote location you want to backup to.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ronnie001]

Hi All,

I was able to backup my database over the network using:

BACKUP DATABASE Foo TO DISK = '\\myserver\myshare\foo.bak' WITH INIT

And then I ran a DBCC CHECKDB and it came back fine after 30 mins.

Many thanks for all your assistance.

Cheers,

Ronnie