May 4, 2017 at 7:19 pm
Hi Everyone,
I'm using MS SQL 2005 - sqlcmd on a windows 2003 box (yes not complaint!!) restoring to a SQL 2012 server running Windows 2008R2
I created a job on SQL 2005 box (T-SQL and Command Exec)
1. TSQL step backup to SQL 2012 box via network share
Successful!
2. CMDEXC step - run sqlcmd to on SQL 2005 box to remotely restore the database on SQL 2012 box
sqlcmd -E -d master -S WINSRV_SQL2012 -Q "RESTORE
DATABASE [HR_DB] FROM DISK = N'E:\Backup\HR_DB.bak ......
Successful!
3. CMDEXC step run sqlcmd to on SQL 2005 box to remotely restore the database on SQL 2012 boxsqlcmd -E -d master -S WINSRV_SQL2012 -Q "ALTER DATABASE [HR_DB] SET READ_ONLY WITH NO_WAIT"
No Error in SQL JOB - job completes successfully
I don't see HR_DB on WINSRV_SQL2012 in read_only
When I run the sqlcmd from dos prompt on SQL 2005 box it does work successfully though... is this a SQL 2005 bug/sp issue with sql cmd in SQL Agent...
Unfortunately... it is a 12 year old un supported DB 🙁
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
May 5, 2017 at 4:03 am
replace " with no_wait" to "with rollback immediate" !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 5, 2017 at 6:19 am
Which, it's worth noting, will rollback open transactions. Not the end of the world or anything, but worth noting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2017 at 7:04 am
To explain...
The NO_WAIT option on the ALTER DATABASE means that if the exclusive DB lock cannot be obtained immediately, the command should abort, not wait to try and acquire the lock. Hence, if there was *any* other connection to that DB, the command would run, be unable to get the needed lock, and exit without changing the DB's state.
ROLLBACK IMMEDIATE says to rollback existing transactions, terminate existing connections and then set the DB to READ_ONLY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply