March 7, 2012 at 4:44 am
Hi guys,
I am looking to run a 4 line script in sql server 2008 r2 express.
I dont have sql agent installed for this instance.
Has anyone any sample script that would allow me to run this small script every evening for example.
Any help would be great, thanks.
March 7, 2012 at 4:50 am
Use the task scheduler in Windows to run the command through sqlcmd.
John
March 7, 2012 at 4:59 am
Thanks John, just wondering if you can explain in more depth by any chance please?
March 7, 2012 at 5:07 am
I could give you a step-by-step guide, but you should be able to use a search engine to find something like that yourself. Is there a particular bit you don't understand?
John
March 7, 2012 at 5:15 am
Will I need to create a stored procedure for this script to run for a specific table as there is multiple tables under the instance I have created?
Or can I amend the script to point at a specific DB?
Thanks again.
March 7, 2012 at 5:23 am
A stored procedure would probably be a good idea. You'll need to use dynamic SQL if it's going to run against different tables, so make sure you understand the implications of that. I'm not sure how you'd change it at the task scheduler end to pass different parameters to the proc - that will depend on your exact requirements and may involve scripting of some kind (batch files, VB, etc).
Another option is to create an SSIS package on a server that has the full product installed. You schedule it to run from there while pointing it at the SQL Server Express server.
John
March 7, 2012 at 8:14 am
Aidan,
John has given good advice. Reading the thread, it strikes me that this might be a new role or task for you. Can you tell us what you need to achive and if the SP you speak of is to carryout the task or automate a batch of tasks.
Thanks,
A
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 8, 2012 at 5:26 am
Yes he gave great advice in fairness.
What I want to do so is run a script firstly at lets say 23.00 everyday to ensure that certain data is kept up to date in SQL.
So I will need a stored procedure along with a batch file I believe.
As you said this is a new task for myself and I am quiet unsure as to what I need, etc.
In addition what I would also like to do is to be able to have a backup run at a certain time of a particular DB in a sql instance, which would be great.
However, primarily I need to run this script.
March 8, 2012 at 5:57 am
Do you know how to build the SP? if not, upload the code.
As far as scheduling is concerned, you can use windows scheduler and sqlcmd.
If your not sure, upload the code, requirements and server name and we can sort you out.
As for the backup, take a look at the below; it backs up to a variable set path and appends the date and time to the file. All backups are verifyed. I can convert to SP if you like. Also capable of backing up remote databases over LS (if needed).
SET NOCOUNT ON
DECLARE @databases_table TABLE(server VARCHAR(50),dbname VARCHAR(50))
INSERT INTO @databases_table(server,dbname) VALUES('server1','database_on_server1')
INSERT INTO @databases_table(server,dbname) VALUES('server2','database_on_server2')
DECLARE @current_date VARCHAR(15)
DECLARE @current_database VARCHAR(50)
DECLARE @backup_path VARCHAR(300)
DECLARE @backup_string VARCHAR(1000)
DECLARE @backup_file VARCHAR(60)
DECLARE @server VARCHAR(50)
DECLARE @sql_server VARCHAR(50)
DECLARE @exec VARCHAR(20)
SET @backup_path = 's$\Microsoft SQL Server\MSSQL.1\MSSQL\DO NOT DELETE\'
SET @current_date = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(), 120),'-',''),':',''),' ','_')
DECLARE dbname_cursor CURSOR FOR
SELECT server, dbname FROM @databases_table
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @server, @current_database
WHILE @@FETCH_STATUS = 0
BEGIN
IF @server = 'server1'
BEGIN
SET @sql_server = 'server1amed_instance1'
END
IF @server = 'server2'
BEGIN
SET @sql_server = 'server2amed_instance2'
END
SET @backup_file = @current_database + '_backup_' + @current_date + '.bak'
SET @backup_string = 'BACKUP DATABASE [' + @current_database + ']
TO DISK = N''\\' + @server + '\' + @backup_path + @backup_file + ''' WITH NOFORMAT, NOINIT, NAME = N''' + @current_database + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''' + @current_database + ''' and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N''' + @current_database + ''' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''' + @current_database + ''''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''' + @backup_path + @backup_file + ''' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO'
SET @exec = 'EXEC(@backup_string) ' -- + 'AT ' + @server -- to run remotely
FETCH NEXT FROM dbname_cursor INTO @server, @current_database
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 8, 2012 at 7:08 am
Can you specify what you mean by SP please?
Ideally I would not like to identify the script exactly but I can give you a sample of the script with some names replaced as this is for a customer.
I can amend the code you would provide by replacing instance name, etc with what it should be.
Instance name: is office2\sqlexpress
Code: select * from test
Server name: TestServer
What I require is for this script to be run for this instance on the above server to run at a specific time each night of the week.
I would greatly appreciate if you could comment some of the code so I would have an increased understanding of how it works.
Thanks again for your assisstance
March 8, 2012 at 7:37 am
What I meant by 'SP' is 'stored procedure'. I asked you if you could build one.
From the very limited info you have given the SP creation code would be:
USE master
GO
CREATE PROCEDURE dbo.usp_example_stored_proc
AS
BEGIN
SELECT * FROM test.sys.tables WHERE schema_id = 1
END
The command for your batch file is:
sqlcmd -S YOURSERVER\YOURINSTANCE -Q "exec master.dbo.usp_example_stored_proc"
Assuming 'sqlcmd' is accessible straight from 'cmd' (i.e. in your PATH)
The scheduled task steps would be:
start --> Setting --> Control Panel
Scheduled Tasks --> Add Scheduled Task
Next --> Browse --> Next
Set schedule name and type --> Next
Set time and recurrance --> Next -->
Give login\exec datails --> Next --> Finish
You will need relevant permissions for all of the above and authorisation to make the changes. TBH I am starting to get concerned that you should be doing these tasks at all.
SP's and Windows scheduler is prettty basic stuff and if you dont know how to start, you should be concerned about how you will support.
I'm not going to comment the backup script for you just yet, lets see what you make of the above.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 8, 2012 at 8:22 am
I understand your concern especially given the fact I didnt really know how to create a SP but again we all have to start somewhere, this one in particular I understand would be SP in its most basic of forms.
However I tested this on our testing site here with the code I will be using, all it is would be updating a few records and it work perfectly so thank you kindly for your help there.
As regards the DB backup, I have software which can backup a SQL DB if it is configured correctly and works fine, what I was asking for in that one was just assessing if there was an alternative really.
If you can assist with the DB backup I would appreciate, I test everything and discuss will colleagues prior to deploying to re assure you.
Thanks very much for your help again.
March 8, 2012 at 8:49 am
Ok the commented and slightly modded backup script is below:
SET NOCOUNT ON
-- declaration of temp table
DECLARE @databases_table TABLE(server VARCHAR(50),dbname VARCHAR(50))
-- populate a temp table with db's to be backed up
INSERT INTO @databases_table(server,dbname) VALUES('server1','database_on_server1')
INSERT INTO @databases_table(server,dbname) VALUES('server2','database_on_server2')
-- declarations of required variables
DECLARE @current_date VARCHAR(15)
DECLARE @current_database VARCHAR(50)
DECLARE @backup_path VARCHAR(300)
DECLARE @backup_string VARCHAR(1000)
DECLARE @backup_file VARCHAR(60)
DECLARE @server VARCHAR(50)
DECLARE @sql_server VARCHAR(50)
DECLARE @exec VARCHAR(20)
-- set backup path (partial-unc)
SET @backup_path = 's$\Microsoft SQL Server\MSSQL.1\MSSQL\DO NOT DELETE\'
-- set variable with current date and time (YYYMMDD_HHMM)
SET @current_date = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(), 120),'-',''),':',''),' ','_')
-- declare cursor to loop round temp table and backup each instance row)
DECLARE dbname_cursor CURSOR FOR
-- select statement to control cursor
SELECT server, dbname FROM @databases_table
OPEN dbname_cursor
-- fetch statement for cursor to return the first row
FETCH NEXT FROM dbname_cursor INTO @server, @current_database
WHILE @@FETCH_STATUS = 0
BEGIN
-- if, used to set connection string for servers with named instances (for remote execution over ls's)
IF @server = 'server1'
BEGIN
SET @sql_server = 'server1amed\instance1'
END
-- if, used to set connection string for servers with named instances (for remote execution over ls's)
IF @server = 'server2'
BEGIN
SET @sql_server = 'server2amed\instance2'
END
-- set variable to required name of backup file ({DATABASE}_backup_{DATE_TIME}.bak)
SET @backup_file = @current_database + '_backup_' + @current_date + '.bak'
-- set backup string to be executed (this includes the database backup and verification of the file created
SET @backup_string = 'BACKUP DATABASE [' + @current_database + ']
TO DISK = N''\\' + @server + '\' + @backup_path + @backup_file + ''' WITH NOFORMAT, NOINIT, NAME = N''' + @current_database + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''' + @current_database + ''' and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N''' + @current_database + ''' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''' + @current_database + ''''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''' + @backup_path + @backup_file + ''' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO'
-- used for remote execution. uncomment the second part to enable remote execution. @server must = your ls
SET @exec = 'EXEC(@backup_string) ' -- + 'AT ' + @server -- to run remotely
-- execute code to backup the current database
EXEC @exec
-- fetch the next record from the cursor (temp table)
FETCH NEXT FROM dbname_cursor INTO @server, @current_database
END
-- close cursor and deallocate memory space
CLOSE dbname_cursor
DEALLOCATE dbname_cursor
To turn it in toa SP put:
USE {hosting db}
GO
CREATE PROCEDURE dbo.{procedure_name}
AS
BEGIN
At the top.
And:
END
At the end.
Then to execute you can use similar code to that in my earlier post (for your batch file).
Keep in mind you should also be integrity checking your databases each day and maintaning a retension policy for the backed up files (you cant just keep backing up).
Now were you paying cash or cheque 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 8, 2012 at 9:10 am
Thank you very much, yes we will be doing a great deal of monitoring for this along with a few days of tests before we consider going live with this operation.
Seriously many thanks for your help, I will pay with a cheque that will bounce no doubt 😀
March 8, 2012 at 9:22 am
in that case make it out to:
Mickey Mouse
@
The Fair'n'Appriciative Co LTD.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply