Sql Server Programming :: Creating a backup script


	Creating a backup script Hi all, I have a SQLexpress database out at a location. Since there is no SQLAgent or option to create a maint plan, I need to create a script to backup my Databases. I created a backup thru my SQL 2005 and scripted it out for all the paramaters. I noticed that there is no longer remove old files after X days any longer. and I can't see how to get the date into the file name either. From my old 2000 maint plan days the files were created DBName_DB_YYYYMMDD.BAK Can anyone tell me where in the code below I can have the names change and add remove after 2 days? Does retain start over write after the 2 days? BACKUP DATABASE [DB1] TO [BD1Full] WITH RETAINDAYS = 2, NOFORMAT, INIT, NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 TIA, Joe You're in for a lot of coding/scripting, so save yourself some time and use what Jasper so nicely has provided for us: http://www.sqldbatips.com/showarticle.asp?ID=27 http://www.sqldbatips.com/showarticle.asp?ID=29 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "jaylou" <jay@discussions.microsoft.com> wrote in message news:D4D8D498-597F-40E9-8DA1-89342FACEC73@microsoft.com... - Hide quoted text - > Hi all, > I have a SQLexpress database out at a location. Since there is no SQLAgent > or option to create a maint plan, I need to create a script to backup my > Databases. I created a backup thru my SQL 2005 and scripted it out for all > the paramaters. I noticed that there is no longer remove old files after X > days any longer. and I can't see how to get the date into the file name > either. From my old 2000 maint plan days the files were created > DBName_DB_YYYYMMDD.BAK > Can anyone tell me where in the code below I can have the names change and > add remove after 2 days? Does retain start over write after the 2 days? > BACKUP DATABASE [DB1] TO [BD1Full] WITH RETAINDAYS = 2, NOFORMAT, INIT, > NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 > TIA, > Joe Thank you I will let you know how it works! - Hide quoted text - "Tibor Karaszi" wrote: > You're in for a lot of coding/scripting, so save yourself some time and use what Jasper so nicely > has provided for us: > http://www.sqldbatips.com/showarticle.asp?ID=27 > http://www.sqldbatips.com/showarticle.asp?ID=29 > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > "jaylou" <jay@discussions.microsoft.com> wrote in message > news:D4D8D498-597F-40E9-8DA1-89342FACEC73@microsoft.com... > > Hi all, > > I have a SQLexpress database out at a location. Since there is no SQLAgent > > or option to create a maint plan, I need to create a script to backup my > > Databases. I created a backup thru my SQL 2005 and scripted it out for all > > the paramaters. I noticed that there is no longer remove old files after X > > days any longer. and I can't see how to get the date into the file name > > either. From my old 2000 maint plan days the files were created > > DBName_DB_YYYYMMDD.BAK > > Can anyone tell me where in the code below I can have the names change and > > add remove after 2 days? Does retain start over write after the 2 days? > > BACKUP DATABASE [DB1] TO [BD1Full] WITH RETAINDAYS = 2, NOFORMAT, INIT, > > NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 > > TIA, > > Joe Thank you for this info it was great!!! - Hide quoted text - "Tibor Karaszi" wrote: > You're in for a lot of coding/scripting, so save yourself some time and use what Jasper so nicely > has provided for us: > http://www.sqldbatips.com/showarticle.asp?ID=27 > http://www.sqldbatips.com/showarticle.asp?ID=29 > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > "jaylou" <jay@discussions.microsoft.com> wrote in message > news:D4D8D498-597F-40E9-8DA1-89342FACEC73@microsoft.com... > > Hi all, > > I have a SQLexpress database out at a location. Since there is no SQLAgent > > or option to create a maint plan, I need to create a script to backup my > > Databases. I created a backup thru my SQL 2005 and scripted it out for all > > the paramaters. I noticed that there is no longer remove old files after X > > days any longer. and I can't see how to get the date into the file name > > either. From my old 2000 maint plan days the files were created > > DBName_DB_YYYYMMDD.BAK > > Can anyone tell me where in the code below I can have the names change and > > add remove after 2 days? Does retain start over write after the 2 days? > > BACKUP DATABASE [DB1] TO [BD1Full] WITH RETAINDAYS = 2, NOFORMAT, INIT, > > NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 > > TIA, > > Joe