Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NeilMc
Starting Member
2 Posts |
 Posted - 2010-10-19 : 04:47:04
|
| Hi I am trying to use the SQLCMD utility from within a windows batch script to update the bacup mode of a database into BULK_LOGGED whilst I do a large data load and then revert back to FULL afterwards.The database name is fed into the TSQL command as a variable in order for the script to be generic and usable on a number of other servers.However, the command ALTER DATABASE %DBNAME% SET RECOVERY BULK_LOGGEDfails when executed within the sqlCmd utility with a message that states RECOVERY is invalid for the SET command.From this I'm assuming that the SET statement within the TSQL for the alter database command is being seen as a separate statement or is possibly a reserved word within SQLCMD.I've tried serveral ways of enclosing the statement within quotes but it always fails. I've also spent many hours looking for the answer on the Net. I'm now desparate to get this working so would be very grateful for any help or suggestions.RegardsNeil |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
NeilMc
Starting Member
2 Posts |
Posted - 2010-10-19 : 07:01:18
|
| I've finally solved this.The database name needs to enclosed within square brackets [] ....EXECUTE dbo.sp_ExecuteSQL N'ALTER DATABASE [%DBNAME%] SET RECOVERY BULK_LOGGED'Obvious when you know how :o) |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 07:08:51
|
Ok thx for coming back and posting your solution  So it was already dynamic sql but that was not shown in your first post...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
ALTER DATABASE command fails in SQLCMD