1.) Taking a database out of an availability group,
2.) Setting the recovery to simple,
3.) Shrinking the log file,
4.) Setting the recovery mode back to full,
5.) Then backing it up.
I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call? I have the code to reestablish the database to the availability group if I can get the restore feature working.Â
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command   BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I am trying to make my maintenance plan to backup on a remote server but it fails with:
"failed with the following error: "xp_delete_file() returned error 2".
I am pretty sure its because it doesnt have full access to the share folder... My question is, how can I grant access to it, if I am using a local account as sql agent service: NT ServiceSQLSERVERAGENT.I need to change the service account?
Sorry if this is elementary, but I have searched and cannot find an answer. I have a backup file of a sql server 2000 database. I want to restore it to a remote hosted sql server 2005. Using sql server management studio, it seems to me that I cannot access the files stored local on my computer. Does anybody have a solution? What is the best way to deploy my backup file to the remote server? Thanks for any suggestions!
FYI: I've posted this on a couple of forums and haven't gotten any response. I hope someone here can help since this is way past due.
First I'll give a little background on our situation.
Log Shipping and Replication are out, so I am scripting a backup locally, an xcopy to a remote box, and then a restore.
In the early stages of this, I'm trying to do 3 databases. 2 of them work fine alone. It's when I add the 3rd one that I have a problem. I noticed that in the 2nd stored procedure that I probably need to take out the WITH REPLACE if I'm dropping it beforehand as well. I don't have time to test it on this box until later tonight. I don't think that's the issue because it was doing the same thing before I added the drop. I'm overwriting the .txt file so I don't have the exact error that it's giving. I believe it's something similar to "Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation." I believe it also said [SQLSTATE 42000].
Now for the code. Props to Tara and the code she's put online.
Any help would be appreciated and I'll be glad to help answer questions related to what I've got.
CREATE PROC sp_backup_user_dbs3 AS SET nocount ON DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss DECLARE @cmd SYSNAME -- stores the dynamically created DOS command DECLARE @Result INT -- stores the result of the dir DOS command DECLARE @RowCnt INT -- stores @@ROWCOUNT DECLARE @DBName SYSNAME DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file DECLARE @loglogical VARCHAR(1000) DECLARE @datalogical VARCHAR(1000) DECLARE @restoreData VARCHAR(255) DECLARE @restoreLog VARCHAR(255) DECLARE @backupFile VARCHAR(255) DECLARE @physicalNameData VARCHAR(255) DECLARE @physicalNameLog VARCHAR(255) DECLARE @physicalNameDataStripped VARCHAR(255) DECLARE @physicalNameLogStripped VARCHAR(255) DECLARE @ExecStr NVARCHAR(4000) DECLARE @strSQL VARCHAR(1000) DECLARE @restoreToDataDir VARCHAR(255) DECLARE @restoreToLogDir VARCHAR(255) DECLARE @path VARCHAR(100) SET @path = 'I:ackupMoveTo14'
--we need to delete all the old backup files from the I:ackupMoveTo14 folder -- Build the del command SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F' --PRINT @cmd EXEC master..xp_cmdshell @cmd, NO_OUTPUT
CREATE TABLE #whichdatabase ( dbname SYSNAME NOT NULL )
INSERT INTO #whichdatabase ( dbname ) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] IN ( 'db1', 'db2') ORDER BY [name] -- Get the database to be backed up SELECT TOP 1 @DBName = dbname FROM #whichdatabase SET @RowCnt = @@ROWCOUNT -- Iterate throught the temp table until no more databases need to be backed up WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '' + @DBName + '.BAK' BEGIN backup log @dbname WITH truncate_only END
-- Backup the database BACKUP database @DBName TO disk = @filename
DELETE FROM #whichdatabase WHERE dbname = @DBName
-- Get the database to be backed up SELECT TOP 1 @DBName = dbname FROM #whichdatabase SET @RowCnt = @@ROWCOUNT -- Let the system rest for 5 seconds before starting on the next backup WAITFOR delay '00:00:05' END
DROP TABLE #whichdatabase SET nocount OFF BEGIN SET @cmd = '' SET @cmd = 'xcopy I:ackupMoveTo14*.BAK \RemoteServer /C /Y' EXEC master.dbo.xp_cmdshell @cmd END BEGIN
EXEC [RemoteServer].master..usp_restoreDbsFromDir2 END RETURN 0 GO
3rd Step(the code for the usp_restoreDbsFromDir2 on the remote server):
-- Get files sorted by date SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'
CREATE TABLE #Dir (DirInfo VARCHAR(7000) ) -- Stores the dir results CREATE TABLE #BackupFiles (BackupDate varchar(10), BackupFileName nvarchar(1000) ) -- Stores only the data we want from the dir CREATE TABLE #RestoreFileListOnly ( LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), [Size] numeric(20,0), [MaxSize] numeric(20,0) )
INSERT INTO #Dir EXEC master.dbo.xp_cmdshell @cmd
INSERT INTO #BackupFiles SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) FROM #Dir WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'
-- Get the newest file SELECT TOP 1 @bkpFile = BackupFileName FROM #BackupFiles ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the table until no more databases need to be backed up WHILE @RowCnt <> 0 BEGIN
SET @cmd = @restoreDir + @bkpFile
INSERT INTO #RestoreFileListOnly EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')
--get the dbname from the bkpFile name --SET @strSQL = CHARINDEX('_db_', @bkpFile) --SET @dbname = LEFT(@bkpFile, @strSQL - 1)
SET @backupDisk = @restoreDir + @bkpFile SELECT @datalogical = LogicalName FROM #RestoreFileListOnly WHERE Type = 'D' SELECT @loglogical = LogicalName FROM #RestoreFileListOnly WHERE Type = 'L' SELECT @PhysicalDataPath = PhysicalName FROM #RestoreFileListOnly WHERE Type = 'D' SELECT @PhysicalLogPath = PhysicalName FROM #RestoreFileListOnly WHERE Type = 'L'
SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate' --alter database MyDatabase set offline with rollback immediate --PRINT @strSQL EXEC (@strSQL)
SELECT @strSQL = 'DROP database ' + @dbname --alter database MyDatabase set offline with rollback immediate --PRINT @strSQL EXEC (@strSQL)
i have  .bak file  downloaded from internet , and i also have istalled  sql server  2012.my problem that i can not restore  this  .bak file and get this error massage :
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf'. (Microsoft.SqlServer.SmoExtended) - .bak file  version  = 661  10  50  1600 = sql server  2008R - my sql version   = Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Is it possible to backup/restore sql server databases in a logical way like oracle(exp/imp, expdp,impdp, can export/import tables/users etc)?my case, I have a database which has many data files, in different file groups, I would like to put them together into one file and don't want to shred the database.
I have a database size of 9.8TB and I backup it to 30 backup devices. Each one has 110GB after backup compression.I tried to restore these files to standby server via 100MbE network but it always failed. My colleague told me this never happen before and I said yes because I have done this before a lot of times when backup devices are significantly smaller.He said the only way is to copy backup files locally and restore locally. But I am trying another method, I create more: 64(maximum) backup devices and try to restore via network again.SQL server version is Microsoft SQL Server 2012 (SP1)
- 11.0.3401.0 (X64)  Jan 9 2014 13:22:15  Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) .
What I want to do is to backup my database through the SQL script, when the backup is successful, the .bak file is saved in my local harddisk. After that, I used the XYRunProc to execute a "copy" command to copy the .bak file to a remote server.
However, when I want to restore the database using the .bak on the remote server, I found that the .bak is corrupted. This problem does not exist if the file is copied to the remote server manually (click copy on local server and click paste on remote server).
Do anyone know what's the problem of this? This really made me very frustrated. Hope someone can help me, thank you very much!
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 enoughspace 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 switchto my pc.My wkst uses w2k pro sp4, the other PC win xp home SP1. MSDE 2000.The share is visible and RW for the administrator of my wkst.The 2 pc are in the same workgroup and are not part of a domain.I tried to restore using this code:RESTORE DATABASE MydbFROM DISK='\uncpath ofile.bak'WITH ...and get the error 3201 and in the log:"BackupDiskFile::OpenMedia: the backup peripheral'\uncpath ofile.bak' could not open. Error in O.S. = 5(AccessDenied.). " (I'm translating form italian)I read that the problem can arise from the fact that the restoration isexecuted by a sql server "user" that has not the same permissions as theadministrator. I tried to assign to the SQLSERVERAGENT service the useradministrator with no avail.And executing:xp_cmdshell 'dir \uncpath ofile.bak'gives the error "access denied".Is there a solution to this problem?Otherwise how can I restore a database whose backup has almost the samesize as the free space on the disk?thank youmaxx--NOSPAM: Rimuovere i trattini dallo username!Cut hyphens from my username!
I have a user database which has 1 data file and 1 logfile. I did a complete backup of the database to a file on the disk drive, using Enterprise manager. Due to some reason, I had to drop the database. The only way I could restore it was by, 1. Create the database with the same name. 2. Restore the complete backup from the file device.
While doing so, I get the message "Backup set holds the backup of daatabase other than the existing 'userdbname' database. BAckup terminating."
Is it because the database was dropped and recreated. If I choose the option to overwrite, it restores successfully.
Is this normal and right way to do it? Is there any thing else that I need to take care of, while backup or restore?
I need to restore a backup file which resites on a network share to my local Sql70 server. Is it possible to restore a database from a backup file on the network share? Thanks!
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
Hi everybody, On executing the RESTORE command of SQL Server to restore from a backup of 78.3 MB, the "Server Application Unavailable" error message comes up.The error message in the Application log is as follows:aspnet_wp.exe (PID: 2184) was recycled because memory consumption exceeded the 152 MB (60 percent of available RAM). However using Query Analyser of SQL Server I am able to restore the database. What is the solution to this problem?
A full database backup file was created and placed in my C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLBackup folder. In attempting to restore the file using "Restore Database", I get the following error: System.Data.SqlClient.SqlError: Directory lookup for the file "d:Microsoft SQL ServerMSSQLdataworkspace.mdf" failed with the operating system error 3 (The system could not find the file path specified.).
One of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.
I tried below Scripts. All scripts shows same error.
EXEC sp_attach_single_file_db @dbname='PhoenixPolice', @physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf' GO CREATE DATABASE PhoenixPolice ON Â Â (NAME = N'PhoenixPolice', Â Â FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH_REBUILD_LOG GO CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH
All scripts shows below error
File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.
May Any one guide me? I have a backup file of database which is in SQL Server 2000. it has no Extension.I want to restore this backupfile or this database in my SQL Server 2005. I have tried to Attach Database or attach this backup file in Sql Server2005 but it also fails . First I have created New database name as is on the backupfile and then I have also tried to rename this file with .bak extension and then try to restore again it fails. Plese Guide me urgently........ :eek: :eek: :eek: :eek: :eek:
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write restore filelistonly from disk = 'path and filenam.bak' restore database. zy from disk = 'path and filename.bak' with replace, move..... move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
I checked quickly one network  and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
I tried to use backup and restore database tasks to restore backup file but it does not work. The backup file I tried to restore in SQL server 2000 is from somewhere else (from my friend) and saved in cd-rom, not the one I created before. How can I restore it to view in SQL server 2000 database? Can you show me step by step? Thanks for your help
I have a production SQLServer 6.5 on Nt 3.51 SP4 that had problems two weeks ago, an I had to restore the backup of the previous night.
we are testing our disaster recovery procedures on another server, identical to the production one.
We have installed NT 3.51 SP 4 and SQLServer 6.5 in the same order and with the same configuration of the production server, but i can't restore on the test server the backup files that i have succesfully resotred on the production server.
we make the SQLServer back up on file ( and then we backup those files on tape with NT backup) so I don't think is a problem of HW Tape Bios or Compression because i have tried to resotre directly the files without taking them from a tape.
1) I have created a new database , without data, with the same devices, in megabyte, that I have on the production server. than from enterprise manager I have started the restore from file, and after two seconds everything stops, and I can't even shutdown the task but i MUST turn the server off using the power button.
when I start the server again there are no specific errors in the event log nor in the SQLServer error log. The db i was trying to restore is marked "loading" and it is not available.
This same procedure works perfectly on the production server.
2) I have then created another db on the test server, and I have succesfully tranferred ( but not resotred) the produciont db onto the test one, using the transfer menu on SQLEnterprise manager.
3) another strange thing is that I have tryed to expand the tempdb, which is only 2 mega by default, but whenever i try to expand it, using enterprise manager, only the LOG area of tempdb sucessfully expands, not the data area. I don't know if this behaviour is related to the unsuccesfull restore or if it is another problem.
i have checked the sort order and character set of the 2 servers and they are the same.
I have 2 questions, and I appreciate if somebody can help me to figure out the good way to do. Thanks a lot.
1/. If I want to restore tranlog backup file from linked server to SQL Server 2000, Is the database online or offline during that time (since I want to make sure db online for users, but not offline)
2/. Can I restore tranlog backup file from SQL Server 2000 to SQL Server 2005 database?
Now i must restore a database from a backup file of MS SQL Sever 6.5,but this file is bad. When i restore database from this file, SQL Server tell me that the format of this file is not file format of SQL Server 6.5.So that I must repair this backup file firstly. I don't know database file format of SQL Server 6.5.
hi i have restore database use backup file in another machine of same name of database there following error
TITLE: Microsoft SQL Server Management Studio ------------------------------
Restore failed for Server 'database name'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'dbname' database. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476
This script will read the contents of a DB backup file, and generate a restore command.
Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.
This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.
Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.
-- Create Restore Database Command from DB Backup File
select [--Restore--]= case when a.Seq = 1 then @cr+ @cr+'restore database '+c.DatabaseName+ @cr+'from disk ='+@cr+@tab+''''+ @backup_path+''''+@cr+'with'+@cr else '' end+ @tab+'move '''+a.LogicalName+ '''to '''+a.PhysicalName+''' ,'+ case when a.Seq = b.Seq then @cr+@tab+'replace, stats = 5 , recovery' else '' end from #filelist a cross join ( select Seq = max(b1.Seq) from #filelist b1 ) b cross join ( select DatabaseName = max(c1.DatabaseName) from #header c1 ) c order by a.Seq go drop table #header drop table #filelist
I am confused regarding why the default behavior of this option is to use file no 1 if the option is not specified? If I take two backups on different days, weeks, months, etc. and write them to the same Backup set, the second and most recent backup by the way gets the higher file number (position) as one would expect. What is incomprehensible to me is why the decision was made to restore the oldest backup from the backup set if you do not specify with file no? Seems like common sense that I would NEVER want to restore the oldest backup by default and would most-likely always want to restore the most recent backup by default!
If anyone has suggestions on how I could suggest that the behavior of this option be changed, I would appreciate it, as this has caused me much pain.
I €˜d like to discus with you the following REAL enough disaster scenario: 1. The TEST database is in the FULL backup mode. 2. WE have a full TEST DB backup and all tr. log backups. 3. DBA moved TEST database OFFLINE for maintenance operations. 4. MDF file for TEST DB was removed by mistake. 5. TRANSACTION LOG (LDF) file is OK. 6. DBA want to recover database to the point of failure. According with MS SQL Server 2000 documentation it is possible. We need to backup the transaction log , BUT I CANNOT DO THIS. PLEASE, HELP. In the same time, I can make LDF backup and recover database to the point of failure, if database is online and I stop/start SQL Server to remove MDF file.
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
I can run this example from SQL Book Online from sql query analyzer. I can build the TestDB database.
BACKUP DATABASE Northwind TO DISK = 'c:Northwind.bak' RESTORE FILELISTONLY FROM DISK = 'c:Northwind.bak' RESTORE DATABASE TestDB FROM DISK = 'c:Northwind.bak' WITH MOVE 'Northwind' TO 'c: est estdb.mdf', MOVE 'Northwind_log' TO 'c: est estdb.ldf' GO
But... When I build stored procedure and call it through VB6. I've got the gray database symbol along with message TestDB (Loading/Suspecting) Why I cannot run these commands through VB6
