Backup / Restore Question - MSDE - Microsoft SQL Server

Tim Morrison

MSDE2000

I have an application in which I am running a TSQL command of BACKUP DATABASE and RESTORE DATABASE for the backup and restore commands for my application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the database)

Now I have the database back with all my data. What are the gotchas when doing a backup and restore using this method? I am not relying on transaction logs to restore to a certain point, the user can only restore back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim Morrison

Jul 20 '05 #1

Subscribe Reply

9098

Dan Guzman

This method will work find for the SIMPLE recovery model. It doesn't matter
whether or not the target database exists since it will be recreated during
the restore if needed.

Be sure to backup WITH INIT or the backup will append to the existing backup
file and the file will grow indefinitely. Also, consider copying the backup
file elsewhere for disaster recovery.

--
Hope this helps.

Dan Guzman
SQL Server MVP
"Tim Morrison" <sa***@kjmsoftware.com> wrote in message
news:YE3Db.545312$Tr4.1480932@attbi_s03...
MSDE2000

I have an application in which I am running a TSQL command of BACKUP
DATABASE and RESTORE DATABASE for the backup and restore commands for my
application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
database)

Now I have the database back with all my data. What are the gotchas when
doing a backup and restore using this method? I am not relying on
transaction logs to restore to a certain point, the user can only restore
back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim Morrison

Jul 20 '05 #2

Tim Morrison

Yes, I have the database set to simple recovery in my initial SQL script the
user uses to create the database.

I also have WITH INIT in my backup command.

I also discovered that the restore database name does not have to be the
same as the initial database. This is both a benefit and a risk.

I even 100% uninstalled MSDE, and reinstalled (Including SP3a), then
performed my restore command, and everything seems to work perfectly.

Im guessing that if I wanted to send a sample database with my application
that includes sample data, it would be very easy to do using this method.

Im learning more and more every day. I have a SAMS SQL Server 2000 book
which is always helpfull

Tim Morrison

"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:7O****************@newsread2.news.atl.earthli nk.net...

This method will work find for the SIMPLE recovery model. It doesn't matter whether or not the target database exists since it will be recreated during the restore if needed.

Be sure to backup WITH INIT or the backup will append to the existing backup file and the file will grow indefinitely. Also, consider copying the backup file elsewhere for disaster recovery.

--
Hope this helps.

Dan Guzman
SQL Server MVP
"Tim Morrison" <sa***@kjmsoftware.com> wrote in message
news:YE3Db.545312$Tr4.1480932@attbi_s03...
MSDE2000

I have an application in which I am running a TSQL command of BACKUP
DATABASE and RESTORE DATABASE for the backup and restore commands for my
application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
database)

Now I have the database back with all my data. What are the gotchas when
doing a backup and restore using this method? I am not relying on
transaction logs to restore to a certain point, the user can only restore
back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim Morrison

Jul 20 '05 #3

Tim Morrison

This method will work find for the SIMPLE recovery model. It doesn't matter whether or not the target database exists since it will be recreated during the restore if needed.

Be sure to backup WITH INIT or the backup will append to the existing backup file and the file will grow indefinitely. Also, consider copying the backup file elsewhere for disaster recovery.

--
Hope this helps.

Dan Guzman
SQL Server MVP
"Tim Morrison" <sa***@kjmsoftware.com> wrote in message
news:YE3Db.545312$Tr4.1480932@attbi_s03...
MSDE2000

I have an application in which I am running a TSQL command of BACKUP
DATABASE and RESTORE DATABASE for the backup and restore commands for my
application. For testing purposes, i did the following:

1) Ran a BACKUP DATABASE command to a file named C:\TEST.BAK.
2) Deleted the database completely.
3) Ran a RESTORE DATABASE on the same file (note, I did NOT recreate the
database)

Now I have the database back with all my data. What are the gotchas when
doing a backup and restore using this method? I am not relying on
transaction logs to restore to a certain point, the user can only restore
back to their last backup (may be daily, weekly or monthly)

TIA

--
Tim Morrison

Jul 20 '05 #4

Similar topics

8982

Restore a remote backup

by: emmexx | last post by:

I want to restore a huge database into my workstation. The size of the backup file is more than 6 GB and I don't have enough space on my HD for both the database and the backup file. So I put the file in a shared folder on a pc connected through a switch to my pc. My wkst uses w2k pro sp4, the other PC win xp home SP1. MSDE 2000. The share...

Microsoft SQL Server

2547

backup

by: JIMMIE WHITAKER | last post by:

I'm using ms access 2000 .adp file, and tables on sql server / msde. On the menu there's a backup option. It creates a file with a .Dat extension. How do I take the Dat file and restore data? This news group has been real helpful-- thanks.

Microsoft SQL Server

2005

Will OSQL Produce SQL Backup?

by: john_20_28_2000 | last post by:

Will osql product an SQL backup? By this I mean a text file with the transact-sql in it so I can use it to recreate the database elsewhere? Thank you.

Microsoft SQL Server

2362

SQL Backup procedures...

by: gcetti | last post by:

Can anyone recommend backup procedures for a SQL Server/MSDE product? We have upgraded our program from Access to SQL & before we could just tell our customers what .mdb files to include on their backup script. Now with SQL, the BACKUP/RESTORE SQL commands work as backups, but would involve a separate process for our customers to execute,...

Microsoft SQL Server

3058

Clearification of 32 and / or 64 DB2/UDB 8.X backup restore under AIX 4.3 and AIX5.2

by: Vikrant | last post by:

Friends, I have read DB2/UDB 8.x 'RESTORE DATABASE Command', with my 'limited' knowledge & skill. I think it should address my concern, but I want advice/ opinion / experience and any care I must take to restore DB2/UDB 8.x database 64 to 32 bit). (1) Due to hardware limitaion I can not have 64 bit DB2/UDB 8 on AIX 4.3.3 host. Let us...

DB2 Database

3329

db2 backup, StorageManager

by: Eric Herber | last post by:

I've a question regarding db2 (V8.1) and database backups going to a storage manager like TSM for example. As I can see in the storage manager if I backup the complete database over the TSM API (no tablespace backups) the backup images is stored as a single backup object. Later I will be able to restore the complete database from this...

DB2 Database

3793

backup/restore nTs data

by: Hardy | last post by:

hi gurus, now I have to backup and restore a 8 T size db2 database. from two s85 to two 670. the partitions,tablespaces of the db should be redesigned then I plan to use redirected restore. but my concern is, such big size db, I'm afraid something unexpected will destory all the effort. Who have related experience? Can you give some advice?...

DB2 Database

2043

Calling SQL DMO to backup / restore DB via C#

by: Paul Aspinall | last post by:

Does anyone have any sample code, or references to help when calling SQL DMO to backup / restore DB via C# Thanks

C# / C Sharp

2019

to read backup file

by: Zenek | last post by:

Hello, I have: - server MS SQL MSDE (2000) - database 'COLLBASE' - table 'MAIN' - row: column 'NAME' value 'version' and column 'VALUE' value '003' I make backup files by SQL query.

Microsoft SQL Server

7329

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...

General

7253

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7476

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

5779

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...

Career Advice

4815

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3313

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

3314

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

888

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

549

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General