commit transation log without bkup- Copy mdf and ldf to SAN then restore

[anam]

We have a 30+GB database, this database has some files in SQL and some files in another application.

We would like to implement the followings, if it is possible:

1- Take database offline
2- Commit everything
3- Copy .mdf and .ldf files to SAN
4- Snap copy everything from San
5- Take database online.

My questions here are:

1- Can we restore from the copy files we will place into SAN?

2- Can you commit transaction logs without doing backup?

I read http://forums.databasejournal.com/sh...ghlight=rename
Article but I am not sure if this would apply here for the restore part.

anam

[MAK]

checkpoint

BTW. Why cant you take a backup and restore instead of taking offline.

[Claire]

Try the following process

1.Do Checkpoint

2.sp_detach_db 'dbname'

3.Copy the mdf and ldf into SAN

4.sp_attach_db 'dbname','filename1','filename2','...

[anam]

The reason why we don't do bkup/rstr because it takes time, plus there would be some other files from the other application which it needs to be backed up as well so it is preferable to copy to SAN then restore the files in case of emergency.

Thanks
Anam

[anam]

Hi Claire;

Thanks for your reply. Can I do Alter database offline instead of attach/detach? Thanks.

Anam

[Claire]

The reason you have to use detach and attach is because you are going to move mdf and ldf file to somewhere which is not default sql server data file location.Which is going to cause problem , beacuse it's irrational to ask SQL Server to look for where the data and log file located.

Alter database offline and online is for some other use. For for backup and restore use.

[anam]

Claire;

What if you have users using the database? Can use kill the users first before you detach it?

Anam

[MAK]

That is why you need to backup istead of detach and attach. Do you take full backup or differential or log backup for that database? If you do, just copy the backup files and log files to SAN.

Anyway, for killing all the users on a particular database please refer.

usp_killprocess
http://www.databasejournal.com/featu...le.php/2174031

[anam]

As I said, management doesn't want to do backup/rstr.

The other question I have is that can you attach a databse to files which are not on the same server after you detach them. In another words, if you have the .mdf and .ldf files on another server, can you attach the database to them?

Help

Anam

[Claire]

Hi...whatever you detach previously need to be in the attach filename list.

Say you have 1 mdf , 2 ndf and 1 ldf from the database.
When you attach the database, all the 4 file need to be include in the parameters.

sp_attach_db
@dbname = 'dbname',
@Filename1 = 'filepath1',
@FileName2 = 'filepath2',
@FileName3 = 'filepath3',....

Doesnt matter whether those file are from different server or not.

[Claire]

For killing process, I always run the following statement, copy the result from the result pane , then paste and execute again in the query analyzer.

select 'kill '+convert(varchar(5),spid) from master..sysprocesses where db_name(dbid) = 'DBName'

[anam]

Claire;

I used filename as you recommended and tried to read from another server, but got the following error:

Server: Msg 5110, Level 16, State 2, Line 1
File '\\SERVER\E$\TEST\DBtest.mdf' is on a network device not supported for database files.

[MAK]

You cannot connect/create database ( .mdf,.ldf file) on a network using UNC path.

you can enable that feature by using traceon command but not recommended.

Copy the file to the server locally and then do sp_attach

[Claire]

Hi anam,

Mak's suggestion is right.Copy the file from different server and store in the server where ur SQL Server located.

Then use the syntax for sp_attach_db by specifying the exact filename after you move all the data files into one server.