November 13, 2009 at 9:47 am
I have more than 300 dbs which i have to move from 2 different drives to a single drive. Is there any script that can automate this process. I dont want to do it manually for these many databases as it may take a week for me.
thanks
November 13, 2009 at 9:56 am
I am not sure of any automation for this.
Last time I had run into the same situation, I had to script detach and the attach for one database and reused it for other databases, which was a bit tedious.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 13, 2009 at 10:49 am
I like to write scripts that generate scripts to execute. Please switch SSMS into text mode results and have a look this:
To generate script that will detach databases you can use this statement.
select 'exec sp_detach_db ''' + name + '''' from sys.databases
where name not in ('master', 'tempdb', 'model','msdb')
This script generates script to attach databases on new location - you may need to adjust it to a different path, or to split data and log files but the basic principle is the same.
declare @newPath varchar(128)
--this is where files will be stored.
set @newPath = 'D:\Databases\'
;with filesCTE as
(
select b.name [db name], physical_name [old file name],
@newPath + --new path for files
reverse(left(reverse(physical_name), charindex('\', reverse(physical_name), 1) - 1)) [file name], --remove existing path
row_number() over(partition by b.name order by b.name ) [file number]
from sys.master_files a inner join sys.databases b on a.database_id = b.database_id
where b.name not in ('master', 'tempdb', 'model','msdb')
)
select 'exec sp_attach_db ' + quotename(b.name) +
convert(varchar(max),
(select ', @filename' + convert(varchar, [file number]) + --file number
'=' + quotename([file name], '''')
from filesCTE where [db name] = b.name
order by [file number]
for xml path('')
))
from sys.databases b
where b.name not in ('master', 'tempdb', 'model','msdb')
So now, just execute generated detach script, then move files physically and then run generated attach script. It should work.
Regards
Piotr
...and your only reply is slàinte mhath
November 13, 2009 at 11:07 am
if you can do this via the alter database modify file option would be better, won't lose dbowner, dbid or possibly default database foe user.
been a while since I used this and have two versions, not sure why, so test it. one addition may be to put [] around names
/****** Object: StoredProcedure [dbo].[MoveAllDatabaseFile] Script Date: 11/13/2009 18:04:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--This script create a store procedure that Move All MDF - LDF Files
--Wrote by Avi Grinberg
create procedure [dbo].[MoveAllDatabaseFile]
@FileType char (3),@fullpath varchar(100)
as
if @FileType not in ('MDF','LDF','mdf','ldf')
begin
print '@FileType must be MDF or LDF (or lower case) and @fullpath should be like N''C:\DatabaseFiles\'' the script was terminated!.'
print 'example of right syntax - exec MoveAllDatabaseFile ''MDF'',N''C:\DatabaseFiles\'''
return
end
if @fullpath not like '%\'
begin
print 'The @fullpath must end with a ''\'' ,this script is terminated!. '
return
end
declare @dbname as varchar (100)
declare @LogicalFileName as varchar (100)
declare @PhysicalFileName as varchar (100)
declare @runalter as varchar(500)
--Case LDF
if @FileType = 'LDF' or @FileType = 'ldf'
begin
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
select sysdb.name as 'DBName',masterfiles.name as 'LogicalFileName',substring(right(physical_name,charindex('\',reverse(physical_name))),2,100) as 'PhysicalFileName'
from sys.master_files masterfiles,sys.sysdatabases sysdb
where masterfiles.database_id = sysdb.dbid
and masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4
end
--Case MDF
else
begin
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
select sysdb.name as 'DBName',masterfiles.name as 'LogicalFileName',substring(right(physical_name,charindex('\',reverse(physical_name))),2,100) as 'PhysicalFileName'
from sys.master_files masterfiles,sys.sysdatabases sysdb
where masterfiles.database_id = sysdb.dbid
and masterfiles.type_desc = 'ROWS' and masterfiles.database_id > 4 --not take system DB's
end
--Start execute MDF or LDF
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @dbname,@LogicalFileName,@PhysicalFileName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @LogicalFileName + ' , filename = N'''+ @fullpath + @PhysicalFileName + ''')'
print (@runalter)
FETCH NEXT FROM CRS_db INTO @dbname,@LogicalFileName,@PhysicalFileName
END
CLOSE CRS_db
DEALLOCATE CRS_db
---------------------------------------------------------------------
November 13, 2009 at 11:31 am
Tara-1044200 (11/13/2009)
I have more than 300 dbs which i have to move from 2 different drives to a single drive. Is there any script that can automate this process. I dont want to do it manually for these many databases as it may take a week for me.thanks
This can be done with SQLCMD
SQLCMD:
Use:
:CONNECT <server>\,<instance1>
sqlcmd code
go
to switch to another instance.
sqlcmd
-S <instance name>
-d master
-E
-i <script name where the code below was saved>
-v database ="<database>" logfilename="<current log file>" newlogpath ="<new log file path>" originallogpath ="<original log file path>" datafilepath ="<data file path>"
-o <output file>
declare @cmd varchar(2000);
select 'drop connected users'
while (select count(*) from [master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID('$(database)'))>0
begin
select @cmd='kill '+CAST(spid as varchar(5)) from [[master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID('$(database)');
exec [dbo].[sp_executesql] @cmd;
end
select 'setting database to SINGLE_USER mode'
alter database $(database) set SINGLE_USER;
select 'updating database settings to move file'
alter database $(database) modify file (name='$(logfilename)', FILENAME='$(newlogpath)');
select 'detaching database'
exec [master].[dbo].[sp_detach_db] @dbname = N'$(database)', @keepfulltextindexfile=N'true';
select 'moving file'
exec [dbo].[xp_cmdshell] 'move "$(originallogpath)" "$(newlogpath)"';
select 're-attaching database'
create database [$(database)] on (filename = N'$(datafilepath)'), (filename = N'$(newlogpath)') for attach;
go
November 13, 2009 at 12:30 pm
If you go for the alter database..modify file method, (which I recommend) you will need to offline the database first and bring it back online once you have moved the file(s)
select 'alter database ', name, 'set offline with rollback immediate' from master.sys.databases where database_id > 4
run modify file commands
move files
select 'alter database ', name, 'set online' from master.sys.databases where database_id > 4
Of course however you do this it would be sensible to back the databases up first
---------------------------------------------------------------------
November 17, 2009 at 10:53 am
how can we write a script using cursor like
1.select 80 databases one by one using cursor
2. dettach database
3. Move file using xp cmd shell script from location A to B
4. Attach database from location B
These steps can be executed in a cursor ona after another.
November 17, 2009 at 11:00 am
don't use a cursor, use dynamic SQL to create your commands as shown above
Are all the files moving from the same place to the same new location? If not how spread about are they?
---------------------------------------------------------------------
November 17, 2009 at 11:09 am
all fiels will be moved wth in the same computer from one drive to another.
November 17, 2009 at 2:00 pm
then as this is a one-off task you may as well keep it simple and use windows explorer to move the files.
---------------------------------------------------------------------
November 18, 2009 at 10:04 am
Piotr.Rodak (11/13/2009)
I like to write scripts that generate scripts to execute. Please switch SSMS into text mode results and have a look this:To generate script that will detach databases you can use this statement.
select 'exec sp_detach_db ''' + name + '''' from sys.databaseswhere name not in ('master', 'tempdb', 'model','msdb')
This script generates script to attach databases on new location - you may need to adjust it to a different path, or to split data and log files but the basic principle is the same.
declare @newPath varchar(128)
--this is where files will be stored.
set @newPath = 'D:\Databases\'
;with filesCTE as
(
select b.name [db name], physical_name [old file name],
@newPath + --new path for files
reverse(left(reverse(physical_name), charindex('\', reverse(physical_name), 1) - 1)) [file name], --remove existing path
row_number() over(partition by b.name order by b.name ) [file number]
from sys.master_files a inner join sys.databases b on a.database_id = b.database_id
where b.name not in ('master', 'tempdb', 'model','msdb')
)
select 'exec sp_attach_db ' + quotename(b.name) +
convert(varchar(max),
(select ', @filename' + convert(varchar, [file number]) + --file number
'=' + quotename([file name], '''')
from filesCTE where [db name] = b.name
order by [file number]
for xml path('')
))
from sys.databases b
where b.name not in ('master', 'tempdb', 'model','msdb')
So now, just execute generated detach script, then move files physically and then run generated attach script. It should work.
Regards
Piotr
Hello,
your script is excellent, but what if FullTextCatalog is defined on the database that is to be deatched and then attached.
The database full backup will fail, if that is not taken into consideration !
Also, the script can be modified and FT can be taken care too !
Hope this helps !
\\K 🙂
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
November 18, 2009 at 12:55 pm
Oh yes, this script only reflects requirements specified by OP. You can definitely taylor it to suit your needs.
Regards
Piotr
...and your only reply is slàinte mhath
November 18, 2009 at 1:14 pm
And if this sort of thing will happen more than once, consider storing your .mdf and .ldf names in a table on a database that doesn't tend to get moved. Then just apply the script to the names (and possibly paths) you pull out of the table, which could make things much easier.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply