mstumpo
asked on
Error running an SQL express backup script
Running SQL express we need to run an automatic backup. I created the script, but get this error when we run it.
Script
SET J=%date:~-10,2%
SET A=%date:~-4%
SET M=%date:~-7,2%
SET H=%time:~0,2%
SET MN=%time:~3,2%
SET S=%time:~-5,2%
IF "%time:~0,1%"==" " SET H=0%HOURS:~1,1%
SET DIRECTORY="D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backu p"
set FILE=%DIRECTORY%\sqlbackup _%J%_%M%_% A%_A_%H%_% MN%_%S%.ba k
cd d:\Program Files\Microsoft SQL Server\90\Tools\Binn
sqlcmd -S Server_name\SQLEXPRESS -E -Q "BACKUP DATABASE [JA2010] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backu p\JA2010.b ak' WITH NOFORMAT, INIT, NAME = N'JA2010-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
Error:
System.Data.DataException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.Data.SqlClient.SqlE xception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
at System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj)
at System.Data.SqlClient.TdsP arser.Conn ect(Server Info serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlI nternalCon nectionTds .AttemptOn eLogin(Ser verInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlI nternalCon nectionTds .LoginNoFa ilover(Str ing host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlI nternalCon nectionTds .OpenLogin Enlist(Sql Connection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlI nternalCon nectionTds ..ctor(DbC onnectionP oolIdentit y identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlC onnectionF actory.Cre ateConnect ion(DbConn ectionOpti ons options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.D bConnectio nFactory.C reatePoole dConnectio n(DbConnec tion owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.D bConnectio nPool.Crea teObject(D bConnectio n owningObject)
at System.Data.ProviderBase.D bConnectio nPool.User CreateRequ est(DbConn ection owningObject)
at System.Data.ProviderBase.D bConnectio nPool.GetC onnection( DbConnecti on owningObject)
at System.Data.ProviderBase.D bConnectio nFactory.G etConnecti on(DbConne ction owningConnection)
at System.Data.ProviderBase.D bConnectio nClosed.Op enConnecti on(DbConne ction outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlC onnection. Open()
at SQLScheduler.Data.DataWork er.OpenCon nection()
at SQLScheduler.Data.DataWork er.RunSQL( String p_strSQL, Int32 commandTimeout, DataSet& dataResults)
--- End of inner exception stack trace ---
Server stack trace:
at SQLScheduler.Data.DataWork er.RunSQL( String p_strSQL, Int32 commandTimeout, DataSet& dataResults)
at SQLScheduler.Data.DataWork er.RunSQL( String p_strSQL)
at SQLScheduler.Data.DataWork er.CheckSQ L(String script)
at SQLScheduler.Services.JobS ervice.Che ckSQLSynta x(String serverName, String jobName, String script)
at System.Runtime.Remoting.Me ssaging.St ackBuilder Sink._Priv ateProcess Message(In tPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Me ssaging.St ackBuilder Sink.Priva teProcessM essage(Run timeMethod Handle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Me ssaging.St ackBuilder Sink.SyncP rocessMess age(IMessa ge msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Pr oxies.Real Proxy.Hand leReturnMe ssage(IMes sage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Pr oxies.Real Proxy.Priv ateInvoke( MessageDat a& msgData, Int32 type)
at SQLScheduler.Services.JobS ervice.Che ckSQLSynta x(String serverName, String jobName, String script)
at SQLScheduler.Client.JobFor m._btnChec kSyntax_Cl ick(Object sender, EventArgs e)
Script
SET J=%date:~-10,2%
SET A=%date:~-4%
SET M=%date:~-7,2%
SET H=%time:~0,2%
SET MN=%time:~3,2%
SET S=%time:~-5,2%
IF "%time:~0,1%"==" " SET H=0%HOURS:~1,1%
SET DIRECTORY="D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backu
set FILE=%DIRECTORY%\sqlbackup
cd d:\Program Files\Microsoft SQL Server\90\Tools\Binn
sqlcmd -S Server_name\SQLEXPRESS -E -Q "BACKUP DATABASE [JA2010] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
Error:
System.Data.DataException:
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlC
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.SqlClient.SqlC
at SQLScheduler.Data.DataWork
at SQLScheduler.Data.DataWork
--- End of inner exception stack trace ---
Server stack trace:
at SQLScheduler.Data.DataWork
at SQLScheduler.Data.DataWork
at SQLScheduler.Data.DataWork
at SQLScheduler.Services.JobS
at System.Runtime.Remoting.Me
at System.Runtime.Remoting.Me
at System.Runtime.Remoting.Me
Exception rethrown at [0]:
at System.Runtime.Remoting.Pr
at System.Runtime.Remoting.Pr
at SQLScheduler.Services.JobS
at SQLScheduler.Client.JobFor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok so i am a moron, the server name was not changed to 2003exchange. My bad please close this question, thanks for pointing me in the right direction
ASKER