MS SQL Server :: Backup Files In Append Mode To Be Copied.

SQLEXPRESS Backup File Losing &&"NETWORK SERVICE&&" User When Copied
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:

srv = New Server("MYPCSQLEXPRESS")

db = srv.Databases("washmaster")

Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)

Dim recoverymod As RecoveryModel

recoverymod = db.DatabaseOptions.RecoveryModel

rs.NoRecovery = False

rs.Devices.Add(bdi)

rs.Database = "washmaster"

rs.ReplaceDatabase = True

srv.KillAllProcesses("washmaster")

rs.SqlRestore(srv)

This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:

....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)

The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.

How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?

Thanks,
SJonesy

View Replies !

Backup Append Problem
We have a file where my co-employee backup's our database. The problem is that the file is getting too large because he always use APPEND when he backup's. Is there a way to delete some of the appended backup instances?
Thank you very much for your help.

View Replies !

Append New Backup On A Device
hi all!

appending new backup on a same device - yes or no?

if somebody can tell me, is ok to constantly create new backus on a same device? i'm asking that, because in that case, defice file it will grow and grow.

is there any limit or suggestions how many appending is recomended, cause of file growth?

tnx in adv!

View Replies !

I Want Restore My Backup With Append
hello my freands
i attached my database on 3 computer those are stand alone.
i get backup form those separately .
now,i have 3 backup files,with name:
"bkcomputer1.bak","bkcomputer2.bak" and "bkcomputer3.bak"
i want merge these,and make new file with new name "master"
and restore to new computer
my code for restor is:
restore database [mydatabase] from disk='c:kupcomputer1.bak'
restore database [mydatabase] from disk='c:kupcomputer2.bak'
restore database [mydatabase] from disk='c:kupcomputer3.bak'
but this way is false,because each restore deleted structure and data old data base,therefore after compile 3 over line,i have only data of computer3
please help me....
thanx

M.O.H.I.N

View Replies !

Backup Restoration / Append Query
Hi All:Being a bit of a nube to MSSQL I could use a little advice. This is thesituation.A client's HDD became full so I backed up, deleted then recreated a blankdatabase (they said they didn't need the stored data).They have now deceided that they want the data again.My plan is to restore the database to a second volume so it can grow as muchas is needed, however, there is now data in the second instance that wouldneed importing to the restored DB.I can restore the backed up db with a different name and reconfigure thesoftware that accesses it but what would be the best method to append therestored DB with the data in the current instance of said DB? Both DBs areidentical.TIAMP

View Replies !

Tape Backup Append Fails, Overwrite Succeeds
SQL S7 sp1 NT4 sp5 HP Colorado TR4
Using wizard to schedule backup
Consistent error set/get_tape_device_parameter failure

Tried to test using backup from enterprise manager
Complete backup works only when overwriting existing tape
Selecting append causes error to appear

Databases 200MB or less
Reformatted tapes after conversion from 6.5
Increased size MSDB to greater than largest DB
HP tape assure says tape unit working fine

What next ?

View Replies !

Configuration Files Do Not Work In CmdExec Mode
I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig (on my dev machine).

Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.

However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:....myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig cannot be found.

What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.

Is there a way around this?

View Replies !

Hot Backup In SIMPLE Mode
We have a SQL Server 7.0 database running with trunc. log on chkpt andselect into/bulkcopy checked and need to develop a backup strategy.One of our DBAs insists that since the transaction log is beingtruncated, we can't do a hot backup (a FULL backup in multiuser mode)because if a transaction comes through during the backup it will leavethe backup in an inconsistent state. I'm skeptical, but I don't knowhow SQL Server 7 avoids this problem.If SQL 7 is not truncating the transaction log, it uses thetransaction log to roll forward changes that occurred during thebackup. Obviously if it's truncating the log it must have some way toapply these transactions anyway (such as not doing a checkpoint duringthe backup, backing up the log at each checkpoint, etc.).Can anyone confirm that a hot backup will be valid when trunc. log onchkpt is checked? Does anyone know how SQL 7 accomplishes this?Thanks!James

View Replies !

DB Backup - Single User Mode
Hello ,Is it possible/recommended to do SQL server instance backups in Singleuser mode ?Thanks in advance,atv

View Replies !

Database In Suspect Mode- No Backup Available
Database is in suspect mode and I cant get it out. Customer does not have a backup.........

