Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Errors in automated restore job, but this worked in 2005 Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 2:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 3:42 PM
Points: 23, Visits: 36
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.



Post #1340887
Posted Monday, August 6, 2012 2:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 20,858, Visits: 32,878
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?




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)
Post #1340899
Posted Monday, August 6, 2012 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 3:42 PM
Points: 23, Visits: 36
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.



Post #1340920
Posted Tuesday, August 7, 2012 2:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:01 PM
Points: 18, Visits: 446
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).


Post #1341079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse