SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 VB and backing up db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLError
Yak Posting Veteran

63 Posts

Posted - 05/12/2004 :  11:53:48  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 05/12/2004 :  12:46:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 05/12/2004 :  16:01:22  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 05/12/2004 :  16:02:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 05/12/2004 :  17:01:25  Show Profile  Reply with Quote
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

USA
752 Posts

Posted - 05/12/2004 :  17:31:46  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 05/12/2004 :  17:58:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
752 Posts

Posted - 05/12/2004 :  18:15:15  Show Profile  Reply with Quote
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 - 05/12/2004 :  19:07:22  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 05/12/2004 :  19:11:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
32 Posts

Posted - 04/25/2006 :  17:47:49  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 04/25/2006 :  17:50:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
32 Posts

Posted - 04/25/2006 :  18:02:17  Show Profile  Reply with Quote
ok. will do.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000