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 master
EXEC sp_addumpdevice 'disk', 'Store_1', 'c:Store_1.dat'
BACKUP DATABASE Store TO Store_1
Here 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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
SQLExpress, Locked Files, And Sp_detach
I have a situation where I want to copy a SQLExpress mdf file, after having read some metadata from it, but I find that an exclusive lock persists on the file for about 5 minutes after closing... the lock is held by "sqlservr.exe" This lock prevents me from copying the file. It seems that I may be able to fix this by calling sp_detach_db after closing the connection (does this seem reasonable?) but I am unsure what the database name (dbname) is that I need to pass to sp_detach_db. To make this a little more concrete, here is the connection string I use... "Data Source=.SQLEXPRESS;AttachDbFilename=C: estdataDVDCollectionDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True'" so in that case what would be the dbname? Note that I need to do this through API (in a .NET application)
View Replies !
View Related
Backup Sqlexpress 2005 To A 2000 Database -
I have sqlexpress 2005 on my local machine - but my hoster has sql 2000 - so i used the DB publishing wizard to create the script and sent it over to them - they are now saying that they need a .bak file in 2000 to host my database - is this a bunch of horse hockey or not? if this is legit - any ideas on how I can create a .bak 2000 sql from sqlexpress 2005? many thanks
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Sqlexpress Installer Doesn't Believe Sqlexpress Has Been Uninstalled
Because of numerous problems trying to get sqlexpress working, I uninstalled it with the intention of reinstalling (via Add or Remove Programs). However, now when I try to reinstall it, I get a message that the I am not making a changes so it won't let me install it. I do have sql server 2005 developer's edition installed; is that the reason? and does that mean I cannot have both installed on the same machine?
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Delete Old Backup Files
hi, i want to delete the backup files older than 15 days. how can i do this? Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running . Any idea? thanks in adv. kumar
View Replies !
View Related
Size Of Backup Files
I need to know how big SQL Server 7 backup files are in comparison to the database size. For example if I have a database that is 300 Mb and I do a complete backup to disk with SQL Server 7 will the backup file be about 300 Mb? Thanks, Mike
View Replies !
View Related
Is It "Backup Dump" Files Available In 7.0?
Hi Everybody, I hv worked in 6.5. There we take backup through the command 'Dump Database'(e.g---> "Dump database master to masdump WITH INIT." ). As for as I know, in 7.0 we are taking backup using the command "Backup Database". Is it possible to use "Dump Database" command in 7.0. Can anyone give explanation on that please. thanks in advance Srinivasan
View Replies !
View Related
Migration Without Backup-files *.dts
Hello All, I have to migrate SQL 2000 DTS-Packages to SQL 2005. My Problem is that I didn't saved my DTS-Packages as files and the SQL 2000 Server exist no longer. Actually I have only the old hard disk with the SQL 2000 databases. Do you have any ideas what can I do? Thanks Jonas
View Replies !
View Related
Log Backup Files - Appending
Quick question regarding log backup files. Currently I have a maintenance plan running a Full backup weekly, differential backups nightly, and log backups hourly. The log backups are all going into a single backup file - but it's hard to see what's going on behind the scenes here. Does this file get 'reset' when the full backup is performed? Will it just keep growing indefinitely and should I be creating new files for each log backup, or manually deleting the file each week during the full backup task? Thanks
View Replies !
View Related
Different Files In One Backup (MS SQL 2005)
Hi there, I have sent a mssql backup file in .bak format to my host admin and asked them restore it. but they told me there are 3 files with 3 different dates in that backup what does it mean? should I ask them to restore all of them or just the last one? Thanks very much in addvance (context: MS SQL 2005 express) Cheers Shahram
View Replies !
View Related
Remove Scheduled Backup Files
Dear all, I have no problem with backing up database. My solution is that when I scheduled backup file (daily) for my database and it worked well. But when i created the scheduling , i didn't set the end date, so the backup scheduling still work even if I remove the backup file path in backup diglog box. Now I wan't to remove that backup scheduling, anyone tell me how to do that? Thanks so much....
View Replies !
View Related
I Dont Have Any Backup Of My Database, But I Have The Mdf's And Ldf's Files
Im trying to recover my database using the mdf and ldf files.I dont have any backup and i have recovered two of the mdf files usinga tool which "discovers" deleted files after hard drive formatting...It sounds cool, isnt it...:? :(Obviously, i get a "suspect" message when the server starts and the logfile says this kind of things:·"Full PathName.MDF is not a primary database file." (This is one ofthe files repaired using the magic tool.·Error: 823, Severity: 24, State: 6·"I/O error (torn page) detected during read at offset0000000000000000 in file 'Full PathiName2.mdf'... Name2.mdf is thesecond file·Device activation error. The physical file name 'Full PathName2.mdf'may be incorrect.When i try to execute the command "DBCC CHECKDB ('Database_Name') WITHPHYSICAL_ONLY" i get the following message :·Could not open FCB for invalid file ID 0 in database 'Logs'.Do you have any ideas? Thank you very much...:D
View Replies !
View Related
SQL2005 Database Backup Files
We are in the early (development test) stages of upgrading from SQL200to SQL2005. We have noticed that our backups, created with the appendoption, are not stored as individual .bak files when viewed throughWindows Explorer. From time to time, we have to create disk space bydeleting backup files. How is this managed in SQL2005?
View Replies !
View Related
Compressing Backup Files In SQL 2005
Hello All, I tried to backup my database in SQL Server 2005 (Express edition). But the size of the backup file was the same as that of the database file. Is there any means to compress and backup so that the size of the backup file is much smaller? Regards, Yuvanya
View Replies !
View Related
Backup Issues ---Open Files
I am using Seagate Backup Executive for backup of SQL data and log files. I get the following reading for SQL files------ File Open, Not Backed Up. Backup Executive has been set up to back up open files, and is backing up some open files on the network (ie Lotus Notes databases). Is there any procedure I need to follow in order to set up a third-party application (like Seagate Backup Exec) to backup SQL data (log and data files). Could it be a permissions problem ??? Has anyone gotten Backup Exec to work with SQL Server 7.0? Can you recommend any literature on Backup tasks? Thanking you in advance for your help Shaleen
View Replies !
View Related
Deleting Backup Files Question
I have created some backups where expiration dates or days to be retained were not specified. Using EM, how do I find the old backups. Using EM, how do I delete the old backups (hoping this will clean up the MSDB tables and physically delete the files from disk). Thanks!!!!
View Replies !
View Related
Help Deleting Old Backup Files Using Sqlmaint.exe
All, I have this as my command line using sqlmaint.exe SQLMAINT.EXE -D PerformanceObsProgram -CkDB -CkAl -UpdOptiStats 15 -RebldIdx 20 -BkUpDB F:MSSQL7BACKUP -BkUpMedia DISK -DelBkUps 2months -Rpt F:MSSQL7LOGPerformanceOBSProgram_DBkUp.rpt -DelTxtRpt 2months My problem is that the files that are older than 2 months are not being deleted. The account has admin right on both NT and on SQL server.
View Replies !
View Related
Copying Backup Files To Different Locations
I have a maintenace plan that copies production backup files in DR server. The production is mission critical and runs 24/7. We have a round the clock backups. Backups include full backup, differential backups and log backups. To secure these backup files, I was asked to keep these files in different server at the same time as it goes to DR Server. As I am using maintenace plan for scheduled backups that go to DR server and I had no idea how to incorparate copying these files to another server in the same maintenence plan. I came up with idea using xp_cmdshell stored proc that I attached on the maintenace plan in executeT-SQL statement task. The statement looks like Exec master..xp_cmdshell 'copy e:DRbackup*.bak \fgh-sql16ackup$' Above statement copy all backup files on DR server and takes these to remote server called fgh-sql16. My question here is ....are there any other effective methods to copy backup files simultaneously to different servers via maintenace plan. Thanks all
View Replies !
View Related
Remove Old SQL 2005 Backup Files
I am trying to configure a maintenance plan in SQL 2005 to do a full backup every day and to do log file backups every hour. What I want to do is to keep 3 days worth of full backups and 24 hours of log backups and remove all the backups older that this from the server so that I do not use all of my disk space. I will then backup these files to tape so that I have a good backup history. In SQL 2000 there was a simple option to remove backup files older than a given time frame. Is there an option to do this in 2005? If not can you suggest a way that I can do this.
View Replies !
View Related
Backup/Restore From/to Compressed Files
Hi everyone, already tried this in other SQL forums, but maybe i have some luck here. I need mainly to restore database backups from customers. They arrive in all kind of formats (zip, rar, gz). I'd like to be able to restore those directly from the compressed file, because i'm talking up to 7GB rar files which take a while to uncompress in a separate step. I'm working for 6 years in R&D environments, but mostly on Linux/Oracle where this is an easy task using pipes, but i haven't found a sinlge web page, post or even script to do this with MSSQL. The VDI is not really what i'm looking for, so aren't backup software like SQLBackup, Litespeed etc. because i can't force the customer to use those. Anybody any idea or even the same problem maybe with solution? Thx.
View Replies !
View Related
T-SQL To Delete Files From Backup Device
I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10 Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be. All the backups and T-log backups are written to one device which is one file. Thanks Scott
View Replies !
View Related
Backup Individual Files Vs Whole Database
We have a pretty large SQL Server database, just shy of 400 GB. It is divided up into 24 different data files. The previous DBA here said it is faster to backup the backup individual data files to seperate files. So what he did was issue 6 backup statements like the one below as a step in an agent job. BACKUP DATABASE MYDATABASE FILE = 'Data', FILE = 'data2', FILE = 'data3', FILE = 'data4' TO Data1_4 WITH FORMAT Next step: BACKUP DATABASE MYDATABASE FILE = 'data5', FILE = 'data6', FILE = 'data7', FILE = 'data8' TO Data5_8 WITH FORMAT etc.. in sequence. What I question is how this is faster than just issuing a backup for the whole database? Has anyone ever ran into this before? - Eric
View Replies !
View Related
Backup Jobs Not Deleting Old Files!
Such a simple task. Not doing as it should!! This Maintenance Cleanup Task is set to delete all BAK and TRN files it's made (in seperate maintenance plans) in given path, with the given file extension, delete files based on the age of the file at task run time. Delete files older that 4 days. The files are now backing up for months and months. I'm not going to take care of this. I've got a computer to do this for me, every time it runs its jobs, every 4 hours and once overnight. This is ignoring commands and refusing to do as it's told. I've checked the settings in here over and over. It's so simple - what could be wrong? I've checked the path, the file age, the extensions... The disks are getting full! Has anyone seen anything like this?
View Replies !
View Related
Delete Backup Files Older Then...
Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now. What on earth am i not doing? Thanks in advanced
View Replies !
View Related
Backup SQL Database Files With Vb.net 2005
I am trying to backup the sql database files from a local computer to a server location through vb.net 2005. Is there a simple way to do this? Every time I try to detach the database, I cannot because the database is in use. Is there something I am missing with this? Thanks
View Replies !
View Related
|
TRACKER
