E' necessario avere i permessi per accedere via file system al database remoto ed una user sql amministrativa.
Ecco i passaggi:
- collego via net use al sql server
- killo le connessioni attive sul db che deve essere copiato
- eseguo lo shrink del db e lo metto offline
- copio i file mdf ed ldf in locale
- metto online il db
copydb.bat:
rem killo in processi in uso sul db che deve essere copiato, lo setto offline, copio i file e lo rimetto online
echo on
echo utilizzo:copydb.bat NOMESERVER NOMEDB SQLUSER SQLPASSWORD DOMINIO USER SQLPATH LOCALPATH
echo esempio:"copydb.bat" sqlserver1 pubs sa password domain01 myusername "\\sqlserver1\e$\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf" "\\sqlserver1\e$\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf" "c:\sqllocal"
set NOMESERVER=%1
set NOMEDB=%2
set SQLUSER=%3
set SQLPASSWORD=%4
set DOMINIO=%5
set USER=%6
set SQLMDFPATH=%7
set SQLLDFPATH=%8
set LOCALPATH=%9
net use \\%NOMESERVER% /user:%DOMINIO%\%USER%
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -i "db_kill_connections.sql"
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "DBCC SHRINKDATABASE (%NOMEDB%,10)"
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "alter database %NOMEDB% set offline"
copy %SQLMDFPATH% %LOCALPATH% /Y
copy %SQLLDFPATH% %LOCALPATH% /Y
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "alter database %NOMEDB% set online"
pause
db_kill_connections.sql:
DECLARE @spid int
-- Declare a cursor For process records that concern with MyDatabase.
DECLARE sysprocesses_cursor SCROLL CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = db_id('Pubs')
OPEN sysprocesses_cursor
FETCH NEXT FROM sysprocesses_cursor
INTO @spid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN -- Clear up processes.
print 'kill @spid'
print @spid
EXEC ('KILL ' + @spid)
FETCH NEXT FROM sysprocesses_cursor INTO @spidEND
CLOSE sysprocesses_cursor
DEALLOCATE sysprocesses_cursor
No comments:
Post a Comment