Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Move Physical Files Expand / Collapse
Author
Message
Posted Monday, April 9, 2012 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Perry Whittle (4/9/2012)
opc.three (4/9/2012)
but having the database in single-user mode when it is brought online again is useful as it gives us a chance to run some verification checks on the database after the data files have been moved

What verification checks?
The database will either come online or fail if the path\filename is not correct

Basically anything the admin wants to verify before allowing users back into the database. A check to make sure the file paths look good in sys.master_files is a good one that comes to mind for this scenario. It's also a good idea to have a script to exercise basic functions and data in a database before turning it over to users. I have one for each of my databases that support user applications. Do you not have verification scripts for your databases? Or do you just assume that if a database is online that everything must be in working order?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1280281
Posted Monday, April 9, 2012 11:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 6,752, Visits: 14,400
If you'd bothered to read the code in the post it reads as follows

alter database DatabaseName set single_user with rollback immediate
go
-- Set databae Offline
alter database DatabaseName set Offline
go
--- Cut and paste the files from the original location to the new location
-- Be sure to write the name and new path exactly
alter database DatabaseName
Modify file (Name = logicalName,Filename = 'DriveLetter:ewPath\DataFileName.mdf/ndf or ldf')--Create one per every file
go
---Set database backonline
alter database DatabaseName set Online
go
--Set Database back to multi_user
alter database DatabaseName set multi_user

In this context setting the database single user is unnecessary since it uses rollback immediate anyway. I don't need to verify a file move, if I got it wrong the database will not start, then i'll go check sys.master_files, which incidentally resides in master so whats the point of keeping the database single user. What if you're not the user who gets the first connection?
Maybe you'd like to point out exactly what it is you verify? If sys.master_files has an incorrect path, the database will not start!


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1280294
Posted Monday, April 9, 2012 11:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Perry Whittle (4/9/2012)
If you'd bothered to read the code in the post it reads as follows

alter database DatabaseName set single_user with rollback immediate
go
-- Set databae Offline
alter database DatabaseName set Offline
go
--- Cut and paste the files from the original location to the new location
-- Be sure to write the name and new path exactly
alter database DatabaseName
Modify file (Name = logicalName,Filename = 'DriveLetter:ewPath\DataFileName.mdf/ndf or ldf')--Create one per every file
go
---Set database backonline
alter database DatabaseName set Online
go
--Set Database back to multi_user
alter database DatabaseName set multi_user

In this context setting the database single user is unnecessary since it uses rollback immediate anyway. I don't need to verify a file move, if I got it wrong the database will not start, then i'll go check sys.master_files, which incidentally resides in master so whats the point of keeping the database single user. What if you're not the user who gets the first connection?
Maybe you'd like to point out exactly what it is you verify? If sys.master_files has an incorrect path, the database will not start!

I did read the code. Did you read my post? I noted that you could skip setting it to single_user before taking it offline, as you had shown it, and that setting the database to single_user could be done while the database was offline. Granted, verifying sys.master_files may not uncover anything interesting, however what if you had multiple files to move and missed one? I am not sure why you are arguing against a verification step. My comment regarding running any proprietary scripts to exercise functionality and data before turning it over to users still stands as well. It's standard practice, and one I would not forego, even for the described change where the perceived risk is low.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1280305
Posted Monday, April 9, 2012 11:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 6,752, Visits: 14,400
opc.three (4/9/2012)
however what if you had multiple files to move and missed one?

The database would not come online!
The only time this would differ in any way is with file group restores but that's a whole different scenario and not even relevant to this post\script.

I always issue my move commands before offlining the database that's the preferred\tested method for me.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1280311
Posted Monday, April 9, 2012 12:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Perry Whittle (4/9/2012)
opc.three (4/9/2012)
however what if you had multiple files to move and missed one?

The database would not come online!
The only time this would differ in any way is with file group restores but that's a whole different scenario and not even relevant to this post\script.

Sure it would. If I have two data files in my database, both on D:\, and I want to move one to E:\ and one to F:\, if I run an alter to move one to E:\ but I forget to move the other one to F:\ the database will happily come online with one data file on D:\ and one on E:\.

A verification step would help me see if the desired outcome were achieved...sometimes I find it useful to write the verification checks at a completely separate time from when I write the code to make such changes...sometimes even before writing the code to make the changes


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1280315
Posted Monday, April 9, 2012 12:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 6,752, Visits: 14,400
opc.three (4/9/2012)
If I have two data files in my database, both on D:\, and I want to move one to E:\ and one to F:\, if I run an alter to move one to E:\ but I forget to move the other one to F:\ the database will happily come online with one data file on D:\ and one on E:\.)

if you left the old file in place yes it would. If you moved the file instead of copying it would fail to come online.

Look it's your opinion and you're entitled to it, at the end of the day it's unnecessary to go single user first just take the data straight offline.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1280318
Posted Monday, April 9, 2012 12:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Perry Whittle (4/9/2012)
opc.three (4/9/2012)
If I have two data files in my database, both on D:\, and I want to move one to E:\ and one to F:\, if I run an alter to move one to E:\ but I forget to move the other one to F:\ the database will happily come online with one data file on D:\ and one on E:\.)

if you left the old file in place yes it would. If you moved the file instead of copying it would fail to come online.

I never said otherwise.

Look it's your opinion and you're entitled to it

I feel like I should thank you for allowing me my opinion

at the end of the day it's unnecessary to go single user first just take the data straight offline.

Agreed (note: this is the second time I am agreeing with you on this point).


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1280323
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse