March 25, 2010 at 12:14 am
Hi,
No problem if you are going to move your databases through dynamic script for every database, Its takes time and rick too.
The better way to move is write a dynamic query with MOVE option something like...
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for database files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:\data\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
.
.
.
.
.
---Use
Alter @Name with Move option to move all your database
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- That's way to move all your databases and with single restart of services.
Thanks & Regards,
Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India
March 25, 2010 at 5:23 am
John Mitchell-245523 (3/23/2010)
muthukkumaran (3/23/2010)
Use the alter database method instead of detach/attach.Write a dynamic sql script to move the files.
First try to do in your Dev/test server.
This is one way of doing it, but unfortunately it involves stopping SQL Server and moving every data and log file manually. Compare that with doing a backup and restore (assuming you have sufficient disk space). You can script the backups and the restores, and then go and have a cup of tea while the script is running. If your databases have a lot of free space in them then this method has even more advantages since backups will only back up data, whereas moving a file involves moving the all free space as well.
John
Incorrect statement. Everything associated with the detach, file movement, reattach can be done with automated scripting that is created with a script that reads sys.databases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 26, 2010 at 9:23 am
have a look at
http://msdn.microsoft.com/en-us/library/ms189493.aspx
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
March 26, 2010 at 9:33 am
gergav33 (3/26/2010)
have a look athttp://msdn.microsoft.com/en-us/library/ms189493.aspx
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
the objective here is to move an existing file. above would not work for primary file and would be slow and risky for any other file.
---------------------------------------------------------------------
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply