Author |
Topic |
SQLError
Yak Posting Veteran
63 Posts |
Posted - 2004-05-12 : 11:53:48
|
Hi, I currently and using the commandoConn.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 mydb?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 |
|
|
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? |
|
|
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 |
|
|
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 infoI 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. |
|
|
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.DanielSQL Server DBA |
|
|
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 -EYou should then see >1You 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 exitexitTara |
|
|
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 -EYou should then see >1You 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 exitexitTara
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.DanielSQL Server DBA |
|
|
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 ofall the data I need, or just my database?Why would I need to back anything else up? |
|
|
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 |
|
|
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! |
|
|
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 SomeDatabaseFROM 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 Kizeraka tduggan |
|
|
bogus
Starting Member
41 Posts |
Posted - 2006-04-25 : 18:02:17
|
ok. will do. |
|
|
|