getting Errors starting

Analysis of database 'SBM01' (7) is 100% complete (approximately 0 more seconds)

ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to create symptom dump

Error: 7987, Severity: 22, State: 1

A possible database consistency problem has been detected on database 'SBM01'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'SBM01'.

ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to create symptom dump

Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 51, PSS = 0x19b591e0, EC = 0x19b59510
*
* User initiated stack dump. This is not a server exception dump.

Error: 3314, Severity: 21, State: 3

Error while undoing logged operation in database 'SBM01'. Error at log record ID (222:9976:477)..

Error: 9004, Severity: 23, State: 2

An error occurred while processing the log for database 'SBM01'..

Error: 3414, Severity: 21, State: 1

Database 'SBM01' (database ID 7) could not recover. Contact Technical Support..

View Replies !

Transaction Log Huge Loading SQL Profiler Trace Files In Simple Mode

Hi there - can anyone advise on the following issue. We have recently performed some server side tracing on a particular SQL instance over 24hr period. We are now attempting to load these into a database for analysis. Here lies the problem.

When we are loading the profiler trace files (one at a time) into the database the transaction log is growing at an excessive rate. Even though the database is in SIMPLE mode.

We are loading the traces using the command:

INSERT INTO sqlTableToLoad
SELECT * FROM ::fn_trace_gettable('MytraceFileName', DEFAULT)

Can anyone advise how we could possibly get round this issue as we're running out of space due to the transaction log.

Thanks

View Replies !

Is There A MS SQL Backup Tool That Will Zip Or Compress The Backup Files?
Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.
I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.

View Replies !

Dynamic Backup With Configurable # Of Backup Files
This is an initial piece of work to build a backup command and configure the number of backup files dynamically. I've had a desire to do this for a while and finally sat down yesterday and worked out what you see below. This is initially just a POC to see what it will take for me to incorporate this desire into my current backup routines. However, it is functional for quick ad-hoc work and can easily be converted to an sp, so here you go. :)

