sql modify file statement

Question

I'm having a SQL issue and have tried multiple combinations to no avail. I've tried commas, and also semicolons, after the first two ), all three ), just the last ), on all of these. No such luck. Please help me fix the query. Thanks in advance!

I have done due diligence and came across several sites (I can only post two links because of reputatoin) these: Create database using script at the default path? http://www.sqlteam.com/forums/topic.asp?topic_id=148732

USE master
GO
ALTER DATABASE location_cust_db
MODIFY FILE
(
    NAME = location_cust_db_data1,
    MAXSIZE = UNLIMITED,
)
(
    NAME = location_cust_db_data2,
    MAXSIZE = UNLIMITED,
)
(
    NAME = location_cust_db_data3,
    MAXSIZE = UNLIMITED,
)
GO

USE master
GO
ALTER DATABASE location_cust_db
MODIFY FILE (NAME = location_cust_db_data1, MAXSIZE = UNLIMITED)
MODIFY FILE (NAME = location_cust_db_data2, MAXSIZE = UNLIMITED)
MODIFY FILE (NAME = location_cust_db_data3, MAXSIZE = UNLIMITED)
GO

THIS WORKS, but very cumbersome to do 20 files.

USE master
GO
ALTER DATABASE location_cust_db
MODIFY FILE
(NAME = location_cust_db_data1,
MAXSIZE = UNLIMITED)


USE master
GO
ALTER DATABASE location_cust_db
MODIFY FILE
(NAME = location_cust_db_data2,
MAXSIZE = UNLIMITED)
GO

USE master
GO
ALTER DATABASE location_cust_db
MODIFY FILE
(NAME = location_cust_db_data3,
MAXSIZE = UNLIMITED)
GO

score 1 · Answer 1 · answered Jul 03 '13 at 19:43

Your third option is closest - each modification has to be in a separate ALTER DATABASE command, but you can leave out the USE statements since you're always running from MASTER :

USE master
GO

ALTER DATABASE location_cust_db
MODIFY FILE (NAME = location_cust_db_data1, MAXSIZE = UNLIMITED)
GO

ALTER DATABASE location_cust_db
MODIFY FILE (NAME = location_cust_db_data2, MAXSIZE = UNLIMITED)
GO

ALTER DATABASE location_cust_db
MODIFY FILE (NAME = location_cust_db_data3, MAXSIZE = UNLIMITED)
GO

..etc

score 0 · Answer 2 · answered Jul 03 '13 at 19:48

if I got the issue right, so this one would help you

begin
use master;
declare @com nvarchar(1024);
set @com='ALTER DATABASE location_cust_db MODIFY FILE (NAME = location_cust_db_data';
declare @i int;
set @i=1;
while(@i<20) begin
      set @com=@com+str(@i)+' ,MAXSIZE = UNLIMITED);'
      exec (@com);
      set @i=@i+1;
     end
end

score 0 · Answer 3 · answered Jul 03 '13 at 21:21

0

use [location_cust_db]
GO

declare csDBFiles cursor local fast_forward for
select
    'ALTER DATABASE [' + DB_NAME() + '] MODIFY FILE (NAME = [' + name + '], MAXSIZE = UNLIMITED)'
from
    sys.database_files

open csDBFiles

declare @stmt varchar(2000)

fetch next from csDBFiles into @stmt
while @@fetch_status = 0
begin
    print @stmt
    print 'GO'
    exec(@stmt)

    fetch next from csDBFiles into @stmt
end

close csDBFiles
deallocate csDBFiles
GO

answered Jul 03 '13 at 21:21

i-one

5,050
1
28
40

Possibly `where type_desc = 'ROWS'` at cursor declaration, or any other condition(s) if necessary. – i-one Jul 03 '13 at 21:27

sql modify file statement

3 Answers3