I trying to write a script that will detach a DB copy the physical file to a new location then remount the original DB.
the Problem that i am having it detaching the DB as i get error telling me that the DB is in use.
There is an Access front end open with and ODBC connection to the SQL DB and this needed to be open to as it is where the command to create the Copy is coming from.
I have tried the following code
USE master ALTER DATABASE EclipseSQL SET SINGLE_USER with no_wait
exec sp_detach_db 'EclipseSQL'
exec xp_cmdshell 'MKDIR "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices" '
exec xp_cmdshell 'ECHO Y| cacls "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" /p everyone:f'
exec xp_cmdshell 'xCopy "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices\" '
exec sp_Attach_db 'EclipseSQL', 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf'
i get the following output from this
Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'EclipseSQL'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'EclipseSQL' because it is currently in use.
(2 row(s) affected)
(2 row(s) affected)
(3 row(s) affected)
Msg 1801, Level 16, State 3, Line 1
Database 'EclipseSQL' already exists. Choose a different database name.
any know how i get disconnect all users no questions asked?
Try:
Code Snippet
alterdatabase EclipseSQL setread_onlywithrollback immediate
go
alterdatabase EclipseSQL setread_writewithrollback immediate
go
|||That did the trick thanks
没有评论:
发表评论