Creating databse backups automatically in specific period of time

Question

Creating databse backups automatically in specific period of time

jaryszek

Default port

Points: 1425
More actions
December 14, 2017 at 1:51 am

#330534

Hi Guys,
I am creating back ups of my database using code:
USE QlikView; GO BACKUP DATABASE QlikView TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SERVERTEST\MSSQL\Backup\QlikView.Bak' WITH FORMAT, MEDIANAME = 'C_SQLServerBackups', NAME = 'Full Backup of QlikView'; GO
It is ok but question is can i create these back ups only on the last day of every month?
I can do it in VBscript and set up task scheduler on server but maybe there is a simple way to create back up in MS SQL SERVER EXPRESS using T-SQL?
Jacek

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

jaryszek Default port Points: 1425 More actions · Answer 1

I think that i will have to use VBscript anyway because i have to run scripts outside from SQL Server express because i do not have Job Assistant here...

Jacek

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

You could use the Windows task scheduler instead. By the way, don't back up to the C drive. Make sure backups go to a different disk from the operating system and database files, and thereafter off the server all together as quickly as possible.

John

jaryszek Default port Points: 1425 More actions · Answer 3

thank you John!
Ok this is better idea to have backups on different drive then C drive.

Question about tash scheduler in Windows. I can set up the task here but some code should run MS SQL Server Query.
What can you recommendt?

Jacek

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

jaryszek - Thursday, December 14, 2017 2:29 AM
Question about tash scheduler in Windows. I can set up the task here but some code should run MS SQL Server Query.
What can you recommendt?

Jacek

Have a look at this. It's a command-line utility for running SQL queries. It'll be available on your server by default.

John

jaryszek Default port Points: 1425 More actions · Answer 5

jaryszek

Default port

Points: 1425

December 14, 2017 at 3:05 am

#1972095

Thank you John,

I will test it !
Jacek

goher2000 SSCertifiable Points: 5477 More actions · Answer 6

another way of doing backup for all databases, I use this for SQL express backup via sqlcmd/task schedular, BTW you can use SQL agent from standard/developer/enterprise edition to take backup of SQL express instance provided appropriate ports are open and SQL agent have access to express edition/server.

declare @cmd varchar(5000)
select @cmd ='if ''tempdb'' <> cast(''?'' as nvarchar) backup database [?] to disk =''x:\sql_backup\?\?_2017_06_15.bak'''
PRINT @cmd
exec sp_msforeachdb @cmd

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

My concern is only backing up the database once a month. If you don't mind losing almost one months data due to a failure just before your monthly backup, you really should backup the database more frequently.

jaryszek Default port Points: 1425 More actions · Answer 8

Hi Guys,

thank you!
I do not have SQL server Agent and standard SQL edition.

This code:
declare @cmd varchar(5000) select @cmd ='if ''tempdb'' <> cast(''?'' as nvarchar) backup database [?] to disk =''x:\sql_backup\?\?_2017_06_15.bak''' PRINT @cmd exec sp_msforeachdb @cmd

is for SQL Server Agent or cmd task ?

Best Wishes,
Jacek

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

That's T-SQL, so no, it won't run in command line.
You can run it via the util that John mentioned.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jaryszek Default port Points: 1425 More actions · Answer 10

Thank you Guys!

The best method and the simplest for me is creating VBscript and run it via tash scheduler.

Best wishes,
Jacek

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Simpler than calling SQLCMD and passing it T-SQL? Ok.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jaryszek Default port Points: 1425 More actions · Answer 12

Yes,

I can create query in MS SQL Server, save it and simple run via VBscript code.
So i am using T-SQL within Query Designer in MS SQL Server.

Best Wishes,
Jacek Antek