I was also prompted onto this by a desire that a member has indicated he wants for his backups as well. However I did not solve his exact request - he wanted dynamic backup directories & files so that he could stripe them across different devices. If I understood correctly. My routines use one share to backup the database, I don't think it's wise to start spreading individual backup files across different shares/disks (unless it's absolutely neccessary) as it creates a fairly complex set of backup and restore op's that if not well documented, can be...difficult for people to use other than the original author.

Looking for comments and feedback, I'm not really a guru at building strings in this manner and if there's a better way, I'd like to know.

SET NOCOUNT ON
GO

SET CONCAT_NULL_YIELDS_NULL OFF
GO
/******************************************************************************
**
**Name: Dynamic_Backup_Devices.sql
**
**Description: Create backup command with X number of backup files.
**
**Author: G. Rayburn
**
**Date: 11/08/2007
**
**Depends on: SQLAgent having network access to UNC path.
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 11/08/2007 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
DECLARE @NumBakFiles int
, @DBName sysname
, @BackupStr_01 varchar(256)
, @BackupStr_02 varchar(256)
, @BackupStr_03 varchar(256)
, @BackupStr_04 varchar(256)
, @CurrLoopOp int
, @DynString_01 varchar(2048)
, @DynString_02 varchar(1024)

SET @NumBakFiles = 3

SET @DBName = 'Foo'

SET @BackupStr_01 = 'BACKUP DATABASE [' + @DBName +']' + char(10) + char(13) + ' TO '
SET @BackupStr_02 = 'DISK = ''\SERVERFOOSQLDUMP' + @@SERVERNAME + 'Databases' + @DBName + '' + @DBName
SET @BackupStr_03 = '_' + CONVERT(varchar(8),getdate(),112) + '_1.BAK''' + char(10) + char(13)
SET @BackupStr_04 = 'WITH INIT'

SET @DynString_01 = @BackupStr_01 + @BackupStr_02 + @BackupStr_03

-- Uncomment if you don't like the CONCAT_NULL_YIELDS_NULL setting.
-- SET @DynString_02 = ''

SET @CurrLoopOp = 2

WHILE @CurrLoopOp <= @NumBakFiles
BEGIN
SET @DynString_02 = @DynString_02 + ' , ' + @BackupStr_02 + '_' + CONVERT(char(8),getdate(),112) + '_' + CONVERT(varchar(2), @CurrLoopOp) + '.BAK''' + char(10) + char(13)
SET @CurrLoopOp = @CurrLoopOp + 1
END

SET @DynString_01 = @DynString_01 + @DynString_02 + @BackupStr_04

PRINT @DynString_01

-- EXEC (@DynString_01)
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO

View Replies !

Database In Single User Mode After Backup
Hi!

I have a problem with databases that are left in single user mode after transaction log backup.
I have a database maintenance plan job that backs up the transaction log and checks data and index linkage every hour.
Sometimes the job fails and when I look in the report file it says that it has tred to put the database in single user mode and failed because the database is in use and then in the next step it says that it cannot perform the operation because there already is a user in the database.

Why is the database put in single user mode?
What can I do to avoid finding my databases in single user mode?

Below is a part of the report file:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 15089: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot change the 'single user' option of a database while another user is in the database.
[1] Database BV Produktion: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database BV Projektering: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 924: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dms_user' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dms_user' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** Execution Time: 0 hrs, 0 mins, 9 secs **

View Replies !

SQL Server 7 In Single-User Mode After Backup?
Hi:

This question came to me from a friend and I don't have a lot of details so I apologize in advance. I thought it might be worth a shot to ask here since I couldn't find anything on point in the MS help or KB. A SQL Server 7 database running on an NT 4.0 box is having a full database backup nightly. The backup routine is pretty standard, created using the Maintenance Wizard. It backs up two databases on the server. The problem is that when folks come into work in the AM, the databases are in Single-User mode and someone has to go in and change this. Is this normal behavior? My (admittedly limited) understanding of SQL 7 backups is that they are relatively transparent, and Single User mode is not necessary. You can operate normally other than you can't to any unlogged txn's while the backup is running. They have not done anything explicit to put the databases in Single-user mode when the backup begins. Any ideas how to prevent this from happening? (Or how to automate the switch out of Single-user mode when the backup is completed?)

Thanks!

View Replies !

Backup Of SQL Database In Single User Mode ?
Hello ,

Is it possible to do the backup of SQL server instance in a Single user mode ?
What is the recommended practice ?

Thanks in advance,
atv

View Replies !

Db Backup Simple Vs. Full Recovery Mode
When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?

View Replies !

Restoring 2005 Backup With 8.0 Compatability Mode On 2000 - Help
Hi,

I have a database that is in the SQL Server 2000 compatability mode on my SQL 2005 server. I am trying to restore a backup of this database on my SQL Server 2000 database on another server and keep getting strange messages. First trying Red-Gate and then plain SQL Server with no luck.

It seems as I remember that the 7.0 and 2000 compatibility issue between backups did not arise when the compatibility mode was set to 7.0.

Is this "planned" behavior or an "undocumented feature"??? Anyone else have success doing this?

Thanks,

John Campbell

View Replies !

Backup Db To Split Backup Files
Hi everyone,

I want to do sql db backup.But how can I backup db to split backup files? The reason I want to split the backup file is becasue single file size is too big and I want to write to dvd.

any idea or scripts?

cheers

View Replies !

Is It Possible To Prevent Databases From Being Copied?
Hi,

We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end.

Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data.

Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies.

Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication.

Is this possible?

Regards,

Sigol.

View Replies !

Attach Db File Copied Previously
I got a mdf file from my co-workerwho just stopped SQL server and delete ldf fileand give me a mdf file.if it was detached, it would work finebut it was just copied.Is there any way to attach this mdf file?I tried sp_attach_db and sp_attach_single_file_dband both failed.thanks,

View Replies !

Different Results Same Query Between Original And Copied Db
Hi all,

I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong.

Has anybody seen this behavior?

Thanks for your help

Luis Torres

View Replies !

Replication SPs Copied W/CRLF In Names
Hi folks -

New DBA (longtime developer) here. I've just noticed something strange on a couple of my databases (SQL 2k). There are some stored procs that have a CRLF or CR+CRLF prefixing the name (they show up as unprintable-char black squares in EM).

They all appear to be replication-related SPs (e.g. ). The names are as follows:
sp_MSdel_<replicated_table_name>
sp_MSins_<replicated_table_name>
sp_MSupd_<replicated_table_name>

There are corresponding normally-named SPs. Not all the replication-related SPs have been copied this way. The create date of all the new SPs is the same, and is (IIRC, I wasn't involved then...) the date of the SP4 application to the server.

Anyone ever seen this? More importantly, is there a way to determine if these SPs are being used? I'd like to delete them if I can, but not until I'm sure of what's happened. TIA for any input.

View Replies !

Char Varchar Not Copied While Transferring

Hi,

I have the following problem.

I am using DTS connecting SQL SERVER 2005 and Sybase9. I successfully copy from  Sybase9 to SQL SERVER 2005.

Now when i am copying from  SQL SERVER 2005 to  Sybase9 char and varchar types are not copied. The data types are the same as well as data type length. Integers, numeric and date values are copied. I have tried data conversion but that did not help.

sql server 2005 collation is Greek_CI_AI and Sybase9 collection is 1253ELL

Any solution how to solve loosing char and varchar types when copying from   SQL SERVER 2005 to  Sybase9 ?

Note: I connected to Sybase using System DSN

Regards,
Vasilis

View Replies !

Backup SQL Files
Hi,We are about to install MSSQL Server 2000, on a Windows XP HomeMachine. However, we have servers we could set routine backups of filesto be done to. What what be the best way of doing this?Is there functionality in SQL Server 2000, where we can say dump alldata definitions, accounts, and data to files on this drive at regularintervals?What other suggestions do you have apart from obviously the usual RAID,and Tape Drive stuff?ThanksDavid

View Replies !

Backup Files
I am going to move to a different host. Can I get back up files of my SQLServer database (from the host) and use those to set up on the new host? Andif so, is that what I should be asking for - backup files? Someone told methere should be 2 files.Thanks

View Replies !

Backup Files
hello! can you take a look at my question?
We use ARCServe to backup exchange server file (priv1.stm and priv1.edb) every night. When I select both of the files and start to back up using ARCServe. It showed me that Backup Operation Successful.

When I check the files, it didn’t backup both of the files. I don’t know why. Do you know how to backup these two files?

Many thanks.

View Replies !

Backup Files
I have heard from two different source's, how many Backup files you can have.
1) 16

2) 32

Which is the right answer.

View Replies !

Copied Database Maintains Original Name In Backups
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,

View Replies !

One Table Is Ultra Slow At Being Copied, Others Are Fine
This is a really really odd problem.

Here's the situation. I've got a DB with several tables. Let's pick out two of those tables from the bunch.. they both have approx 2500 rows in them. I take one and duplicate it (right click, All Tasks->Export Data, copy tables, etc) and it duplicates just fine into another DB.

Now, the second table.. takes FOREVER. Yet it has the same amount of rows in it! And this is a flat table export.. I'm not including dependent objects or anything like that.

Not to mention this is slow, but when I use Red-Gate SQL Data Compare, it gets stuck when it hits this specific table.

Anyone have any idea what would cause this?

View Replies !

SSIS Error With Duplicate IDs Of Copied Packages
Hi,

I recently encountered an error when I created several copies of one package.

It's always nearly the same package with small modifications. I call this packages from a parent package which is part of our datawarehouseing-framework.

The problem is, when copying a packages or using a packages as template the packages' IDs and Task's-IDs are the same. And this isn't only an issue concerning logging!! :

When the parent package calls one of the copied packages the first task is executed in every package parallely. Furthermore ... when I for example set a breakpoint on a data transformation task in one of the packages, the breakpoint is set in all packages on the same task! This is resulting in strange errors because the tasks-states and variable values seem to get mixed up.

Unfortunately there is only a possibility to change the package's ID, but the IDs of tasks are readonly!

One solution is, to create a new package and copy all the tasks to the new package which creates new IDs, but doing so, I have to manually recreate a long list of variables, all the configurations, all the connection-managers once again. Furthermore I loose the layout of tasks.

I found some posts about it here

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/6f85a31ea190608a/0eae312aa8440cf8?lnk=gst&q=pitfall&rnum=1&hl=de#0eae312aa8440cf8 or

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/760093d58bf6ccb5/32ced2f2020ef3f7?lnk=st&q=data+flow+task+id+copy&rnum=2&hl=de#32ced2f2020ef3f7

saying the issue will be fixed by SP2, but now I don't see any comment on it in the CTP of Service Pack 2.

Is there any solution to this problem or official roadmap about a fix from Microsoft??

Greetings Monte

View Replies !

BackUp SQLEXPRESS Mdf Files
Hi All,I searched the archives but I could not find any useful stuff for me.I can backup a database on SQLExpress like USE masterEXEC sp_addumpdevice 'disk', 'Store_1', 'c:Store_1.dat'BACKUP DATABASE Store TO Store_1Here is my problem: If I use logins,roles etc. in an application automatically a mdf file is created. Or I can create a new sql database. SQLExpress dynamically use the mdf file when connection string defined. This mdf file is not defined as a database on SQLExpress. Because SQLExpress has not a gui, I cannot see the registered database. How can I backup any sqlexpress mdf file without deattaching or attaching?Thanks

View Replies !

Backup Database To CSV Files
HiWhat is the easiest option for Exporting ALL of my SQL Server tables into a CSV file (either separate CSV files for each table or one big file with all the table columns and data) ???I just want to Backup my SQL Server database like we backup MySQL database using phpMyAdmin. Unfortunately, my SQL hosting company does not allow backups for free.Thanks for help

View Replies !

Database Backup Using .LDF && .MDF Files
Hi,
I am developing a tool that takes snapshot of a particular database.
By snapshot I mean, attaching the .LDF & .MDF files.
Would you please let me know how to access these files & take a backup of the same.
And also how to restore them back. (using c# programming)
Thanks,
Archana A.A.

View Replies !

Backup Database Using Files
I am but a lowly DBA unworthy of this task...

I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.

My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.

While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:

-- =============================================
-- Create database on mulitple file groups
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO

CREATE DATABASE MultiFile
ON PRIMARY
( NAME = MultiFile,
FILENAME = N'e:MSSQLDataMultiFile.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile2,
FILENAME = N'e:MSSQLDataMultiFile2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile3,
FILENAME = N'e:MSSQLDataMultiFile3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile4,
FILENAME = N'e:MSSQLDataMultiFile4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = MultiFile5,
FILENAME = N'e:MSSQLDataMultiFile5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)

LOG ON
( NAME = MultiFile_Log,
FILENAME = N'e:MSSQLDataMultiFile_Log.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO

I have tried the following backup script:

BACKUP DATABASE MultiFile
FILE = 'MultiFile',
FILE = 'MultiFile2'
TO Backup01
WITH
INIT

BACKUP DATABASE MultiFile
FILE = 'MultiFile3',
FILE = 'MultiFile4'
TO Backup02
WITH
INIT

BACKUP DATABASE MultiFile
FILE = 'MultiFile5'
TO Backup03
WITH
INIT

And here is the restore script:

RESTORE DATABASE MultiFile2
FILE = 'MultiFile',
FILE = 'MultiFile2',
FILE = 'MultiFile3',
FILE = 'MultiFile4',
FILE = 'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:MSSQLDataaMultfile.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile2.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile3.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile4.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile5.mdf',
MOVE 'MultiFile_log' TO 'E:MSSQLaMultFile_Log.ldf'

However, running the Restore script generates the following error:

Server: Msg 3259, Level 16, State 1, Line 1
The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?

I am trying this because my objectives are to:
1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window.
2. The database must be up and operational 7x24x365 (except for one 4 hour window each month)
3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.

I appreciate any thoughts and or guidance you can provide.

Regards,

hmscott

View Replies !

Backup Files Not Released
hi

I have had trouble with maintenance plans not deleting expired backups, & this filling the hadrdisk. i have now done an agent job using tsql backup with init/skip, so it just overwrites. problem now is that it fails saying the backup device isnt available (cant delet the file from OS either) - so this was obviously the prob with the maintence plans too - sql/os doesnt seem to be releasing the file after it is completed and a server restart is needed to release it...
anyone know how to sort this?
thanks
des

View Replies !

Differential Backup Files
When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.

View Replies !

Shrinking Log Files After Backup
Hello everybody.
I have SQL2000 sp3 standard
with 50 db's

All db set for full recovery
and autoshrink
Backup done with Tivoli

full backup once a week
log backup done every 12 hrs

Problem .. shrinking logs

every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db

70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."

Why i getting "Cannot shrink ..." ?

even if job runs right after backup of the log files ,I still have messages.

Thank you

Alex

View Replies !