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 Administration (2000)
 VB and backing up db

Author  Topic 

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 11:53:48
Hi, I currently and using the command

oConn.Execute "backup database test to disk='c:\test.bak'"
from vb to backup the db.

Is this a safe method which stores everything I need to recover my
db?

Also, if the computer is formated and I want to restore this backup,
would there be any trouble doing this.

I know if I detach the MDF and LOG file, I could just reatch them.

Can I do a restore with just the .bak file?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 12:46:34
Yes the BACKUP command is a safe method and it does grab everything in the user database. Note I said user database. Logins aren't stored in there, neither are DTS packages, or jobs and lots of other stuff. You need to also back up the system database for complete recovery. I would also suggest not doing this in VB. Schedule your command inside SQL Server. There is no reason to bring an application into this when SQL Server has it built in.

Yes you can do a restore with just the .BAK file, but not that you won't get the stuff that is in the system tables, so back those up too!

Tara
Go to Top of Page

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 16:01:22
Thank you for your response.

I am using MSDE and there I don't have enterprise manager.
Is it still possible to do the things you suggest?

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 16:02:47
You will need to use osql.exe which is a command line utility. There are free 3rd party tools out there though if you want to use a GUI tool.

Tara
Go to Top of Page

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 17:01:25
Once again, thanks.

If you can, can you point me to a site where I can get the info
I need on this utility.

I am kind of under a deadline here doing many things at once so I don't have much time to look for the correct info.

Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 17:31:46
In MS Access 2000 there is a SQL SERVER MDA file you can create. From there you will see all you stored procs and stuff. Its kind of nice if you have no front end. Just do existing project and point the ODBC to your MSDE SQL Server install.

That or you could always get an MSDN universal and do you dev in enterprise manager from SQL Server 2k developer edition.

Daniel
SQL Server DBA
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 17:58:19
SQL Server Books Online is where you would find the information about osql.

Here's a quick look. Go to Start/Run. Type in cmd. Hit enter.

At the command line:

osql -SServerName -E

You should then see >1

You can now type in queries.

>1 USE DBName
>2 GO
>1 SELECT * FROM Table1
>2 GO
>1 BACKUP DATABASE DBName TO DISK = 'C:\test.BAK' WITH INIT
>2 GO
>1 exit
exit

Tara
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 18:15:15
quote:
Originally posted by tduggan

SQL Server Books Online is where you would find the information about osql.

Here's a quick look. Go to Start/Run. Type in cmd. Hit enter.

At the command line:

osql -SServerName -E

You should then see >1

You can now type in queries.

>1 USE DBName
>2 GO
>1 SELECT * FROM Table1
>2 GO
>1 BACKUP DATABASE DBName TO DISK = 'C:\test.BAK' WITH INIT
>2 GO
>1 exit
exit

Tara



Yup you can even launch query batches that are stored in a text file from the osql commandline. So you could automate the backup by just using the windows scheduler to run a .bat file that opens osql and runs the backup batch.

Daniel
SQL Server DBA
Go to Top of Page

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 19:07:22
tduggan,

would using the command line options you wrote do a full backup of
all the data I need, or just my database?

Why would I need to back anything else up?







Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 19:11:16
It would be just for the user database. You need to also backup the system databases. The system databases contain logins, DTS packages, jobs and lots of other stuff. If you are using MSDE, then you probably only need to worry about logins right now, which is in the master database.


Tara
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2006-04-25 : 17:47:49
I know I am digging up an oldie here... 4 years to be exact... but there is one thing I have learned when new to a forum, SEARCH FIRST! Then ask.

That's how I found this golden oldie.

My client has a desire to be able to make backups and restore them under different names so they can run tests before going live with runs - different scenarios and things of that nature. This will save my fellow staff members a lot of time; cause then we won't have to do their backups and restores.

For example, lets say we have a db called "CompanySalesMaster" and they want to test different commission scenarios against it.

"CompanySalesMaster" is backed-up to companymastersales.bak.

I want to be able to restore this file as BAK_<date>_CompanySalesMaster and not be constrained to the existing name.

I know this is doable via Enterprise Manager, hense, it must be doable via the command line... and the last thing I wanna do is give the client EM!

I have tried using the RESTORE command with a new filename, but all that does is throw an error.

BTW, I am using VB6/SP6, and SQLServer 2000.

Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-25 : 17:50:14
Please start a new thread and post the command that is throwing the error, also include the error. But here's a sample:

RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\SomeBackupFile.BAK'
WITH REPLACE, MOVE 'SomeDatabase_Data' TO 'F:\MSSQL\DATA\SomeDatabase_Data.MDF',
MOVE 'SomeDatabase_Log' TO 'G:\MSSQL\DATA\SomeDatabase_Log.LDF'

Tara Kizer
aka tduggan
Go to Top of Page

bogus
Starting Member

41 Posts

Posted - 2006-04-25 : 18:02:17
ok. will do.
Go to Top of Page
   

- Advertisement -