Convert error - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Convert error

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-10-15 : 11:12:49

DECLARE @DatabaseName varchar(100)
SELECT @DatabaseName = 'Northwind'+ getdate()
print @DatabaseName

I got below error

Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 11:15:01

DECLARE @DatabaseName varchar(100)
SELECT @DatabaseName = 'Northwind'+ convert(varchar(11),getdate())
print @DatabaseName

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-10-15 : 11:41:56

Thanks a lot!!
I need the results Below



'C:\MSSQL\BACKUP\Northwind\Northwind.full.20081015.1.bak'

DECLARE @DatabaseName varchar(100)
'Northwind'+ convert(varchar(11),getdate())

BACKUP DATABASE Northwind TO DISK = 'C:\MSSQL\BACKUP\Northwind\Northwind.full.2008101511:39:14.410.1.bak'

Nexttime when i took the backup it should create the backup with
Northwind.full.2008101511:40:00.000.2.bak

Northwind.full.2008101512:40:00.000.3.bak

THanks for your help !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 12:59:13

[code]DECLARE @DatabaseName varchar(100),@Sql varchar(1000)
SET @DatabaseName ='C:\MSSQL\BACKUP\Northwind\Northwind.full.'+ convert(varchar(11),getdate()) + '.bak'

SET @Sql='BACKUP DATABASE Northwind TO DISK = '''+@DatabaseName +''''

EXEC(@Sql)[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-15 : 13:37:21

why don't you use maintenance plan for backup? It will give you timestamp as well after bak files.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-10-15 : 16:51:33

Thanks a lot visakh !

Below i want updated the 126 in the convert function..
DECLARE @DatabaseName varchar(100),@Sql varchar(1000)
SET @DatabaseName ='C:\MSSQL\BACKUP\Northwind\Northwind.full.'+ convert(varchar(11),getdate(),126) + '.bak'

SET @Sql='BACKUP DATABASE Northwind TO DISK = '''+@DatabaseName +''''

EXEC(@Sql)

Now I want to the restore DB as Northwind_Test after the backup was done.
I want this process to be automated as a job.whenever a backup was done then the same backup needs to be restored as Northwind_Test..

DECLARE @DatabaseName varchar(150),@Sql varchar(1000)
SET @DatabaseName ='C:\MSSQL\BACKUP\Northwind\Northwind.full.'+ convert(varchar(11),getdate(),126)
SET @Sql='RESTORE DATABASE Northwind_Test FROM DISK ='''+@DatabaseName +''''

EXEC(@Sql)

Please correct me the restore process code..
Thanks for your help in advance !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources