Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Errors in automated restore job, but this worked in 2005


Errors in automated restore job, but this worked in 2005

Author
Message
jweyl
jweyl
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 37
Hi -

I have some very simple code that kills all the user pids, then switches the db to single use and runs a restore and sync of the logins. We do this on a nightly basis. Last month our environment got upgraded to SQL server 2008 and now the code won't work. I am not sure why? But I am getting the following error out of SQL Server "Incorrect syntax near '*'. [SQLSTATE 42000] (Error 102)" I haven't been able to find anything to help me out.

Here is my simple code:
use master
go
DECLARE @KillSpid int , @CMD nvarchar (20) DECLARE cur_kill CURSOR FOR SELECT SP.spid, SD.name
FROM SYSPROCESSES SP JOIN SYSDATABASES SD ON SP.dbid = SD.dbid WHERE SD.name = 'MyDBName'
OPEN cur_kill FETCH NEXT FROM cur_kill INTO @KillSpid ,@CMD WHILE @@FETCH_STATUS = 0 --------------------------
BEGIN SET @CMD = 'KILL ' + CAST ( @KillSpid as Varchar(3)) EXECUTE sp_executesql @CMD
PRINT CAST ( @KillSpid as Varchar(3)) + ' SPID KILLED ' FETCH NEXT FROM cur_kill INTO @KillSpid , @CMD
END CLOSE cur_kill DEALLOCATE cur_kill
go

ALTER DATABASE [MyDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE


RESTORE DATABASE [MyDBName] FROM DISK = N'\\MyServer\LogShipping\MyDBName_backup.bak' WITH FILE = 1,
MOVE N'MyDBName_Data' TO N'E:\Data\MyDBName_Data.MDF',
MOVE N'MyDBName_Log' TO N'E:\Data\MyDBName_Log.LDF',
NOUNLOAD, REPLACE, STATS = 10
GO

Then the DB runs the sp 'exec sp_change_users_login' to sync users on this new server.

Does anyone have any idea why this isn't working? Over the weekend I did a trace on thee jobs and I am reading through the results right now, but that might take a little bit more digging.



Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
jweyl (8/6/2012)
Hi -

I have some very simple code that kills all the user pids, then switches the db to single use and runs a restore and sync of the logins. We do this on a nightly basis. Last month our environment got upgraded to SQL server 2008 and now the code won't work. I am not sure why? But I am getting the following error out of SQL Server "Incorrect syntax near '*'. [SQLSTATE 42000] (Error 102)" I haven't been able to find anything to help me out.

Here is my simple code:
use master
go
DECLARE @KillSpid int , @CMD nvarchar (20) DECLARE cur_kill CURSOR FOR SELECT SP.spid, SD.name
FROM SYSPROCESSES SP JOIN SYSDATABASES SD ON SP.dbid = SD.dbid WHERE SD.name = 'MyDBName'
OPEN cur_kill FETCH NEXT FROM cur_kill INTO @KillSpid ,@CMD WHILE @@FETCH_STATUS = 0 --------------------------
BEGIN SET @CMD = 'KILL ' + CAST ( @KillSpid as Varchar(3)) EXECUTE sp_executesql @CMD
PRINT CAST ( @KillSpid as Varchar(3)) + ' SPID KILLED ' FETCH NEXT FROM cur_kill INTO @KillSpid , @CMD
END CLOSE cur_kill DEALLOCATE cur_kill
go

ALTER DATABASE [MyDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE


RESTORE DATABASE [MyDBName] FROM DISK = N'\\MyServer\LogShipping\MyDBName_backup.bak' WITH FILE = 1,
MOVE N'MyDBName_Data' TO N'E:\Data\MyDBName_Data.MDF',
MOVE N'MyDBName_Log' TO N'E:\Data\MyDBName_Log.LDF',
NOUNLOAD, REPLACE, STATS = 10
GO

Then the DB runs the sp 'exec sp_change_users_login' to sync users on this new server.

Does anyone have any idea why this isn't working? Over the weekend I did a trace on thee jobs and I am reading through the results right now, but that might take a little bit more digging.


I can't find an * (asterick) in your code. Also, why are you manually killing spids when the ALTER DATABASE [MyDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE will terminate all connections to the database allowing your subsequent restore run?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jweyl
jweyl
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 37
the alter db was an extra step I put in after the switch over to SQL Server 2008 when reading about getting exclusive access. There were a few applications that were hanging on after the pids were manually killed.

There isn't an asterisk in the code, which is why this issue is so confusing. I can't figure out why this is happening.



db_expert_pradeep
db_expert_pradeep
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 523
Your code is running fine i have checked in SQL 2008 as below

DECLARE @KillSpid int , @CMD nvarchar (20)
DECLARE cur_kill CURSOR FOR SELECT SP.spid, SD.name
FROM SYSPROCESSES SP JOIN SYSDATABASES SD ON SP.dbid = SD.dbid WHERE SD.name = 'MyDB'
OPEN cur_kill
FETCH NEXT FROM cur_kill INTO @KillSpid ,@CMD
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = 'KILL ' + CAST ( @KillSpid as Varchar(3))
EXECUTE sp_executesql @CMD
PRINT CAST ( @KillSpid as Varchar(3)) + ' SPID KILLED '
FETCH NEXT FROM cur_kill INTO @KillSpid , @CMD
END
CLOSE cur_kill DEALLOCATE cur_kill
go
--Sp_who2 'Active'
ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDB] FROM DISK = N'D:\All_File_Fullbackup.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

--RESTORE DATABASE [MyDBName] FROM DISK = N'\\MyServer\LogShipping\MyDBName_backup.bak' WITH FILE = 1,
--MOVE N'MyDBName_Data' TO N'E:\Data\MyDBName_Data.MDF',
--MOVE N'MyDBName_Log' TO N'E:\Data\MyDBName_Log.LDF',
--NOUNLOAD, REPLACE, STATS = 10
--GO



56 SPID KILLED
57 SPID KILLED
63 percent processed.
100 percent processed.
Processed 168 pages for database 'MyDB', file 'MyDB_Primary' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG1_Dat1' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG1_Dat2' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG2_Dat3' on file 1.
Processed 8 pages for database 'MyDB', file 'MyDB_FG2_Dat4' on file 1.
Processed 1 pages for database 'MyDB', file 'MyDB_log' on file 1.
RESTORE DATABASE successfully processed 201 pages in 0.104 seconds (15.099 MB/sec).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search