Scheduling an automatic backup

I have only started using SQL Server 2005 Express Edition recently.I also installed Management Studio Express version to manage the database. While testing the database I had came across some question and they are:

Can I upgrade from SQL Server 2005 Express to Workgroup/Standard/Enterprise without loosing the database and its contents. While I backed up the database, I was permitted to backup only on to my local drive even though I have administrative rights on the system. It would be easy if I could backup to a network drive directly.My question is:Is it possible to backup directly to a network drive rather than backing the database to a local drive and then "copy and paste" it to a network drive.
Windows Scheduler: I have scheduled a daily backup of the database by midnight. I have also written the script to run the backup. But every time the scheduler starts the backup, it asks for the database password. This halts the backup until I come back in the morning and manually enter the password. My question is: is it possible to run an automatic database backup using Windows Scheduler? I understand that there are 2 types of backups: full and differential backups. My question is: Is it possible to dynamically allocate different names automatically to consecutive backups so that the previous backups are not over written

Thanks for your patience and time.

regards,
Berly Sam

1. Yes

2. SQL Server only allows backups to local drives or LUNs. There are third party backup tools that will backup to a network drive/share. I usually have a job step that 'moves' the files to a network share after the backup is complete.

3.Yes. I assume that you are using SQLCmd.exe. Use on of the following command line arguements (switches): -U login_id [ -P password ] } OR –E trusted connection. For addtional information, see Books Online, Topic: "SQLCmd Utility'. If necessary, you can download a copy of Books Online here.

4. Since you would need two separate Jobs scheduled, one for FULL backup and one for DIFFERENTIAL, then it seems that you would not have any issue having two different names. And yes, you can dynamically create your file names.

|||Thank you Mr. Rowland for replying.
But in the question of Back ups: i need to create different differential backups which take different names dynamically. Is it possible to schedule differential backups without over writing the previous backup.

Thank you for your time and patience
Regards
Berly Sam
|||

They will not 'take' names.

You will have to dynamically, and in your script/code, create the names and provide them to the backup command.

|||I was working over the backup case but was unable to come up with a solution as to how i can automatically backup the database while dynamically allocating names to consecutive differential backups. Just a reminder, the database I am using is SQL Server 2005 Express edition.

The script that is used to run the backup is
BACKUP DATABASE [DatabaseName] TO DISK = N'C:\BackUp\ST_BKUP_14_06_07'WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'DatabaseName-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

I have also run the SQLCMD utility.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S [SERVER] -U backupadmin -P [PASSWORD] -i B:\ST_BACKUP\sqlscript.sql

What else must i do so that i get an automatic backup without being asked to login for every scheduled backup.Since the 'login' makes it compulsory for me to be on the system while the backup takes place. Is there any alteration i must make so that different names are alloted to consecutive backups thus not overwriting an old backup.|||

Before the part of the script that executes the BACKUP command, create a variable with the name you wish to use.

Code Snippet

DECLARE @.MyBackupName nvarchar(250)

SET @.MyBackupName = 'C:\BackUp\ST_BKUP_' + convert( varchar(10), getdate(), 112 ) + '.BAK'

BACKUP DATABASE [DatabaseName] TO DISK = @.MyBackupName
WITH DIFFERENTIAL ,
NOFORMAT,
NOINIT,
NAME = N'DatabaseName-Differential Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10

Every time this runs, the file name will be in the form: ST_BKUP_20070614.BAK, with the date changing every day.

|||

Very cool.

So, for addition information I have a question:

1-Can I create a Stored Procedure with this code and then run it from a command line (cmd) ?

2-Can I to use the osql.exe to run this command or run the Stored Procedure created with this code ?

Tks,

Mura

|||

1. By using OSQL or SQLCmd.

2. Yes, OSQL.exe with SQL 2000, SQLCmd.exe with SQL 2005.

You can use the Windows Scheduler service to automate this.

|||Thank you Mr. Rowland for replying.
Your posts have helped me solve my problem of dynamic name allocation to my backups.
Although I could solve that, the scheduled backup does not execute at the specified time and will be halted till I manually enter the login information. My question is how can i remove the login option that i have to fill-in every time a scheduled backup script is run.

This would complete my question of how an automatic backup is done in SQL Server.
Thank you for your time and patience

Regards
Berly Sam
|||

This command line prompts the user for a Server and password.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S [SERVER] -U backupadmin -P [PASSWORD] -i B:\ST_BACKUP\sqlscript.sql

I suggest that you put in the ServerName in place of [SERVER], and that you use -E instead of: -U backupadmin -P [PASSWORD]. So my suggested command line would be:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -S MyServerName -E -i B:\ST_BACKUP\sqlscript.sql

|||Tkx guy,|||Thank you Mr. Rowland,
I could solve my problem. I really appreciate the time and energy you invested to help me with my issue.Once again thank you
Regards
Berly Sam
|||When I executed this code, it works fine, but the thing is when I tried to change the style in the convert function its throwing an error. The dynamic file which which is generated for each backup should have the date and also the time for me . Can anyone help me on this.
|||

You have probably selected a time format that contains characters unacceptable in a path/filename.

You may prefer to do something more like this:

Code Snippet

DECLARE @.MyBackupName nvarchar(250)

SET @.MyBackupName = 'C:\BackUp\ST_BKUP_'
SET @.MyBackupName = @.MyBackupName + convert( varchar(10), getdate(), 112 ) + '_'
SET @.MyBackupName = @.MyBackupName + replace( convert( varchar(5), getdate(), 108 ), ':', '' )
SET @.MyBackupName = @.MyBackupName + '.BAK'

SELECT @.MyBackupName

--
C:\BackUp\ST_BKUP_20070618_2124.BAK