en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אוג6

Written by: ronen ariely
06/08/2011 12:26 RssIcon

העברת כל הקבצים של מסדי הנתונים למיקום חדש

צריכים להעביר את הקבצים של מסדי הנתונים למיקום אחר? הדיסק מלא ורוצים להעביר את הקבצים לדיסק אחר? ננסה להראות כיצד ניתן לבצע זאת באמצעות שמוש בפונקציות המובנות Detach וכן Attach

העברת הקבצים של מסד נתונים (בודד) למיקום חדש

הרעיון בו נעזר מתבסס על שלושה שלבים:

1.       העתקת הקבצים מהמקום הנוכחי למקום החדש.

2.       ביצוע Detach למסד הנתונים (הוצאת המסד מרשימת המסדים בשרת)

3.       חיבור מסד הנתונים עם הקבצים במיקום החדש אל השרת Re-attach

-- determine the name and the current location of all files of a database

use [DatabaseName]

go

sp_helpfile

go

-- determine the name and the current location of all files that a database

DECLARE @DB AS NVARCHAR(50)

select @DB = name from sys.database_files where type = 0

DECLARE @Log AS NVARCHAR(50)

select @Log = name from sys.database_files where type = 1

 

-- determine the DATA File name

select

      physical_name

      ,PATINDEX('%\%',REVERSE(physical_name))

      ,RIGHT(physical_name,PATINDEX('%\%',REVERSE(physical_name))-1)

from sys.database_files

where type = 0

 

-- determine the Log File name

select

      physical_name

      ,PATINDEX('%\%',REVERSE(physical_name))

      ,RIGHT(physical_name,PATINDEX('%\%',REVERSE(physical_name))-1)

from sys.database_files

where type = 1

 

-- we can not Detach database which have open connections

-- If you want to drop all the connections to a database immediately, you can use the following commands:

USE master

GO

ALTER DATABASE [DatabaseName]

SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [DatabaseName]

SET ONLINE

 

--Alternatively you can kill all the processes using a database with this code:

USE master

go

DECLARE @dbname sysname

SET @dbname = 'name of database you want to drop connections from'

DECLARE @spid int

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)

WHILE @spid IS NOT NULL

BEGIN

EXECUTE ('KILL ' + @spid)

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid

END

 

-- Detach the database

use master

go

sp_detach_db 'mydb'

go

 

-- Re-attach to the new location

use master

go

sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'

go

 

סגירת כל ההתחברויות הפתוחות למסד הנתונים והגדרה שלו לשימוש משתמש בודד

נושא חשוב שיש לזכור הוא שפעולת detach לניתוק מסד נתונים לא תצליח אם ישנם משתמשים מחוברים למסד הנתונים. הסקריפט הבא יראה כיצד נוכל לבצע ניתוק של כל המשתמשים המחוברים למסד הנתונים ולהגדיר אותו לשימוש משתמש בודד כדי שלא יתחברו לנו משתמשים נוספים עד לסיום העבודה. נבצע detach למסד הנתונים וכמובן בסיום נחזיר את מסד הנתונים לעבודה עם משתמשים מרובים

USE master

GO

 

--SET SINGLE_USER & drop all Connection

ALTER DATABASE [AdventureWorks]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

 

--DETACH DataBase

EXEC sp_detach_db @dbname = 'myDbName', @skipchecks = 'true'

GO

 

--SET MULTI_USER

ALTER DATABASE [AdventureWorks]

SET MULTI_USER;

GO

 

עבודה על מסדי נתונים מרובים

הרעיון בו נעזר מתבסס על מה שביצענו עבור הבערת הקבצים של מסד נתונים בודד ושימוש בשלושת השלבים. נעשה שימוש בפרוצדורה sp_msforeachdb כדי לבנות בצורה אוטומטית סקריפט להעברת כל הקבצים של כל אחד ממסדי הנתונים שיש לנו. בשלב הבא נוכל להריץ את הסקריפט במלואו או על חלק ממסדי הנתונים שנבחר.

/*

1. copy all databases to the new location

exept system DB: master,msdb,tempdb,model

 

2. run the next query. it will build you a query for all databases

*/

 

declare @NewDir as nvarchar(100) = N'E:\SQL_DAtaBases\'

declare @SQL as nvarchar(MAX) =

      'USE [?]

     

      IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

      BEGIN

     

      PRINT ''sp_detach_db '''''' + DB_NAME() + ''''''''

      PRINT ''GO''

     

      DECLARE @DB AS NVARCHAR(50),@Log AS NVARCHAR(50)

      select @DB =

            RIGHT(physical_name,PATINDEX(''%\%'',REVERSE(physical_name))-1)

            from sys.database_files

            where type = 1

      select @Log =

            RIGHT(physical_name,PATINDEX(''%\%'',REVERSE(physical_name))-1)

            from sys.database_files

            where type = 0

     

      PRINT ''sp_attach_db ''

      print ''          '''''' + DB_NAME() + '''''', ''''' + @NewDir + ''' + @DB + '''''''' + '', ''''' + @NewDir + ''' + @Log + '''''' ''

      PRINT ''GO'' + char(13)

     

      print ''/********************************************************/''

      END

      '

 

EXECUTE sp_msforeachdb @SQL

 

 

נספח 1: קישורים

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
http://support.microsoft.com/kb/224071